基于Status列添加rownum：统计CLOSED前的CANCELLED次数

需求说明 #

现有包含id、time、status列的表，需添加rownum字段，规则如下：

• CANCELLED状态的记录：rownum为当前连续CANCELLED序列内的递增序号（从1开始）
• CLOSED状态的记录：
  • 紧邻在一组CANCELLED之后的第一条CLOSED，rownum取该组CANCELLED的总条数
  • 该组后续的CLOSED记录，rownum为0
• 其他状态（如ACTIVE）的记录：rownum取最近一组CANCELLED的最后序号

预期输出 #

现有查询及问题 #

原查询语句 #

WITH CTE AS (
    SELECT
        v.BasketId,
        v.StoreId,
        x.person_id,
        v.VBasket,
        v.CreatedAt,
        FORMAT(v.CreatedAt, 'yyyy-MM-dd') AS CreatedDate,
        v.Status,
        ROW_NUMBER() OVER (PARTITION BY x.person_id, v.Status ORDER BY v.CreatedAt) AS rn
    FROM
        [Basket] v
    CROSS APPLY
        OPENJSON(v.TBasket)
        WITH (
            data NVARCHAR(MAX) AS JSON
        ) j1
    CROSS APPLY
        OPENJSON(j1.data, '$.person_ids')
        WITH (
            person_id NVARCHAR(MAX) '$'
        ) x
    WHERE
        x.person_id = '5069640_17a678bf'
),
CTE2 AS (
    SELECT
        BasketId,
        StoreId,
        person_id,
        VBasket,
        CreatedAt,
        CreatedDate,
        Status,
        CASE
            WHEN Status = 'CANCELLED' THEN
                ROW_NUMBER() OVER (PARTITION BY person_id, Status ORDER BY CreatedAt)
            ELSE
                1
        END AS rownum
    FROM CTE
)
SELECT
    person_id,
    CreatedAt ,
    Status,
    rownum
FROM CTE2
ORDER BY CreatedAt;

当前输出（不符合预期） #

更新1后的输出（仍不符合预期） #

修正后的SQL查询 #

WITH BaseData AS (
    SELECT
        x.person_id AS id,
        v.CreatedAt AS time,
        v.Status AS status,
        -- 生成分组：将连续的CANCELLED，以及紧随其后的非CANCELLED记录归为同一组
        SUM(CASE 
            WHEN LAG(v.Status, 1, '') OVER (ORDER BY v.CreatedAt) != v.Status 
                 AND (v.Status = 'CANCELLED' OR LAG(v.Status, 1, '') OVER (ORDER BY v.CreatedAt) = 'CANCELLED')
            THEN 1 
            ELSE 0 
        END) OVER (ORDER BY v.CreatedAt) AS group_id
    FROM
        [Basket] v
    CROSS APPLY
        OPENJSON(v.TBasket)
        WITH (
            data NVARCHAR(MAX) AS JSON
        ) j1
    CROSS APPLY
        OPENJSON(j1.data, '$.person_ids')
        WITH (
            person_id NVARCHAR(MAX) '$'
        ) x
    WHERE
        x.person_id = '5069640_17a678bf'
),
GroupStats AS (
    SELECT
        id,
        time,
        status,
        group_id,
        -- 组内CANCELLED记录的递增序号
        ROW_NUMBER() OVER (PARTITION BY group_id, status ORDER BY time) AS cancel_seq,
        -- 组内CANCELLED记录的总数量
        COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) OVER (PARTITION BY group_id) AS total_cancels,
        -- 组内所有记录的递增序号，用于判断是否为CANCELLED之后的第一条CLOSED
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY time) AS group_seq
    FROM BaseData
)
SELECT
    id,
    time,
    status,
    CASE
        WHEN status = 'CANCELLED' THEN cancel_seq
        WHEN status = 'CLOSED' THEN 
            CASE 
                WHEN group_seq = 1 THEN total_cancels
                ELSE 0 
            END
        ELSE COALESCE(total_cancels, 0)
    END AS rownum
FROM GroupStats
ORDER BY time;

逻辑说明 #

1. BaseData CTE：通过LAG函数检测状态切换，将连续的CANCELLED序列，以及紧随其后的非CANCELLED记录划分为同一分组，确保每组对应一个CANCELLED序列及其后续关联记录。
2. GroupStats CTE：在每个分组内，完成三个统计：
   • 给CANCELLED记录分配组内递增序号cancel_seq
   • 统计组内CANCELLED的总数量total_cancels
   • 给组内所有记录分配递增序号group_seq，用于识别是否为分组内第一条记录
3. 最终SELECT：通过CASE语句实现规则：
   • CANCELLED记录直接使用组内序号cancel_seq
   • CLOSED记录：分组第一条（即刚从CANCELLED切换的记录）取total_cancels，其余取0
   • 其他状态记录取最近分组的total_cancels（