【SQL】 解决用户连续N天登录问题

    业务中常见分析之一是分析用户连续登录使用情况，这也对应着SQL常见面试题——**用户连续N天登录**问题。

    我们假设现在有一张用户登录信息表user_login_info，表中字段有用户id（uid）、登录时间（login_time）。表中数据如下所示：

    现在要求查询出连续登录N天的用户。

    （1） 首先我们要对用户登录表进行去重操作，以避免用户当天多次登录情况对查询结果产生影响。这里用到了**distinct**关键词。

select
    uid, 
    distinct date(login_time) as login_time 
from user_login_info

    （2）其次我们使用窗口排名函数row_number对同一用户的不同登录时间进行排名，得到新一列为rk。

select
    uid, 
    login_time, 
    row_number() over(partition by uid order by login_time) as rk
from
(
    select
        uid, 
        distinct date(login_time) as login_time 
    from user_login_info
) t1

    查询结果如图所示：

    （3）之后用**date_sub**函数计算登录时间login_time一列加上rk天之后生成新的一列sub_date。**假如在表格中，同一用户的sub_date相同则说明相同sub_date数据的行是连续登录使用的情况。**

select *, DATE_SUB(login_time, INTERVAL rk DAY) as sub_date
from
(
    select
        uid, 
        login_time, 
        row_number() over(partition by uid order by login_time) as rk
    from
    (
        select
            uid, 
            distinct date(login_time) as login_time 
        from user_login_info
    ) t1
) t2

    查询结果如图所示：

    （4）之后我们对得到的查询结果，按照用户id，登录时间进行分组计数，得到的计数结果就是用户连续登录多少天的情况记录。

select uid, count(*) as 连续登录天数
from
(
    SELECT *, DATE_SUB(login_time, INTERVAL rk DAY) AS sub_date
    from
    (
        select
            uid, 
            login_time, 
            row_number() over(partition by uid order by login_time) as rk
        from
        (
            select
                uid, 
                distinct date(login_time) as login_time 
            from user_login_info
        ) t1
    ) t2
) t3
group by uid, sub_date

    （5）之后我们可以在此查询结果上，根据需要用having条件就可以筛选出我们想要得知的连续N天登录的用户id

    完整代码如下：

select uid, count(*) as 连续登录天数
from
(
    SELECT *, DATE_SUB(login_time, INTERVAL rk DAY) AS sub_date
    from
    (
        select
            uid, 
            login_time, 
            row_number() over(partition by uid order by login_time) as rk
        from
        (
            select
                uid, 
                distinct date(login_time) as login_time 
            from user_login_info
        ) t1
    ) t2
) t3
group by uid, sub_date
having 连续登录天数 = N

求解思路参考鸡康博主的博客内容

若有错误之处，请批评指正！