SQL实现用户连续登录问题

查询出连续登录 N 天的用户

查询出连续登录 3 天的用户，表名 login_records 表结构如下：

思路：使用 row_number 解决

• 去重，即每个用户每一天只会有一天记录
• row_number() over(partition by userid,login_at order by login_at) ，如果有重复的取出第一个。
• ROW_NUMBER 按照 userid 分组，在组内按照登陆时间升序编上号。
• 用日期减去自己的（编号-1），如果日期是连续的话，减去日期以后会得到相同的时间，这个相同的时间就是连续登录的第一天。
• group by userid,start_at  这里start_at相同表示在同一个连续的时间段，having count(1)控制连续登录的天数。

select userid, start_at, count(1) as cnt
from
(
    select userid, 
        login_at,
        date_sub(login_at, t2.r2-1) as start_at
    from
    ( -- 同一个用户按照时间排序
        select userid, 
            login_at,
            row_number() over(partition by userid order by login_at) as r2
        from
        ( -- 去重，一个用户一天只能有一条记录
            select userid, 
                login_at, 
                row_number() over(partition by userid,login_at order by login_at) as r1
            from login_records
        ) t1
        where r1 = 1
    ) t2
) t3
group by userid, start_at
having count(1) >= 3
order by userid, start_at, cnt

查询连续 N 次都满足相同条件用户

找出连续3次刷抖音都超过40分钟的用户，注意：最近刷抖音可能不是每天都刷，也可能一天会刷多次。表名 douyin_records，表结构如下:

• 思路：使用窗口函数 lag，按照 userid 分区，login_at 升序，找到上一次、上上次的时长，然后在 where 条件里面过滤即可。

SELECT DISTINCT userid
FROM (
         SELECT userid,
                login_at,
                play_minutes,
                LAG(play_minutes, 1, 0) OVER (PARTITION BY userid ORDER BY login_at) AS last_play_minutes,
                LAG(play_minutes, 2, 0) OVER (PARTITION BY userid ORDER BY login_at) AS last_last_play_minutes
         FROM douyin_records
     ) t1
WHERE play_minutes > 40
  AND last_play_minutes > 40
  AND last_last_play_minutes > 40