SQL面试题挑战07：间隔连续问题

SQL面试题挑战07：间隔连续问题

问题

下面是某游戏公司记录的用户每日登录数据, 计算每个用户最大的连续登录天数，定义连续登录时可以间隔一天。举例：如果一个用户在 1,3,5,6,9 登录了游戏，则视为连续 6 天登录。

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

建表语句

drop table if exists login;
create table login(
    id varchar(20),
    dt date
);
insert into login 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')

解答

思路：

首先开窗按id分组按dt排序，然后获取前一行dt（默认值设一个很小的值），然后将dt和前一行dt求差,结果如下：

id	dt	diff
1001	2021-12-12	8381
1001	2021-12-13	1
1001	2021-12-14	1
1001	2021-12-16	2
1001	2021-12-19	3
1001	2021-12-20	1
1002	2021-12-12	8381
1002	2021-12-16	4
1002	2021-12-17	1

可以发现如果diff大于2那么说明当前行和前面已经不连续了。为此我们可以diff来打标记，如果diff大于3那么就设置标记为1，否则为0，结果如下

id	dt	flag
1001	2021-12-12	1
1001	2021-12-13	0
1001	2021-12-14	0
1001	2021-12-16	0
1001	2021-12-19	1
1001	2021-12-20	0
1002	2021-12-12	1
1002	2021-12-16	1
1002	2021-12-17	0

可以看到flag中，1中间的行就是连续登录的天数。接着我们将flag累积求和，这样属于同一个连续登陆区间的flag值会相当，如下

id	g
1001	1
1001	1
1001	1
1001	1
1001	2
1001	2
1002	1
1002	2
1002	2

然后我们按照id和g分组，统计行数即是登录天数。最后按照id分组，求取最大值即可。

完整查询如下

select 
    id,
    max(cnt)max_cnt
from
    (
       select
            id,
            count(*)cnt
        from
            (
                select
                    id,
                    sum(flag)over(partition by id order by dt)g
                from
                    (
                        select
                            id,
                            dt,
                            if(datediff(dt,lag(dt,1) over (partition by id order by dt))<=2,0,1)flag
                        from
                            login
                    )t0
            )t1
        group by
            id,
            g
    ) t2