SQL面试题-间断N天连续问题

1.题目

某游戏公司记录的用户每日登录数据,表名为user_login,字段为id,dt.
id dt
1001 2021-12-12
1002 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1002 2021-12-16
1001 2021-12-19
1002 2021-12-17
1001 2021-12-20
计算每个用户最大的连续登录天数,可以间隔一天。
解释:如果一个用户在1,3,5,6登录游戏,则视为连续6天登录。
1001用户12、13、14、16号为连续登录,连续天数为5
1001用户19、20号为连续登录,连续天数为2
1002用户12号为连续登录,连续天数为1
1002用户16、17号为连续登录,连续天数为

2.数据准备

create table if not exists user_login_zhinanzhen(
    id int,
    dt varchar(32)
);

insert into user_login_zhinanzhen
values
    (1001, '2021-12-12'),
    (1002, '2021-12-12'),
    (1001, '2021-12-13'),
    (1001, '2021-12-14'),
    (1001, '2021-12-16'),
    (1002, '2021-12-16'),
    (1001, '2021-12-19'),
    (1002, '2021-12-17'),
    (1001, '2021-12-20');

select * from user_login_zhinanzhen;

3.解题思路

• 还是一样先分析什么事大维度和小维度，这里大维度是用户，小维度是登录时间
• 和其他不一样的是间隔一天，也算作登录，要看出这里的言外之意说的就是维度的排名需要稍作变化，从而把相差一天的数据也算在同一个组内
• 相减，求出组号，需要验证相差一天分在一个组号内
• 按照组号聚合求值

4.解题代码

T1：按照大维度，即用户id分组开窗排名。这里开窗思路和普通连续的例子没有区别。

    select
        id,
        dt,
        row_number() over (partition by id order by dt) rm_day
        from user_login_zhinanzhen

T2：小维度排名，这里思维可能比较抽象，第二次开窗是求上一次排名的时间是多少天前。第一条记录默认为一个很大的数。

   select
       id,
       dt,
       datediff(dt,lag(dt,1,'1000-10-01') over (partition by id order by dt)) as day_diff
   from t1

结果：可以观察到，只要是断开登录超过两天的那么他们的连续组肯定会变化

T3：使用递增开窗sum(if( ,1,0)) over() 可以实现这个组号

   select id,
       dt,
       sum(if(day_diff>2,1,0)) over (partition by id order by dt rows between unbounded preceding and current row) group_id
    from t2

结果：组号出来了，那么接下来就是和普通的连续一样的分组聚合了

T4：分组聚合

 select
        id,
        group_id,
        (datediff(max(dt),min(dt))+1) as group_day
    from t3
    group by id, group_id

T5:筛选需求

select id,max(group_day) from t4 group by id

结果：

总结：

不管连续问题怎么变化，都是按照大维度和小维度去排名，最后按照要求分成组，组内在聚合。

就像不管离家多远，只要找到了回家的路，回到家，就能分清楚每个家有多少人一样。

又可以将这个“寻找回家路”的过程类比成偏移量，现在的位置减去偏移量就可以回到原来的位置，这里只是偏移次数不一样。
ps：
这里若是要求断开10天也算连续，思考一下怎么求吧