基于30分钟间隔创建Session ID分组的技术需求

这个问题属于典型的时间间隙分组场景——同一ID下，当两条记录的时间间隔超过30分钟时，视为不同会话。单独使用Row_Number()无法解决，因为它仅能实现排序编号，无法识别时间间隔的断点。以下是通用的SQL解决方案，适配多数支持窗口函数的数据库（如PostgreSQL、BigQuery、MySQL 8+等）。

核心思路 #

1. 计算同ID下每条记录与前一条记录的时间差
2. 标记超过30分钟的时间断点（新会话起点）
3. 累计断点标记生成唯一的Session_ID
4. 分组聚合得到每个会话的时间范围

完整SQL代码（标准SQL） #

WITH session_breaks AS (
    SELECT
        ID,
        Session_TM,
        -- 计算当前记录与同ID上一条记录的时间差（分钟）
        TIMESTAMPDIFF(MINUTE, LAG(Session_TM) OVER (PARTITION BY ID ORDER BY Session_TM), Session_TM) AS time_diff_min,
        -- 标记新会话起点：第一条记录 或 时间差超过30分钟
        CASE
            WHEN LAG(Session_TM) OVER (PARTITION BY ID ORDER BY Session_TM) IS NULL THEN 1
            WHEN TIMESTAMPDIFF(MINUTE, LAG(Session_TM) OVER (PARTITION BY ID ORDER BY Session_TM), Session_TM) > 30 THEN 1
            ELSE 0
        END AS is_new_session
    FROM your_table_name
),
session_ids AS (
    SELECT
        ID,
        Session_TM,
        -- 累计新会话标记，生成每个ID下的唯一Session_ID
        SUM(is_new_session) OVER (PARTITION BY ID ORDER BY Session_TM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Session_ID
    FROM session_breaks
)
-- 分组聚合得到最终结果
SELECT
    ID,
    MIN(Session_TM) AS Session_TM_Min,
    MAX(Session_TM) AS Session_TM_Max,
    Session_ID
FROM session_ids
GROUP BY ID, Session_ID
ORDER BY ID, Session_ID;

关键部分说明 #

• LAG(Session_TM) OVER (PARTITION BY ID ORDER BY Session_TM)：获取同ID中当前记录的上一条时间，用于计算时间间隔。
• 时间差函数适配：不同数据库的时间差函数有差异，需按需替换：
  • PostgreSQL：用EXTRACT(EPOCH FROM (Session_TM - LAG(Session_TM) OVER (...)))/60计算分钟差
  • BigQuery：用TIMESTAMP_DIFF(Session_TM, LAG(Session_TM) OVER (...), MINUTE)
• SUM(is_new_session) OVER (...)：累计新会话标记值，每次遇到断点时累加1，确保同会话的记录共享同一个Session_ID。

适配MySQL 5.7及以下（无窗口函数） #

如果使用不支持窗口函数的旧版MySQL，可通过变量实现：

SET @prev_id = NULL;
SET @prev_time = NULL;
SET @session_id = 0;

SELECT
    ID,
    MIN(Session_TM) AS Session_TM_Min,
    MAX(Session_TM) AS Session_TM_Max,
    Session_ID
FROM (
    SELECT
        ID,
        Session_TM,
        CASE
            WHEN ID != @prev_id THEN @session_id := 1
            WHEN TIMESTAMPDIFF(MINUTE, @prev_time, Session_TM) > 30 THEN @session_id := @session_id + 1
            ELSE @session_id
        END AS Session_ID,
        @prev_id := ID,
        @prev_time := Session_TM
    FROM your_table_name
    ORDER BY ID, Session_TM
) AS session_data
GROUP BY ID, Session_ID
ORDER BY ID, Session_ID;

为什么Row_Number()单独无效？ #

Row_Number()仅能为同ID的记录按时间生成连续编号，但无法识别时间间隔的断点——比如两条记录间隔1小时，Row_Number()会给出连续的编号，但它们属于不同会话，因此必须结合时间差计算和累计求和来生成Session_ID。

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