一文读懂SQL中的连续问题
- 一、连续问题介绍
- 二、问题分析及解决方法
- step1:划定分组
- step2:求其他信息
- 三、代码实现
- 1.创建表
- 2.插入数据
- 3.查询语句
一、连续问题介绍
程序员小李计划跳槽,最近在CSDN上疯狂充电,其登陆CSDN的日期如下:
请根据小李的登陆数据,求出其连续登陆信息,具体如下:
1.开始登陆日期
2.结束登陆日期
3.连续登陆天数
4.非连续登陆时距离上次登陆的天数
预期结果是这样的:
二、问题分析及解决方法
想要解决连续问题,总体上要分为两步。
step1:给出连续登陆的分组标识,划定分组
step2:求组内的各种信息
step1:划定分组
- 分析:(认真看!精华在这里)
假设:
每天都登陆的话,则预期间隔(登陆当天距登陆首天的间隔天数)为自增长序列,实际间隔与预计间隔的增长速度相同,且数值相等。
实际:
登陆间断时,实际间隔 不再 (同预计间隔一样)自增长;
连续登陆时,实际间隔 开始( 同预计间隔一样)自增长;
因此:
连续登陆的时间段里,实际间隔与预计间隔的增长速度相同,数值不等,差相等。
- 操作:
在子查询中增加两列:预计间隔和实际间隔;外查询对二者做差,差值相同的记录即为连续登陆的组。
step2:求其他信息
- 开始时间:组内最小的时间,对组内的日期求min
- 结束时间:组内最大的时间,对组内的日期求max
- 连续登陆天数:对组内的数据条数求count
- 距离上次登陆的天数:本组内的首次登陆日期 - 其前一登录日期
(子查询中再增加一列:上一登录日期)
三、代码实现
1.创建表
create table tmptablee(rq datetime);
2.插入数据
insert tmptablee values('2022.5.1') ;
insert tmptablee values('2022.5.2') ;
insert tmptablee values('2022.5.3') ;
insert tmptablee values('2022.5.6') ;
insert tmptablee values('2022.5.7') ;
insert tmptablee values('2022.5.10');
insert tmptablee values('2022.5.11');
insert tmptablee values('2022.5.12');
insert tmptablee values('2022.5.19');
insert tmptablee values('2022.5.20');
insert tmptablee values('2022.5.22');
insert tmptablee values('2022.5.23');
insert tmptablee values('2022.5.28');
3.查询语句
SELECT min(rq) as 开始时间
,max(rq) as 结束时间
,count(1) as 连续登陆天数
,datediff(min(rq),min(rq2)) as 距离上次登陆的天数
FROM ( SELECT datediff( rq,'2022-05-01') as id1 -- 实际间隔
,(SELECT COUNT(1) FROM tmptablee WHERE a.rq > rq) as id2 -- 预期间隔,使用子查询实现
-- ,row_number() over(order by rq) -1 as id2 -- 预期间隔,使用窗口函数实现
,(SELECT MAX(rq) FROM tmptablee WHERE a.rq > rq) as rq2 -- 上一登陆日期,使用子查询实现
-- ,lag(rq,1,rq) over(order by rq) as rq2 -- 上一登陆日期,使用窗口函数实现
,rq
FROM tmptablee a) a
GROUP BY id1 - id2;
-- 或者 GROUP BY date_add(rq,interval -id2 day);
-- 上述聚合就不需要用到id1实际间隔列了,可以理解为用实际日期代替了实际间隔。
得到我们想要的结果啦,简单分析一下,小李五月份连续登陆6组,平均每次连续登陆2.2天,真的是勤奋啊,付出就有收获,我们相信小李一定可以跳槽成功,升职加薪迎娶白富美,走向人生巅峰!