【SQL经典题目】连续日期判断、同时在线人数、会话划分、间隔日期连续、日期交叉

本文主要是介绍【SQL经典题目】连续日期判断、同时在线人数、会话划分、间隔日期连续、日期交叉，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

【1.查询至少连续3天下单的用户】

思路1（使用lead）：

distinct user_id,create_date去重，确保每个用户每天只有一条访问记录
lead(create_date,2,‘9999-12-31’) over(partition by user_id order by create_date)根据用户分区，订单日期排序，取后2行的订单日期(取不到则返回’9999-12-31’)
用datediff计算【订单日期】与【后2行订单日期】的差值，如果等于2则说明存在连续3天下单

筛选出datediff等于2的记录，distinct user_id即可

思路2（使用row_number）：

distinct user_id,create_date去重，确保每个用户每天只有一条访问记录
row_number() over(partition by user_id order by create_date)按用户分区，按访问日期排序，添加序号
每个日期与序号相减获得一个新的日期

group by user_id,diff对用户和新的日期进行分组，然后count()统计日期，判断count() >=3

【2. 用户登录日志表user_id,visit_date 用sql查询出近30天连续访问7天以上的用户数量】
思路：

筛选近30天每个用户的访问记录，并去重确保同一个用户当天只有一条记录
对用户分组按访问日期排序，添加序号
每条记录的访问日期与对应的序号相减，获得一个新的日期字段
group by用户，新的日期字段，统计个数，having筛选大于等于7

select user_id,diff,count(*) as cnt
from
(select user_id,visit_date,date_sub(visit_date,row_number() over(partition by user_id order by visit_date)) as difffrom (select distinct user_id,visit_date from table where visit_date >= date_sub(current_date,30)) t1
) t2
group by user_id,diff
having cnt >= 7

【3. 计算某段时间内同时在线人数最大值】
核心思路：

按时间排序，逐条处理（从上到下累加），获取每个时刻在线人数，取最大值

具体实现：

筛选出每个人的登陆记录并加一个字段1（id，登陆时间，1） union all 每个人的登出记录并加一个字段-1（id，登出时间，-1）
按照登陆/登出时间升序排序
sum()并开窗，窗口范围首行到当前行，即计算累加值（此时数据表示了每一个时刻的在线总人数）
max()计算累加值中的最大值（即同时在线人数最大值）

【4. 同一个用户相邻两次访问记录小于60s，则认为属于一个会话。现需对同一会话的访问记录增加会话id字段】（会话划分问题）
核心思路：

按每个用户的会话时间排序，找到每个用户会话的起点并加标签1，不是起点则加标签0，同一用户对标签进行累加，每个用户不同会话则会有不同的标签

思路：

对每个用户开窗并按访问时间排序，用lag()取上一次访问时间，取不到上一条则默认为0

每条访问记录减去上一次访问时间，差值>60则赋值1，否则赋值0（用1表示每个会话的起点）

对每个用户开窗，窗口范围首行到当前行，用sum()进行累加

用user_id拼接上累加值，表示每个会话

【5. 用户登陆记录表(user_id，login_datetime)，每行表达一个用户何时登陆，求各用户最长的连续登陆天数（间断1天也算连续）】（间断连续日期判断问题）
核心思路1：

用explode()将中间空1天的记录补充上，转化成连续日期的判断问题
（比如2条记录2021-12-03、2021-12-05 变成3条记录 2021-12-03、2021-12-04、2021-12-05）

思路1：

每个用户按照登陆日期去重
每个用户按登陆日期升序排列，用lead()取该用户下一次的登陆时间

如果下次登陆日期和本次登陆日期相差为2，则用array()创建数组，包含本次登录日期、本次登录日期+1

用explode()函数进行炸裂，此时就补充上了空缺日期

按照【题目1】进行连续日期判断即可

核心思路2：

找到每次连续区间的起始日期，赋1，其它日期赋0，将问题转化为会话划分问题，取每个会话中的max日期-min日期，即为连续天数

思路2：

每个用户按照登陆日期去重
每个用户按登陆日期升序排列，用lag()取该用户上一次的登陆时间

本次登录日期减去上次登录日期 >2的话则赋1，否则赋0

对每个用户开窗，窗口范围首行到当前行，用sum()进行累加

对每个用户、会话标签进行分组，取每个会话中max(login_date) - min(login_date) + 1即为最长连续天数。