SQL Server T-SQL：基于值的CTE ROW_NUMBER() OVER PARTITION需求调整

需要基于NAME、VAL1、VAL2生成序号，要求：

• 结果按DT日期降序排列
• 按VAL2的连续分组分配相同序号（比如PN1前2行、PN3单独行、PN1后3行各自为一组，同组序号一致）

当前使用PARTITION BY NAME,VAL1,VAL2的ROW_NUMBER()函数会把所有相同VAL2的行归为同一分区，无法区分非连续的相同VAL2区块，因此不符合预期。

示例数据 #

当前SQL语句 #

SELECT 
    NAME, VAL1, VAL2, DT,
    ROW_NUMBER() OVER (PARTITION BY NAME, VAL1, VAL2 ORDER BY DT DESC) AS seq
FROM your_table
ORDER BY DT DESC;

当前不符合预期的结果 #

期望结果 #

核心思路是识别连续相同VAL2的区块，而非单纯按VAL2分区。可以通过LAG()函数标记VAL2的变化点，再通过累计求和生成分组序号。

实现SQL #

WITH val2_change AS (
    SELECT 
        NAME, VAL1, VAL2, DT,
        -- 标记当前行与上一行（DT降序）的VAL2是否不同
        CASE WHEN LAG(VAL2) OVER (PARTITION BY NAME, VAL1 ORDER BY DT DESC) != VAL2 THEN 1 ELSE 0 END AS change_flag
    FROM your_table
),
grouped_seq AS (
    SELECT 
        *,
        -- 累计变化标记生成连续分组的序号
        SUM(change_flag) OVER (PARTITION BY NAME, VAL1 ORDER BY DT DESC) + 1 AS seq
    FROM val2_change
)
SELECT NAME, VAL1, VAL2, DT, seq
FROM grouped_seq
ORDER BY DT DESC;

逻辑解释 #

1. 标记VAL2变化点：用LAG(VAL2)获取DT降序后上一行的VAL2值，和当前行对比，若不同则记为1（表示进入新分组），相同记为0。
2. 生成分组序号：对change_flag进行累计求和，每遇到一个1，序号自动加1，这样连续相同的VAL2行会被分到同一组，得到相同的序号。
3. 最终按DT降序输出，完全匹配期望结果。

如果你的数据库支持简化窗口范围写法，SUM(change_flag)的窗口子句可以省略ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，因为默认就是从起始行到当前行的累计。

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