基于分组VARCHAR值，按序列范围统计记录数的SQL Server实现

需要在SQL Server 2016及以上版本中，计算按ts（时间）排序后形成的连续prop1数据块的以下统计信息：

• 块内prop1的最大时间（MAX_OF_PROP1_IN_BLOCK）
• prop1值
• prop2值
• 块内prop1总记录数（COUNT_PROP1_IN_BLOCK）
• 块内对应prop2的记录数（COUNT_OF_PROP2_IN_BLOCK）

尝试过使用窗口函数，但得到的是全局范围内prop1/prop2的统计值，而非按连续块范围统计。已尝试的代码如下：

DECLARE @dataTable TABLE
                   (
                       ts DATETIME, 
                       prop1 VARCHAR(4), 
                       prop2 VARCHAR(2)
                   );

INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:51:00', 'AAAA', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:50:00', 'AAAA', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:49:00', 'AAAA', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:48:00', 'BBBB', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:47:00', 'BBBB', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:46:00', 'BBBB', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:45:00', 'AAAA', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:44:00', 'AAAA', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:43:00', 'AAAA', '02');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:32:00', 'AAAA', '02');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:41:00', 'CCCC', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:40:00', 'CCCC', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:39:00', 'CCCC', '02');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:38:00', 'BBBB', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:37:00', 'BBBB', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:36:00', 'BBBB', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:35:00', 'AAAA', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:34:00', 'AAAA', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:33:00', 'CCCC', '02');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:32:00', 'CCCC', '02');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:31:00', 'CCCC', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:30:00', 'CCCC', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:29:00', 'CCCC', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:28:00', 'CCCC', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:27:00', 'CCCC', '01');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:26:00', 'CCCC', '02');
INSERT INTO @dataTable (ts, prop1, prop2) VALUES ('2023-05-01 10:25:00', 'CCCC', '02');

SELECT 
    *, 
    MAX(ts) OVER (PARTITION BY prop1 ORDER BY ts DESC), 
    COUNT(ts) OVER (PARTITION BY prop1 ORDER BY ts DESC )
FROM 
    @dataTable 
ORDER BY 
    ts DESC;

预期结果如下：

解决方案 #

这是典型的连续区间（孤岛）问题，核心是先识别出按ts排序后连续的prop1块，再基于这些块做聚合统计。可以通过CTE分步实现：

WITH BlockGroups AS (
    SELECT 
        *,
        -- 当当前prop1与前一行不同时，标记为新块起点
        CASE WHEN LAG(prop1) OVER (ORDER BY ts DESC) = prop1 THEN 0 ELSE 1 END AS IsNewBlock
    FROM @dataTable
),
-- 累计求和生成唯一的块ID
BlockIDs AS (
    SELECT 
        *,
        SUM(IsNewBlock) OVER (ORDER BY ts DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BlockID
    FROM BlockGroups
),
-- 统计每个块的总记录数和最大时间
BlockStats AS (
    SELECT 
        BlockID,
        prop1,
        MAX(ts) AS MAX_OF_PROP1_IN_BLOCK,
        COUNT(*) AS COUNT_PROP1_IN_BLOCK
    FROM BlockIDs
    GROUP BY BlockID, prop1
),
-- 统计每个块内各prop2的记录数
Prop2Stats AS (
    SELECT 
        BlockID,
        prop2,
        COUNT(*) AS COUNT_OF_PROP2_IN_BLOCK
    FROM BlockIDs
    GROUP BY BlockID, prop2
)
-- 关联统计结果，输出最终数据
SELECT 
    bs.MAX_OF_PROP1_IN_BLOCK,
    bs.prop1,
    ps.prop2,
    bs.COUNT_PROP1_IN_BLOCK,
    ps.COUNT_OF_PROP2_IN_BLOCK
FROM BlockStats bs
JOIN Prop2Stats ps ON bs.BlockID = ps.BlockID
ORDER BY bs.MAX_OF_PROP1_IN_BLOCK DESC;

代码说明 #

1. BlockGroups：用LAG函数对比当前行和前一行的prop1，标记新块的起点。
2. BlockIDs：通过累计求和IsNewBlock值，为每个连续prop1块生成唯一的BlockID，确保相同连续块的记录拥有同一个ID。
3. BlockStats：按BlockID和prop1分组，统计每个块的最大时间和总记录数。
4. Prop2Stats：按BlockID和prop2分组，统计每个块内各prop2的记录数。
5. 最后关联两个统计结果，按块的最大时间倒序输出，与预期结果一致。

这个方案兼容SQL Server 2016及以上版本，利用窗口函数准确识别连续块，再通过分组聚合得到所需的统计值。

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