【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字段】（会话划分问题）
思路：

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

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

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

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

重点是找到每个用户会话的起点并赋值为1！！！