如何按Person分组，聚合连续相同DValue的起止年份？

问题分析 #

你遇到的是典型的岛屿与缺口（Islands and Gaps） SQL场景：需要将同一Person下，连续年份且DValue相同的记录合并为「Person、DValue、STARTYEAR、ENDYEAR」的形式。之前用DENSE_RANK失败的核心原因是：它会对全局相同的DValue分配统一排名，无法区分非连续的相同值（比如2018-2019的100和2021-2022的100会被错误归为同一组）。

解决方案：基于行号差值的分组键 #

核心思路是通过两个ROW_NUMBER()窗口函数的差值生成唯一分组标识，让连续相同DValue的记录拥有相同的分组ID，非连续的则自动生成不同ID。

步骤1：生成分组键 #

先通过窗口函数计算两个行号，再取差值作为分组ID：

SELECT 
    Person,
    Year,
    DValue,
    -- 按Person分组、年份排序的全局行号
    ROW_NUMBER() OVER (PARTITION BY Person ORDER BY Year) AS rn1,
    -- 按Person+DValue分组、年份排序的行号
    ROW_NUMBER() OVER (PARTITION BY Person, DValue ORDER BY Year) AS rn2,
    -- 差值作为分组ID：连续相同DValue时差值不变，值变化时差值改变
    ROW_NUMBER() OVER (PARTITION BY Person ORDER BY Year) - 
    ROW_NUMBER() OVER (PARTITION BY Person, DValue ORDER BY Year) AS group_id
FROM #TEST
WHERE Person = 2;

步骤2：按分组键聚合 #

基于生成的分组ID，聚合得到每个连续组的起止年份：

WITH CTE_Grouped AS (
    SELECT 
        Person,
        DValue,
        Year,
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY Year) - 
        ROW_NUMBER() OVER (PARTITION BY Person, DValue ORDER BY Year) AS group_id
    FROM #TEST
    WHERE Person = 2
)
SELECT 
    Person,
    DValue,
    MIN(Year) AS STARTYEAR,
    MAX(Year) AS ENDYEAR
FROM CTE_Grouped
GROUP BY Person, DValue, group_id
ORDER BY STARTYEAR;

逻辑验证 #

假设你的测试数据如下：

执行CTE后，group_id会是：

最终聚合结果为：

完美区分了非连续的相同DValue分组。

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