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

任务场景:

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

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

数据库:mysql8.0

表部分结构：

记录表A

字段	说明
session	唯一标识
queue_name	组别名
status	状态
created_at	创建时间

评价表B

字段	说明
session	唯一标识
grade_name	评价等级
created_at	创建时间

解决思路：

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

得出最终结果：

---

sql 按着时间分组，每组取 20 条数据

sql 表:

CREATE TABLE `cms_forum` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `article_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '文章id',
  `article_title` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '文章标题',
  `article_link` varchar(200) NOT NULL COMMENT '链接',
  `user_nick` varchar(20) NOT NULL COMMENT '用户昵称',
  `article_summary` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '文章摘要',
  `article_pubdate` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '文章发表时间',
  `article_pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '文章图片',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '编辑id',
  `editor_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '编辑姓名',
  `editor_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '操作时间',
  `is_del` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除：0 没有 1 删除',
  `is_publish` tinyint(10) unsigned NOT NULL DEFAULT '0' COMMENT '是否发布：0 未发布，1 发布',
  `is_recommend` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否推荐：0->未推荐，1->已推荐',
  `order_by` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `article_time` int(4) NOT NULL DEFAULT '2020',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1909 DEFAULT CHARSET=utf8 COMMENT='专题';

按着 article_time(存的是年份) 字段进行分组，每组各取10条数据？
主要实现:一个按着年份划分的时间线

实现的效果:

sql结果

SELECT * FROM cms_forum m WHERE(
SELECT @rn:= CASE WHEN @article_time = article_time THEN @rn + 1 ELSE 1 END AS rn
FROM cms_forum n
WHERE m.article_time = n.article_time
AND n.id > m.id 
AND n.is_del=0)<'20'
AND is_publish=1 
AND is_del=0
ORDER BY article_time desc

SELECT ROW_NUMBER() OVER (ORDER BY n.article_time) row_num FROM cms_forum n