题解 | #最长连续登录天数#

SELECT DISTINCT user_id,max(consec_days) over(partition by user_id order by consec_days desc) max_consec_days
FROM
(SELECT user_id,count(chazhi) consec_days
FROM
(SELECT user_id,fdate,date_sub(fdate,interval row_number() over(partition by user_id order by fdate) day) chazhi
FROM tb_dau
where fdate between '2023-01-01' and '2023-01-31') tmp
group by user_id,chazhi order by consec_days desc) tmp2

1.SELECT user_id,fdate,date_sub(fdate,interval row_number() over(partition by user_id order by fdate) day) chazhi

FROM tb_dau

where fdate between '2023-01-01' and '2023-01-31'

查询出指定时间范围内，每一条记录的登陆时间和ROW_NUMBER的差值chazhi，chazhi相等的数据项就是连续登陆的数据项。

（如有必要需要先对数据进行去重）

2.SELECT user_id,count(chazhi) consec_days，按照chazhi对user_id进行分组，查看用户的每个chazhi下也就是连续登录的天数有几种。

3.SELECT DISTINCT user_id,max(consec_days) over(partition by user_id order by consec_days desc) max_consec_days找出每一位用户的最大chazhi，其结果就是所长连续登录时间。