SQL Server分组/分区合并分类历史表连续时间段问题求解

你之前的方案出错的核心原因是：使用RANK()开窗时，分区规则是personid + categoryid，同一个人员的所有同分类记录都会被划分到同一个分区，自然不会因为中间穿插其他分类而拆分，不符合需求。

这个需求属于SQL经典的「时间区间合并（岛屿问题）」，核心逻辑是给同一个人员下连续未中断的同分类记录生成唯一的分组标识，再按该标识分组聚合即可。

实现思路 #

1. 用LAG()开窗函数获取同人员上一条记录的分类ID，判断当前分类是否和上一条一致
2. 生成分组标记：分类发生变化时标记为1，否则为0
3. 对标记做累加求和，得到每个连续同分类段的唯一组ID
4. 最后按人员ID、分类ID、组ID分组，取最小开始时间、最大结束时间即可

完整SQL代码 #

WITH step1 AS (
    -- 取同人员上一行的分类ID
    SELECT 
        *,
        LAG(categoryid) OVER (PARTITION BY personid ORDER BY startdate) AS prev_category
    FROM #category
),
step2 AS (
    -- 生成分组标记：分类变化或第一条记录时标记为1
    SELECT 
        *,
        CASE WHEN prev_category IS NULL OR prev_category != categoryid THEN 1 ELSE 0 END AS group_flag
    FROM step1
),
step3 AS (
    -- 累加标记得到连续段的唯一组ID
    SELECT 
        *,
        SUM(group_flag) OVER (PARTITION BY personid ORDER BY startdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM step2
)
-- 分组聚合得到最终结果
SELECT 
    personid,
    categoryid,
    MIN(startdate) AS startdate,
    MAX(enddate) AS enddate
FROM step3
GROUP BY personid, categoryid, group_id
ORDER BY personid, startdate

结果说明 #

执行上述代码得到的结果和你给出的预期基本一致，仅personid=2、categoryid=4的起始时间为原始数据中的10/04/2015，你给出的预期结果里写的01/04/2015属于笔误，原始数据中personid=2的分类1结束时间为09/04/2015，和10/04/2015是衔接的，符合业务逻辑。

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