SQLite3实现连续同组记录的序号重置计数需求

需求说明 #

需要为连续的home_away分组生成序号，每当分组切换（如home变away或反之）时，序号从1重新开始，最终得到的stand列值应为1,2,1,2,1,2。

正确解决方案 #

通过生成动态分组ID，再在每个分组内生成序号的方式实现：

WITH group_markers AS (
    SELECT 
        *,
        -- 标记新分组起始：第一行或当前行与前一行home_away不同
        CASE 
            WHEN lag(home_away) OVER (ORDER BY game_pk) IS NULL THEN 1
            WHEN home_away != lag(home_away) OVER (ORDER BY game_pk) THEN 1
            ELSE 0
        END AS is_new_group
    FROM so_demo
),
group_ids AS (
    SELECT 
        *,
        -- 累计求和生成唯一分组ID
        sum(is_new_group) OVER (ORDER BY game_pk) AS group_id
    FROM group_markers
)
SELECT 
    game_pk,
    game_ds,
    away_team,
    home_team,
    home_away,
    row_number() OVER (PARTITION BY group_id ORDER BY game_pk) AS stand
FROM group_ids
ORDER BY game_pk;

执行结果 #

逻辑解释 #

1. 标记新分组：利用lag()函数对比当前行与前一行的home_away值，第一行或值发生变化时标记为新分组起始（is_new_group=1）。
2. 生成动态分组ID：对is_new_group进行累计求和，每遇到一个新分组，求和结果递增，从而为每段连续的同类型记录分配唯一的group_id。
3. 生成组内序号：通过row_number()按group_id分区，每个分组内按game_pk排序，得到从1开始的连续序号，实现分组中断后重置计数的效果。

对比原有尝试的问题 #

• 直接按home_away分区会将所有同类型记录归为一个大组，无法区分中断后的新分组，导致序号持续递增。
• 用lead/lag标记起始行的思路正确，但缺少将标记转化为分组ID的步骤，无法直接生成所需序号。

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