求解连续登录问题sql题目的套路,逐过程分析,看完必懂

连续登录问题套路

/*

 _________________
|1  2022-06-12
|1  2022-06-13
|1  2022-06-14
|2  2022-06-12
|2  2022-06-13
|3  2022-06-12
|3  2022-06-13
|4  2022-06-12

TOP1:求连续三天连续登录的用户
1.先去重(有可能用户一天登录多次)
select
	iser_id,
	date
from t
group by user_id,date




2.在去重的基础上,根据user_id按日期排序
select
	t2.user_id,
	date,
	rank() over(partition by user_id order by date asc) num
from(
    select
        user_id,
        date
    from t
    group by user_id,date
)t2



3.在排序的基础上,利用[ 等差 ]的性质,两数之差为定值
select
	t2.user_id,
	date_sub(t2.date,t2.num) temp_date
from(
    select
        t2.user_id,
        date,
        rank() over(partition by user_id order by date asc) num
    from(
        select
            user_id,
            date
        from t
        group by user_id,date
    )t2
)t3

4.在处理完等差的基础上再次进行按user_id以及temp_date分组过滤去重即可
select
	distinct t3.user_id
from(
    select
        t2.user_id,
        date_sub(t2.date,t2.num) temp_date
    from(
        select
            t2.user_id,
            date,
            rank() over(partition by user_id order by date asc) num
        from(
            select
                user_id,
                date
            from t
            group by user_id,date
        )t2
    )t3
)t4 
grroup by t3.user_id,t3.temp_date
having count(t3.user_id) >= 3 


总结：套路是固定的,比如让你求连续 n 天登录的次数,你只需要改动最后面的数字即可~