【sql开发】间隔连续问题

数据：

某游戏公司记录的用户每日登录数据 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 天登录

解题思路：

先使用lag根据dt列生成一列，使用datediff对dt和刚才生成的相减，得出时间差，并使用if对其判断，使在连续的标记为一组（0，1）rn，嵌套，使用sum(rn)列，使其所有的连续登录，被标记为一组 c1,再嵌套，根据id和c1分组使用datediff（max(dt),min(dt)）得出连续天数c2，最后嵌套，根据id分组，查出最大连续天数。

hive代码：

select id,max(c2) max_day
from(
    select id,dt,datediff(max(dt),min(dt))+1 c2
    from (
        select id,dt,sum(rn)over(partition by id order by dt)c1
        from (
            select id,dt,if(datediff(dt,lag(dt,1,dt) over(partition by id order by dt))<=2,0,1)rn
            from t24
        )t1
    )t2 group by id,c1
)t3 group by id