任务场景:
报表需求,必须由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