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

select user_id,max(cgep) max_consec_days from (
-- count()+1是因为最初始的一条记录是连续两天登录
select user_id,count(label) + 1 cgep,diff from (
select a.user_id,a.fdate fda,b.fdate fdb,datediff(b.fdate,a.fdate) diff,
-- 连续且datediff = 1的记录将被分为同一组
@group_label := case when datediff(b.fdate,a.fdate) = 1 then @group_label 
else @group_label + 1 end label
from
(select row_number() over (partition by user_id order by fdate) rnum,fdate,user_id from tb_dau) a,
(select row_number() over (partition by user_id order by fdate) rnum,fdate,user_id from tb_dau) b,
-- 使用动态变量对连续datediff的记录进行分组
(select @group_label := 0) c
-- 用row_number将日期向上偏移一条记录
where a.rnum = b.rnum - 1 
and a.user_id = b.user_id
and a.fdate between '2023-01-01' and '2023-02-01'
and b.fdate between '2023-01-01' and '2023-02-01'
order by user_id,a.fdate,b.fdate) d
-- 因为使用了日期进行排序，此处label相同并且datediff相同的只有datediff=1的记录
group by user_id,label,diff) e  -- 此处注意不能使用where过滤datdiff = 1，因为select会在where后进行计算导致非连续日期被放在连续区间
group by user_id order by user_id