如何用T-SQL按pointsAchieved分组连续日期区间？

问题背景 #

现有CPD注册数据集：

startDate    endDate     pointsAchieved
01/07/2013   30/06/2014  0
05/10/2014   30/06/2015  0
01/07/2015   30/06/2016  0
01/07/2016   30/06/2017  1
01/07/2017   30/06/2018  1  
30/06/2018   30/06/2019  0
10/12/2019   30/06/2020  0
07/06/2021   30/06/2022  1
01/07/2022   30/06/2023  0

需求是：按pointsAchieved分组连续的日期区间，展示每组的最早startDate、最晚endDate，并按startDate排序，预期输出：

startDate    endDate     pointsAchieved
01/07/2013   30/06/2016  0
01/07/2016   30/06/2018  1
01/07/2018   30/06/2020  0
07/06/2021   30/06/2022  1
01/07/2022   30/06/2023  0

错误解法分析 #

原尝试的T-SQL语句直接按pointsAchieved分组，会把所有同值的记录合并为一组，忽略了区间的连续性：

select Min(startDate) as startDate, Max(endDate) as endDate, pointsAchieved
from cpd_enrolments
where userId=XXXX
group by pointsAchieved
order by startDate

得到错误结果：

startDate    endDate     pointsAchieved
01/07/2013   30/06/2023  0
01/07/2016   30/06/2022  1

正确T-SQL实现 #

要实现连续相同值的分组，需要用窗口函数生成分组标识，具体代码如下：

WITH ranked_data AS (
    SELECT 
        startDate,
        endDate,
        pointsAchieved,
        -- 生成分组ID：当pointsAchieved变化时，分组ID递增
        ROW_NUMBER() OVER (ORDER BY startDate) 
        - ROW_NUMBER() OVER (PARTITION BY pointsAchieved ORDER BY startDate) AS group_id
    FROM cpd_enrolments
    WHERE userId = XXXX
)
SELECT 
    MIN(startDate) AS startDate,
    MAX(endDate) AS endDate,
    pointsAchieved
FROM ranked_data
GROUP BY pointsAchieved, group_id
ORDER BY startDate;

逻辑说明 #

1. 生成分组ID：通过两个ROW_NUMBER()函数的差值来标识连续的相同pointsAchieved组。第一个ROW_NUMBER()按startDate全局排序，第二个按pointsAchieved分区后排序，两者的差值在连续相同值的区间内保持不变，值变化时差值会递增，从而形成唯一的分组ID。
2. 分组聚合：按pointsAchieved和group_id分组，取每组的最早startDate和最晚endDate，最后按startDate排序得到目标结果。

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