SQL Server按时间顺序实现连续Part/Batch块分区递增排名

问题背景 #

待处理的SQL Server数据表包含Part、Batch、TransactionDate三个字段，需要计算符合以下规则的排名列Rank：

• 结果集先按Part升序排序，相同Part下按TransactionDate升序排序
• 遇到新的Part值时，排名从1重置
• 连续相邻的相同Part+Batch数据块内，所有行的Rank值保持一致
• 同一Part下相邻行的Batch发生变化时，即使该Batch值之前出现过，排名也加1
• 每个连续数据块的行数不固定

原有错误写法：DENSE_RANK() OVER (PARTITION BY Part, Batch ORDER BY TransactionDate)
缺陷：会将同一Part下所有相同Batch的行归为同一排名，无法识别时间顺序上的连续块边界。例如Part=1下后续重复出现的Batch=1001会和最早出现的1001块同样标记为1，不符合预期（预期该重复块排名为3）。

实现逻辑 #

该需求属于经典的Gaps and Islands（间隙与岛屿）场景，核心是先识别连续相同批次的独立块，再基于块生成排名：

1. 用LAG()窗口函数取同Part下按时间排序的上一行Batch值，标记当前行是否为新块的起始行
2. 对同Part下的新块标记累计求和，为每个独立连续块生成唯一ID
3. 按Part分区，基于块ID用DENSE_RANK()生成最终排名，保证非连续的同Batch块不会被合并排名

可直接运行的查询代码 #

请将代码中的YourTableName替换为实际表名：

WITH MarkNewBlock AS (
    SELECT
        Part,
        Batch,
        TransactionDate,
        -- 与上一行Batch不同则标记为新块起始
        CASE 
            WHEN LAG(Batch) OVER (PARTITION BY Part ORDER BY TransactionDate) = Batch THEN 0
            ELSE 1
        END AS IsBlockStart
    FROM YourTableName
),
GenerateBlockId AS (
    SELECT
        Part,
        Batch,
        TransactionDate,
        -- 累计求和生成每个连续块的唯一标识
        SUM(IsBlockStart) OVER (
            PARTITION BY Part 
            ORDER BY TransactionDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS BlockId
    FROM MarkNewBlock
)
SELECT
    Part,
    Batch,
    TransactionDate,
    DENSE_RANK() OVER (PARTITION BY Part ORDER BY BlockId) AS [Rank]
FROM GenerateBlockId
ORDER BY Part, TransactionDate;

效果验证 #

用如下测试数据验证：

运行代码后返回结果完全匹配预期，重复出现的非连续Batch=1001块会正确标记为Rank=3，新Part下排名自动从1重置。

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