最长连续登录天数

select user_id, max(consec_days) max_consec_days
from
(select user_id, count(startday) consec_days
from
(select user_id, 
date_sub(fdate, interval (dense_rank() over (partition by user_id order by fdate)) day) startday
  from tb_dau
 where year(fdate) = 2023 and month(fdate) = 1
) t1
group by user_id, startday
) t2
group by user_id

select
user_id
,max(num) max_consec_days
from
    (select
    user_id
    ,date_sub(fdate, interval r day) datesub
    ,count(1) num
    from
        (select
        user_id
        ,fdate
        ,row_number()over(partition by user_id order by fdate) r
        from tb_dau
        where fdate between '2023-01-01' and '2023-1-31') a
    group by 1,2
    ) b
group by 1

#抄的评论区大佬的，看了好几遍才看懂
with t1 as(
    select user_id, count(continday) as consec_days
    from
    (
        select user_id, 
        # data_sub函数
        # 用登陆日期-连续递增的数字（排序的位次），
        # 如果日期连续，就会产生相同的结果
        date_sub(fdate, interval (
            dense_rank() 
            over (partition by user_id order by fdate)
        ) day
        ) continday
        from tb_dau
        where year(fdate) = 2023 and month(fdate) = 1
    ) t2
    # 对计算结果进行分组，相同的在一组，表示连续登录
    group by user_id, continday
)

select user_id, max(consec_days) as max_consec_days
from t1
group by user_id

with
    t1 as (
        select distinct
            user_id,
            fdate
        from
            tb_dau
        where
            fdate >= '2023-01-01'
            and fdate <= '2023-01-31'
    ),
    t2 as (
        select
            user_id,
            fdate,
            rank() over (
                partition by
                    user_id
                order by
                    fdate
            ) as rn
        from
            t1
    ),
    t3 as (
        select
            user_id,
            fdate,
            date_sub (fdate, interval rn day) flag_date
        from
            t2
    ),
    t4 as (
        select
            user_id,
            count(*) total
        from
            t3
        group by
            user_id,
            flag_date
    )
select
    user_id,
    max(total) as max_consec_days
from
    t4
group by
    user_id

AI可真聪明

步骤分析

1. 去重处理：确保每个用户每天的登录记录唯一。

2. 生成序号：为每个用户的登录日期按顺序编号。

3. 计算连续组标识：用登录日期减去序号，相同结果的日期属于同一连续组。

4. 统计每组连续天数：按用户和连续组分组，计算每组的连续天数。

5. 取最大值：对每个用户，取所有连续组中的最大天数。

select 
    user_id,
    max(co) max_consec_days
from (
    select
        user_id,
        count(rn) co
    from (
        select 
            fdate,
            user_id,
            row_number()over(partition by user_id order by fdate) rn,
            date_sub(fdate,interval row_number()over(partition by user_id order by fdate) day) ds
        from tb_dau
    ) a
    group by user_id,ds
) b
group by user_id;

with tmp as (
    select 
    IFNULL(DATEDIFF(next,fdate)=1,0) as diff,
    row_number() over(partition by user_id order by fdate) as eday,
    user_id
from(
select
    fdate,
    user_id,
    lead(fdate,1,NULL) over(partition by user_id order by fdate) as next
from tb_dau) t1
)

select
    user_id,
    MAX(eday-last) as max_consec_days
from(
select
    lag(eday,1,0) over(partition by user_id order by eday rows 1 preceding) as last,
    user_id,
    eday
from tmp
where diff=0
)t1
group by user_id

WITH t1 as (
	select user_id,fdate,DATE_SUB(fdate,interval ROW_NUMBER() over(PARTITION by user_id ORDER BY fdate) day) as date_sub
	from tb_dau
),
t2 as (
SELECT user_id,count(*) day_sum from t1 GROUP BY user_id,date_sub
),
t3 as(
select user_id,day_sum,ROW_NUMBER() over(PARTITION by user_id ORDER BY day_sum desc) day_rank from t2
) 
SELECT user_id,day_sum as max_consec_days from t3 where day_rank =1

select user_id,consecutive_days as max_consec_days
from 
(select user_id,consecutive_days,dense_rank()over(partition by user_id order by consecutive_days desc) as rk 
from 
(select user_id,min(fdate),max(fdate),count(*) as consecutive_days
from 
(select user_id,fdate,date_sub(fdate,interval rn day) as grp,rn
from 
(SELECT user_id,fdate,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY fdate) AS rn
FROM tb_dau) as t1
) as t2
group by user_id,grp) as t3
) as t4
where rk =1 
group by user_id,consecutive_days

select user_id,max(max_consec_days) max_consec_days
from (
select start_day,user_id,count(1) max_consec_days
from (
    select user_id
    ,fdate 
    ,date_sub(fdate,interval row_number()over(partition by user_id order by fdate) day) start_day
    from tb_dau
    where fdate between '2023-01-01' and '2023-01-31'
   )a 
group by start_day,user_id
)t2
group by user_id

with a as 
(select distinct fdate,user_id,row_number() over(partition by user_id order by fdate) ranking from tb_dau),
b as
(select *,date_sub(fdate, interval ranking day) df from a),
c as
(select user_id, count(df) consec_days from b group by user_id,df)
select user_id,max(consec_days) max_consec_days from c group by user_id