基于日期实现分组重置的row_number序列的SQL问题

你的需求是对同一bc下连续出现的相同io进行组内序号计数，而非全局相同io的累计计数。原SQL的partition by bc, io会把所有相同io的记录归为同一分区，导致中断后再次出现的io继续累计序号，这不符合预期。

实现思路 #

1. 识别io的变化节点：通过LAG()函数获取上一行的io值，对比当前行io，标记出io发生变化的位置。
2. 生成连续分组ID：对变化标记进行累加，将连续相同的io划分为同一分组。
3. 组内生成序号：基于分组ID和bc进行分区，使用row_number()生成组内序号。

完整SQL代码 #

WITH ranked_data AS (
    SELECT 
        tt.*,
        -- 标记io发生变化的行（新分组开始）
        CASE WHEN LAG(io) OVER(PARTITION BY bc ORDER BY date) != io THEN 1 ELSE 0 END AS group_change
    FROM (
        SELECT '1a' as bc, 11 as io, '2021-01-01' as date
        UNION ALL
        SELECT '1a' as bc, 11 as io, '2021-01-02' as date
        UNION ALL
        SELECT '1a' as bc, 12 as io, '2021-01-03' as date
        UNION ALL
        SELECT '1a' as bc, 11 as io, '2021-01-04' as date
    ) AS tt
),
grouped_data AS (
    SELECT 
        *,
        -- 累加变化标记，生成连续分组ID
        SUM(group_change) OVER(PARTITION BY bc ORDER BY date) AS group_id
    FROM ranked_data
)
SELECT 
    bc, io, date,
    -- 按bc和分组ID生成组内序号
    ROW_NUMBER() OVER(PARTITION BY bc, group_id ORDER BY date) AS rn
FROM grouped_data
ORDER BY date;

执行结果 #

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