基于time_diffs字段条件实现SQL的row_number分组编号

需求说明 #

• 当time_diffs连续为1时，对应行归为同一组
• 当time_diffs为0时，每行单独成为一组
• 组编号随分组切换依次递增1

解决方案SQL #

我们可以通过标记分组起始点+累计求和的方式实现需求，完整SQL如下：

WITH session_with_time_diff AS (
    select session_id, 
        player_id, 
        country, 
        start_time, 
        end_time,       
        case when timestampdiff(minute, 
                                lag(end_time, 1) over(partition by player_id order by end_time)
                               , start_time) < 5 then 1
             when timestampdiff(minute, end_time
                   , lead(start_time, 1) over(partition by player_id order by start_time)) < 5 then 1
        else 0
        end as time_diffs
    from game_sessions
    where player_id = 1
)
SELECT 
    *,
    SUM(group_start) OVER (PARTITION BY player_id ORDER BY start_time) AS expected_result
FROM (
    SELECT 
        *,
        CASE 
            -- 第一行直接作为新分组起始
            WHEN lag(time_diffs) OVER (PARTITION BY player_id ORDER BY start_time) IS NULL THEN 1
            -- 上一行不是连续1的情况，当前行开启新分组
            WHEN lag(time_diffs) OVER (PARTITION BY player_id ORDER BY start_time) != 1 THEN 1
            -- 当前行time_diffs为0，单独成组
            WHEN time_diffs = 0 THEN 1
            -- 其余情况属于当前分组，不触发新分组
            ELSE 0
        END AS group_start
    FROM session_with_time_diff
) t
ORDER BY player_id, start_time;

逻辑解释 #

1. CTE预处理：保留原查询的time_diffs计算结果，简化后续逻辑
2. 标记分组起始点：
   • 第一行无前置行，直接标记为新分组起始
   • 如果上一行的time_diffs不是1，说明当前行是新分组的开始
   • 只要当前行time_diffs为0，无论前置行状态，都单独成组
3. 生成组编号：对标记的起始点做累计求和，得到连续递增的组编号，完全匹配需求规则

结果对比 #

原查询结果

修改后查询结果（符合期望）

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