如何通过条件ROW_NUMBER在SQL中生成符合规则的REQ_COL列？

原始数据 #

ID  FLAG
1   Y
1   N
1   Y
1   N
1   N
1   N
1   N
1   Y
1   N
2   N
2   Y
2   N
2   N
2   Y
2   Y
2   N

期望输出 #

ID  FLAG    REQ_COL 
1   Y       null
1   N       1
1   Y       null
1   N       1
1   N       2
1   N       3
1   N       4
1   Y       null
1   N       1
2   N       null
2   Y       null
2   N       1
2   N       2
2   Y       null
2   Y       null
2   N       1

REQ_COL生成规则 #

• 每个ID的第一条记录，REQ_COL为null
• 当FLAG=Y时，REQ_COL为null
• 若前一条记录的FLAG为Y，后续出现连续的N时，REQ_COL依次为1、2、3……
• 其他情况REQ_COL为null

解决方案 #

可以通过窗口函数实现，核心思路是先给每个符合条件的连续N序列分组，再在组内生成序号，最后结合规则过滤出null的情况。

以下是适用于大多数SQL数据库（如MySQL 8+、PostgreSQL、SQL Server等）的代码：

WITH numbered_data AS (
    SELECT 
        ID,
        FLAG,
        -- 给每个ID内的记录按顺序编号
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS rn,
        -- 标记每个连续N的组：当FLAG=N且前一条是Y时，开启新组
        SUM(CASE WHEN FLAG = 'N' AND LAG(FLAG) OVER (PARTITION BY ID ORDER BY (SELECT NULL)) = 'Y' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS group_id
    FROM your_table
),
group_counts AS (
    SELECT 
        ID,
        FLAG,
        rn,
        group_id,
        -- 在每个组内生成序号
        ROW_NUMBER() OVER (PARTITION BY ID, group_id ORDER BY rn) AS seq_num
    FROM numbered_data
)
SELECT 
    ID,
    FLAG,
    CASE 
        -- 第一条记录返回null
        WHEN rn = 1 THEN NULL
        -- FLAG=Y返回null
        WHEN FLAG = 'Y' THEN NULL
        -- 仅当属于有效组（group_id不为0）且当前是N时返回序号
        WHEN FLAG = 'N' AND group_id > 0 THEN seq_num
        -- 其他情况返回null
        ELSE NULL
    END AS REQ_COL
FROM group_counts
ORDER BY ID, rn;

代码说明 #

1. numbered_data CTE：

   • 用ROW_NUMBER()给每个ID内的记录排序（若有时间戳或其他明确排序字段，建议替换ORDER BY (SELECT NULL)为实际字段，保证顺序稳定）
   • 用SUM() OVER()生成group_id：每当遇到前一条是Y、当前是N的情况，组ID加1，这样每个符合规则的连续N序列会被分到同一个组

2. group_counts CTE：在每个ID和group_id的分组内，生成组内的递增序号seq_num

3. 最终查询：通过CASE语句匹配所有规则，生成符合要求的REQ_COL列

如果是MySQL 5.x这类不支持CTE的版本，可将CTE改为子查询嵌套，逻辑保持一致。

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