Hive SQL间隔连续问题

问题引入

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

id          dt1001    2021-12-121002    2021-12-121001    2021-12-131001    2021-12-141001    2021-12-161002    2021-12-161001    2021-12-191002    2021-12-171001    2021-12-20

这是个连续问题的升级版，当满足某种要求时我们也是算作连续的，所以不能使用传统的连续编号，然后做差值的解法了。核心思路解析如下：

这种解法是比较常见的，很多场景都可以这样使用。还有比如计算用户的会话数，当两次会话时间超过1分钟时就算做不同的会话，也可以这样做。

#这里假设样例数据存在tmp表里面
select
user_id
,max(diff) as max_login_days
from
(
    select
    user_id 
    ,user_group
    ,datediff(date(max(dt)),date(min(dt)),'dd')+1 as diff  --拿到每个用户下，连续时间里面最大日期与最小日期的差值加1就得到来连续天数
    from
    (
        select
        user_id
        ,dt
        -- 如果当前日期与上一个日期的差值在2之内，那么就给0，否则给1
        ,sum(if(datediff(date(dt),date(last_dt),'dd')<=2,0,1)) over(partition by user_id order by dt) as user_group
        from
        (
            select
            user_id
            ,dt
            ,lag(dt,1,dt) over(partition by user_id order by dt) as last_dt --根据user_id分组，拿到当前行的上一个日期，没有上一个就给自己本身的值
            from tmp
        )t1
    )t1
    group by user_id ,user_group
)t1
group by user_id 
;

相似问题

问题：如下为某电商公司用户访问网站的数据，包括用户id和访问时间两个字段。现有如下规则：如果某个用户的连续的访问记录时间间隔小于60秒，则属于同一个会话，现在需要计算每个用户有多少个会话。比如A用户在第1秒，60秒，200秒，230秒有三次访问记录，则该用户有2个会话，其中第一个会话是第1秒和第60秒的记录，第二个会话是第200秒和230秒的记录。

user_id     ts1001    169200000001001    169200000501002    169200000651002    169200000801001    169200001501002    16920000160

#sql可以简化，这里只是为了拆分每一步是如何做的而没有做合并。
with tmp as (
    select 1001 as user_id,16920000000 as ts
    union all
    select 1001 as user_id,16920000050 as ts
    union all
    select 1002 as user_id,16920000065 as ts
    union all
    select 1002 as user_id,16920000080 as ts
    union all
    select 1001 as user_id,16920000150 as ts
    union all
    select 1002 as user_id,16920000160 as ts
)
select
user_id
,count(distinct user_group) as user_group_cnt
from
(
    select
    user_id
    ,ts
    -- 开窗做累加
    ,sum(flag) over(partition by user_id order by ts) as user_group
    from
    (
        select
        user_id
        ,ts
        -- 判断当前行的时间与上一行的差值
        ,if(ts-last_ts<60,0,1) as flag
        from
        (
            select
            user_id
            ,ts
            -- 取当前行的上一个时间，没有上一行就给自身的时间
            ,lag(ts,1,ts) over(partition by user_id order by ts) as last_ts
            from tmp
        )t1
    )t1
)t1
group by user_id
;