SQL实现累计时长20分钟后重置及多场景会话数统计求助

需求说明 #

需要实现以下逻辑：

• 对每个用户的行为记录按日期分组，满足任一条件时标记新会话（SessionID=1），否则为0：
  • 1. 用户当日的第一条记录
  • 2. 当前记录与上一条记录的时间间隔超过20分钟
  • 3. 从当前会话的起始时间到当前记录的累计时长超过20分钟
• 累计时长达到20分钟后，重置累计起点为当前记录时间，后续从该点重新累计

源数据示例 #

Pick_Date_time        userID   
2024-10-01 03:00:00     A1      
2024-10-01 03:05:43     A1        
2024-10-01 03:07:10     A1        
2024-10-01 03:11:12     B1
2024-10-01 03:13:15     B1
2024-10-01 04:21:10     A1       
2024-10-01 04:26:23     A1
2024-10-01 04:35:12     A1
2024-10-01 04:42:15     A1
2024-10-01 04:56:10     A1
2024-10-01 04:04:12     B1

期望输出示例 #

Pick_Date_time        userID   SessionID  累计时长说明
2024-10-01 03:00:00     A1         1   -- 当日首次登录
2024-10-01 03:05:43     A1         0   -- 累计时长<20分钟
2024-10-01 03:07:10     A1         0   -- 累计时长<20分钟
2024-10-01 03:11:12     B1         1   -- 当日首次登录
2024-10-01 03:13:15     B1         0   -- 累计时长<20分钟
2024-10-01 04:21:10     A1         1   -- 与上一条间隔超20分钟
2024-10-01 04:26:23     A1         0   -- 累计5分钟，<20分钟
2024-10-01 04:35:12     A1         0   -- 累计12分钟，<20分钟
2024-10-01 04:39:12     B1         1   -- 与上一条间隔超20分钟
2024-10-01 04:41:03     B1         0   -- 累计1分钟，<20分钟
2024-10-01 04:42:10     B1         0   -- 累计2分钟，<20分钟
2024-10-01 04:42:15     A1         1   -- 累计21分钟，触发新会话
2024-10-01 04:47:10     A1         0   -- 重置起点，累计5分钟
2024-10-01 04:49:12     A1         0   -- 重置起点后累计7分钟

解决方案：递归CTE实现会话划分与累计重置 #

核心思路是用递归CTE逐个处理用户的记录，动态跟踪当前会话的起始时间，判断是否需要触发新会话并重置起始点：

WITH ordered_data AS (
    -- 按用户、日期、时间排序，生成行号
    SELECT 
        Pick_Date_time,
        userID,
        DATE(Pick_Date_time) AS login_date,
        ROW_NUMBER() OVER(PARTITION BY userID, DATE(Pick_Date_time) ORDER BY Pick_Date_time) AS rn
    FROM your_table_name
),
recursive_session AS (
    -- 递归起始：用户当日第一条记录
    SELECT 
        Pick_Date_time,
        userID,
        login_date,
        rn,
        1 AS SessionID,
        Pick_Date_time AS session_start_time  -- 初始会话起始时间为当前记录时间
    FROM ordered_data
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续记录
    SELECT 
        curr.Pick_Date_time,
        curr.userID,
        curr.login_date,
        curr.rn,
        -- 判断是否触发新会话：三个条件满足任一则为1，否则0
        CASE 
            -- 条件2：与上一条间隔超20分钟
            WHEN TIMESTAMPDIFF(MINUTE, prev.Pick_Date_time, curr.Pick_Date_time) > 20 THEN 1
            -- 条件3：从当前会话起始点累计超20分钟
            WHEN TIMESTAMPDIFF(MINUTE, prev.session_start_time, curr.Pick_Date_time) > 20 THEN 1
            ELSE 0
        END AS SessionID,
        -- 重置会话起始时间：触发新会话则用当前时间，否则沿用之前的起始点
        CASE 
            WHEN TIMESTAMPDIFF(MINUTE, prev.Pick_Date_time, curr.Pick_Date_time) > 20 
                 OR TIMESTAMPDIFF(MINUTE, prev.session_start_time, curr.Pick_Date_time) > 20
            THEN curr.Pick_Date_time
            ELSE prev.session_start_time
        END AS session_start_time
    FROM recursive_session prev
    JOIN ordered_data curr 
        ON curr.userID = prev.userID 
        AND curr.login_date = prev.login_date 
        AND curr.rn = prev.rn + 1
)
-- 最终输出，包含累计时长计算
SELECT 
    Pick_Date_time,
    userID,
    SessionID,
    CONCAT(TIMESTAMPDIFF(MINUTE, session_start_time, Pick_Date_time), 'min') AS 累计时长
FROM recursive_session
ORDER BY userID, Pick_Date_time;

逻辑说明 #

1. 排序与初始化：对每个用户每日的记录按时间排序，生成行号，方便递归逐行处理。
2. 递归起始点：用户每日第一条记录标记为新会话（SessionID=1），会话起始时间设为当前记录时间。
3. 递归判断逻辑：
   • 对后续每条记录，先判断与上一条记录的间隔是否超20分钟（条件2），或者与当前会话起始点的累计时长是否超20分钟（条件3），满足任一则标记新会话，并重置会话起始时间为当前记录时间。
   • 不满足则保持SessionID=0，沿用之前的会话起始时间继续累计。
4. 累计时长计算：通过当前记录时间与会话起始时间的差值，得到当前会话内的累计时长。

注意事项 #

• 该方案支持MySQL 8.0+、PostgreSQL等支持递归CTE的数据库。
• 如果是SQL Server等数据库，只需调整时间差函数为DATEDIFF(MINUTE, ...)即可。
• 需将your_table_name替换为实际表名。

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