SQL查询连续登录用户

问题：如何判读用户连续5天登录过系统？

1.环境
MySQL 8.0.21

2.准备测试数据

create table report_user_login(id bigint auto_increment primary key comment '主键ID', user_id varchar(10) comment '用户ID', login_dt date comment '登录日期');

insert into report_user_login(user_id, login_dt) 
values ('alex', '2022-11-01'), ('alex', '2022-11-02'), ('alex', '2022-11-03'), ('alex', '2022-11-04'), ('alex', '2022-11-04');

insert into report_user_login(user_id, login_dt) 
values ('alex', '2022-11-01'), ('Jobs', '2022-11-02'), ('Jobs', '2022-11-03'), ('alex', '2022-11-04'), ('Jobs', '2022-11-04');

insert into report_user_login(user_id, login_dt) 
values ('Steven', '2022-11-01'), ('Steven', '2022-11-02'), ('alex', '2022-11-03'), ('Steven', '2022-11-04'), ('Steven', '2022-11-04');

insert into report_user_login(user_id, login_dt) 
values ('alex', '2022-11-01'), ('alex', '2022-11-05'), ('alex', '2022-11-03'), ('alex', '2022-11-04'), ('alex', '2022-11-07');

3.实现方式一，只用找出用户，不判断具体从哪一天开始连续登录

-- 1.按照用户和日期去重
-- 2. 构建连续差值的等值
-- 3. 用户和等值分组计数，可以得到用户是否连续
with a1 as (
select distinct a.user_id, a.login_dt from report_user_login a)
, a2 as (select a1.user_id,a1.login_dt,row_number() over(partition by user_id order by login_dt) as rwn
from a1)
select user_id  from a2 group by user_id,date_sub(login_dt, interval rwn day) having count(1) >4;

 

4.实现方式二，找出用户，且查出具体从哪一天开始连续登录的

-- 1.按照用户和日期去重
-- 2.构建每个日期的连续日期，通过日期+具体的数字和不等式来实现关联，相当于扩建了倍数
-- 3.分组count by 取出 对应的值
with a1 as (
select distinct a.user_id, a.login_dt from report_user_login a),
a2 as (select a1.user_id,a1.login_dt,a1.login_dt +1 as dt, aa.user_id user_idaa,aa.login_dt login_dtaa
from a1 left join a1 as aa on a1.user_id = aa.user_id and a1.login_dt <= aa.login_dt and a1.login_dt +4 >= aa.login_dt)
select user_id, login_dt from a2 group by user_id, login_dt having count(*) >=5 ;