[大数据]SQL:求筛选时间段内每天各分组的聚合数据

任务场景:

报表需求，必须由SQL处理完成，页面筛选条件为日期段，需要将所选日期按照每日区分，查出所有组别在每日的进线量、接通量、呼损量、接听率、进线占比、好评率。

进线量为线路全部进线量，接通量为status=‘Answered’量，呼损量为status=‘NoAnswered’量，接听率=接通量/进线量，进线占比=线路进线量/进线总量，好评率=（非常满意+满意）/（非常满意+满意+服务不满意）

数据库:mysql8.0

表部分结构：

解决思路：

1.先根据所选时间段筛选出全部数据的记录和评价信息

select 
	DATE_FORMAT(cu.created_at, '%Y-%m-%d') as created_at, cu.queue_name ,cu.status ,sr.grade_name 
from xcc_data.cdr_union cu 
left join xcc_data.satisfy_rating sr on cu.`session` COLLATE utf8mb4_general_ci = sr.`session` 
where 
	cu.queue_name <> ''
	and cu.created_at between '2021-12-11 00:00:00' and '2021-12-14 00:00:00'

得出结果：

2. 根据线路和日期分组，并计算各线路在各天内的聚合数据

select 
	queue_name,DATE_FORMAT(created_at, '%Y-%m-%d') as created_at,
	count(*) as jxzl,
	sum(if(status='Answered',1,0)) as jt,
	SUM(IF(status='NoAnswered', 1, 0 )) as hs,
	SUM(IF(grade_name = '非常满意',1,0)) as fcmy, 
	SUM(IF(grade_name = '满意',1,0)) as my,
	SUM(IF(grade_name = '对服务不满意',1,0)) as fwbmy
from (
	select 
		DATE_FORMAT(cu.created_at, '%Y-%m-%d') as created_at, cu.queue_name ,cu.status ,sr.grade_name 
	from xcc_data.cdr_union cu 
	left join xcc_data.satisfy_rating sr on cu.`session` COLLATE utf8mb4_general_ci = sr.`session` 
	where 
		cu.queue_name <> ''
		and cu.created_at between '2021-12-11 00:00:00' and '2021-12-14 00:00:00'
)a 
GROUP by a.created_at,queue_name 
order by queue_name,a.created_at

得出结果：

3.求出筛选时间段内各天的总进线量

select count(*) as xlzl,created_at 
from (
	select DATE_FORMAT(cu.created_at, '%Y-%m-%d') as created_at,id 
	from xcc_data.cdr_union cu 
	where 
		cu.queue_name <> ''
		and cu.created_at between '2021-12-11 00:00:00' and '2021-12-14 00:00:00'
)b 
group by b.created_at 

?得出结果：

4.将2,3的结果关联，计算出要求的聚合数据?

select 
	aa.queue_name,
    aa.created_at,        
    jxzl as '进线量',
    jt as '接通量',
    hs as '呼损量',
    IFNULL(CONCAT(ROUND(jt/jxzl*100,2), '%' ),'0.00%') as '接听率',
    IFNULL(CONCAT(ROUND(jxzl/xlzl*100,2), '%' ),'0.00%') as '进线占比',
    IFNULL(CONCAT(ROUND((fcmy+my)/(fcmy+my+fwbmy)*100,2), '%' ),'0.00%') as '好评率'
from (
	select 
		queue_name,DATE_FORMAT(created_at, '%Y-%m-%d') as created_at,
	    count(*) as jxzl,
	    sum(if(status='Answered',1,0)) as jt,
	    SUM(IF(status='NoAnswered', 1, 0 )) as hs,
	    SUM(IF(grade_name = '非常满意',1,0)) as fcmy, 
	    SUM(IF(grade_name = '满意',1,0)) as my,
	    SUM(IF(grade_name = '对服务不满意',1,0)) as fwbmy
	from (
		select 
		    DATE_FORMAT(cu.created_at, '%Y-%m-%d') as created_at, cu.queue_name ,cu.status ,sr.grade_name 
		from xcc_data.cdr_union cu 
		left join xcc_data.satisfy_rating sr on cu.`session` COLLATE utf8mb4_general_ci = sr.`session` 
		where 
			cu.queue_name <> ''
			and cu.created_at between '2021-12-11 00:00:00' and '2021-12-14 00:00:00'
	)a 
	GROUP by a.created_at,queue_name 
	order by queue_name,a.created_at
) aa
left join (
	select count(*) as xlzl,created_at from (
		select DATE_FORMAT(cu.created_at, '%Y-%m-%d') as created_at,id 
		from xcc_data.cdr_union cu 
			where 
				cu.queue_name <> ''
				and cu.created_at between '2021-12-11 00:00:00' and '2021-12-14 00:00:00'
	)b group by b.created_at 
) bb on aa.created_at=bb.created_at

得出最终结果：