如何基于时间戳差值与用户ID为数据表分配session_id？

原始数据表 #

Session ID分配规则 #

• 若两条记录的timestamp差值小于1分钟，则属于同一session；
• 若差值大于1分钟，则session_id为前一条的session_id加1；
• 若user_id发生变化，session_id同样为前一条的session_id加1。

期望结果表 #

实现思路与SQL代码 #

你可以通过窗口函数+累计求和的方式实现，核心是先标记出需要开启新session的行，再对标记累计求和得到session_id，具体实现如下：

步骤说明 #

1. 用LAG()窗口函数获取当前行的上一行user_id和timestamp；
2. 判断当前行是否需要开启新session：要么user_id与上一行不同，要么当前行和上一行的timestamp差值≥60秒；
3. 对上述判断结果（1代表新session，0代表同session）做累计求和，得到连续的session_id。

示例SQL代码 #

SELECT
    user_id,
    timestamp,
    SUM(is_new_session) OVER (ORDER BY user_id, timestamp) AS session_id
FROM (
    SELECT
        user_id,
        timestamp,
        CASE
            -- 第一行默认是新session
            WHEN LAG(user_id) OVER (ORDER BY user_id, timestamp) IS NULL THEN 1
            -- user_id变化 或 时间差≥60秒，标记为新session
            WHEN user_id != LAG(user_id) OVER (ORDER BY user_id, timestamp) THEN 1
            WHEN timestamp - LAG(timestamp) OVER (ORDER BY user_id, timestamp) >= 60 THEN 1
            ELSE 0
        END AS is_new_session
    FROM your_table_name
) t;

代码解释 #

• LAG(user_id) OVER (ORDER BY user_id, timestamp)：按user_id和timestamp排序后，获取当前行的上一行user_id；
• CASE语句：判断是否需要开启新session，满足条件返回1，否则返回0；
• SUM(is_new_session) OVER (...)：对新session标记做累计求和，每遇到一个1，session_id自动加1，实现连续编号。

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