连续登录问题

数据库中有个表的表名叫userlogin_info，有三个字段，id（索引）、userid（用户id）、login_time（登录时间），用户每登录一次APP会往这个表写入一条记录。

问题1：2022年1月中连续7天登录的用户数有哪些？（1至7号连续、或者2至8号连续等）

with t_login as 
(
    select userid,date_sub(login_time,row_cnt) as flag_time 
    from 
    (
        select userid,login_time,row_number over(partition by userid order by login_time) as row_cnt
        from 
        (
            select userid,login_time
            from 
            (
                select id,userid,from_unixtime(unix_timestamp(login_time,'yyyy-MM-dd HH:mm:ss'),'yyyyMMdd') as login_time
                from userlogin_info
            )
            where login_time between 20220101 and 20220131
            group by userid,login_time
        )
    )
)
select userid
from 
(
    select userid,flag_time,count(1) as login_cnt
    from t_login
    group by userid,flag_time
    having login_cnt>=7
)
group by userid

 

问题2：2022年1月中连续7天登录的用户数有多少？（1至7号连续、或者2至8号连续等）

with t_login as 
(
    select userid,date_sub(login_time,row_cnt) as flag_time 
    from 
    (
        select userid,login_time,row_number over(partition by userid order by login_time) as row_cnt
        from 
        (
            select userid,login_time
            from 
            (
                select id,userid,from_unixtime(unix_timestamp(login_time,'yyyy-MM-dd HH:mm:ss'),'yyyyMMdd') as login_time
                from userlogin_info
            )
            where login_time between 20220101 and 20220131
            group by userid,login_time
        )
    )
)
select count(distinct userid) as login_uv 
from 
(
    select userid,flag_time,count(1) as login_cnt
    from t_login
    group by userid,flag_time
    having login_cnt>=7
)

 

解析：

1、对用户（userid）以及登录日期（login_time/天）进行去重，明确用户改天是否有登录

2、用排序函数对用户(userid) 按登录日期（login_time）进行排序得到（row_cnt）

3、用日期函数登录日期（login_time）减去排序的序号（row_cnt)，得到flag_time，如果用户连续登录，则flag_time的计算结果是相同的，反则没有连续登录
4、对用户（userid)以及flag_time进行去重，进行count()聚合计算，筛选出登录次数>=7的用户