基于时间差条件生成session_id的SQL实现需求

看起来你需要根据相邻记录的时间差来划分会话，这个需求在用户行为分析场景里非常普遍，我来给你详细拆解实现方法：

核心思路 #

要生成符合要求的session_id，核心是以下几步：

• 获取每条记录的上一条记录的时间（用窗口函数LAG()）
• 计算当前记录与上一条的时间差，判断是否超过30分钟
• 标记出需要开启新会话的位置（时间差超30分钟或第一条记录）
• 对标记值做累加，得到连续的会话ID

不同数据库的实现示例 #

PostgreSQL 版本

PostgreSQL对时间运算支持很直观，直接用时间减法就能得到间隔：

WITH time_diff_cte AS (
    SELECT
        date_time,
        -- 计算当前与上一条记录的时间差（分钟）
        EXTRACT(MINUTE FROM date_time - LAG(date_time) OVER (ORDER BY date_time)) AS date_diff,
        -- 标记是否开启新会话：第一条记录或时间差超30分钟则标记为1
        CASE
            WHEN LAG(date_time) OVER (ORDER BY date_time) IS NULL THEN 1
            WHEN EXTRACT(MINUTE FROM date_time - LAG(date_time) OVER (ORDER BY date_time)) > 30 THEN 1
            ELSE 0
        END AS new_session_flag
    FROM your_table
)
SELECT
    date_diff,
    date_time,
    -- 累加标记值得到session_id
    SUM(new_session_flag) OVER (ORDER BY date_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM time_diff_cte
ORDER BY date_time;

MySQL 8.0+ 版本

MySQL 8.0及以上支持窗口函数，用TIMESTAMPDIFF计算时间差：

WITH time_diff_cte AS (
    SELECT
        date_time,
        TIMESTAMPDIFF(MINUTE, LAG(date_time) OVER (ORDER BY date_time), date_time) AS date_diff,
        CASE
            WHEN LAG(date_time) OVER (ORDER BY date_time) IS NULL THEN 1
            WHEN TIMESTAMPDIFF(MINUTE, LAG(date_time) OVER (ORDER BY date_time), date_time) > 30 THEN 1
            ELSE 0
        END AS new_session_flag
    FROM your_table
)
SELECT
    date_diff,
    date_time,
    SUM(new_session_flag) OVER (ORDER BY date_time) AS session_id
FROM time_diff_cte
ORDER BY date_time;

低版本MySQL（无窗口函数）

如果你的MySQL版本低于8.0，可以用用户变量来实现：

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

SELECT
    TIMESTAMPDIFF(MINUTE, @prev_time, date_time) AS date_diff,
    date_time,
    @session_id := @session_id + CASE
        WHEN @prev_time IS NULL THEN 1
        WHEN TIMESTAMPDIFF(MINUTE, @prev_time, date_time) > 30 THEN 1
        ELSE 0
    END AS session_id,
    @prev_time := date_time
FROM your_table
ORDER BY date_time;

SQL Server 版本

用DATEDIFF函数计算时间差：

WITH time_diff_cte AS (
    SELECT
        date_time,
        DATEDIFF(MINUTE, LAG(date_time) OVER (ORDER BY date_time), date_time) AS date_diff,
        CASE
            WHEN LAG(date_time) OVER (ORDER BY date_time) IS NULL THEN 1
            WHEN DATEDIFF(MINUTE, LAG(date_time) OVER (ORDER BY date_time), date_time) > 30 THEN 1
            ELSE 0
        END AS new_session_flag
    FROM your_table
)
SELECT
    date_diff,
    date_time,
    SUM(new_session_flag) OVER (ORDER BY date_time ROWS UNBOUNDED PRECEDING) AS session_id
FROM time_diff_cte
ORDER BY date_time;

注意事项 #

• 一定要确保数据按date_time排序，否则时间差计算会出错
• 如果你的数据是多用户的（比如每个用户有独立会话），需要在窗口函数中加上PARTITION BY user_id（替换为你的用户标识列），这样每个用户的session_id会独立计数
• 按照你的需求，时间差超过30分钟才开启新会话，所以判断条件用>30，如果需要包含刚好30分钟的情况，改成>=30即可

备注：内容来源于stack exchange，提问作者user20391531