SQL如何基于用户时间戳10分钟间隔规则实现会话分桶

现有代码问题说明 #

• 现有逻辑仅做了记录过滤，没有实现会话编号的累加分桶逻辑
• 注意多数SQL方言中datediff(mm, ...)实际计算的是月份差，并非分钟差，计算分钟间隔需要替换为对应正确的时间差函数

实现思路 #

1. 用lag()窗口函数获取同用户上一条记录的时间戳
2. 标记是否需要开启新会话：用户第一条记录、与上一条记录间隔超过10分钟时标记为1，否则标记为0
3. 对新会话标记做同用户下的累计求和，得到的结果就是最终的会话编号

实现代码 #

with cte1 as (
    -- 第一步：获取同用户上一条记录的时间戳
    select 
        `user`, -- user为SQL保留关键字，建议用反引号转义
        `timestamp`,
        lag(`timestamp`) over (partition by `user` order by `timestamp`) as last_timestamp
    from your_table_name -- 替换为你的实际表名
),
cte2 as (
    -- 第二步：标记新会话
    select 
        `user`,
        `timestamp`,
        case 
            when last_timestamp is null or timestampdiff(minute, last_timestamp, `timestamp`) > 10 
            then 1 
            else 0 
        end as new_session_flag
    from cte1
)
-- 第三步：累加标记生成会话ID
select 
    `user`,
    `timestamp`,
    sum(new_session_flag) over (
        partition by `user` 
        order by `timestamp` 
        rows between unbounded preceding and current row
    ) as session
from cte2
order by `timestamp`;

适配说明 #

如果你的SQL方言不支持timestampdiff，可以替换为对应分钟差计算逻辑：
比如Hive/Spark SQL可以替换为(unix_timestamp(timestamp) - unix_timestamp(last_timestamp)) / 60计算分钟差，再判断是否大于10即可。

内容的提问来源于stack exchange，提问作者user1