如何让ROW_NUMBER在分区内相同值的间断处重新开始计数？

首先准备测试数据：

CREATE TABLE #t (PackedStr VARCHAR(100), Ident INT, BeginDate DATE, EndDate DATE);
INSERT #t
VALUES ('A,B,C,D,E', 86, '2019-03-18', '2019-03-27')
     , ('A,B,C,D,E', 87, '2019-03-28', '2019-04-09')
     , ('A,B,C,D,E,F,G', 88, '2019-04-10', '2019-04-15')
     , ('A,B,C,D,E', 89, '2019-04-16', '2019-04-24')
     , ('A,B,C,D,E', 90, '2019-04-25', '2019-05-14');

现有问题 #

原查询直接按PackedStr分区，导致非连续的相同PackedStr被归为同一组，结果不符合预期：

SELECT *
     , ROW_NUMBER() OVER (PARTITION BY [PackedStr] ORDER BY [Ident]) RowNumber
     , MIN( BeginDate ) OVER (PARTITION BY [PackedStr] ORDER BY [Ident]) FirstDate
     , MAX( EndDate ) OVER (PARTITION BY [PackedStr] ORDER BY [Ident] DESC) LastDate
FROM [#t]
ORDER BY [Ident];

当前结果 #

期望结果 #

解决方案 #

核心思路是用LAG()函数判断当前行的PackedStr与上一行是否相同，生成分组标识，再基于这个标识进行分区计算：

WITH GroupedData AS (
    SELECT 
        *,
        -- 当前行PackedStr与上一行不同时，生成新分组标记
        SUM(CASE WHEN LAG(PackedStr) OVER (ORDER BY Ident) = PackedStr THEN 0 ELSE 1 END) 
            OVER (ORDER BY Ident) AS GroupID
    FROM #t
)
SELECT 
    PackedStr,
    Ident,
    BeginDate,
    EndDate,
    ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Ident) AS RowNumber,
    MIN(BeginDate) OVER (PARTITION BY GroupID) AS FirstDate,
    MAX(EndDate) OVER (PARTITION BY GroupID) AS LastDate
FROM GroupedData
ORDER BY Ident;

说明 #

1. 生成GroupID：通过LAG(PackedStr) OVER (ORDER BY Ident)获取上一行的PackedStr，若与当前行不同则累加1，确保连续相同的PackedStr被分配到同一个分组ID。
2. 基于GroupID计算字段：以GroupID作为分区键，计算每个连续分组内的行号、最早开始日期和最晚结束日期，完全匹配期望结果。

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