公众号（阿龙学堂）：SqlBoy：经典面试题-间隔连续问题

一、需求

某游戏公司记录的用户每日登录数据

计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录。

二、分析

先将数据按照id,dt排序后得到：

依题意分析得到：

1001用户12、13、14、16号为连续登录，连续天数为5

1001用户19、20号为连续登录，连续天数为2

1002用户12号为连续登录，连续天数为1

1002用户16、17号为连续登录，连续天数为2

如果我们将以上四种情况分为四组，那四组的连续天数计算方式分别为：max(dt)-min(dt)+1，即

16-12+1=5

20-19+1=2

12-12+1=1

17-16+1=2

由此可见，该类问题就可以转换为，先将数据进行分组，再由组内最大日期减最小日期+1得到。分组问题也就是第二题。

以1001用户的数据为例，流程应该是：

三、解法

第一步：将上一行时间数据下移

select    id,    dt,    lag(dt,1,'1970-01-01') over(partition by id order by dt) lagdtfrom test3;记为 t1得到：1001    2021-12-12  1970-01-011001    2021-12-13  2021-12-121001    2021-12-14  2021-12-131001    2021-12-16  2021-12-141001    2021-12-19  2021-12-161001    2021-12-20  2021-12-19

第二步：将当前行时间减去上一行时间数据(datediff(dt1,dt2))

select    id,    dt,    datediff(dt,lagdt) flagfrom t1; 记为 t2得到：1001    2021-12-12  5645641001    2021-12-13  11001    2021-12-14  11001    2021-12-16  21001    2021-12-19  31001    2021-12-20  1

第三步：按照用户分组,同时按照时间排序,计算从第一行到当前行大于2的数据的总条数(sum(if(flag>2,1,0)))

select    id,    dt,    sum(if(flag>2,1,0)) over(partition by id order by dt) flagfrom t2;记为 t3得到：1001    2021-12-12  11001    2021-12-13  11001    2021-12-14  11001    2021-12-16  11001    2021-12-19  21001    2021-12-20  2

第四步：按照用户和flag分组,求最大时间减去最小时间并加上1

select    id,    flag,    datediff(max(dt),min(dt)) daysfrom t3group by id,flag; 记为 t4得到：1001    51001    2

第五步：取连续登录天数的最大值

select    id,    max(days)+1from    t4group by id;得到：1001    51002    2 （1002用户明细略）

第六步：将SQL拼接起来​​​​​​​

select    id,    max(days)+1from(    select        id,        flag,        datediff(max(dt),min(dt)) days    from(        select            id,            dt,            sum(if(flag>2,1,0)) over(partition by id order by dt) flag        from(            select                id,                dt,                datediff(dt,lagdt) flag            from (                select                    id,                    dt,                    lag(dt,1,'1970-01-01') over(partition by id order by dt) lagdt                from test3            )t1        )t2    )t3    group by id,flag)t4group by id;