如何合并维度无变化的连续员工历史记录，取最早起始日最晚结束日

你的原有代码的问题是直接对所有维度列全局分组，会把所有维度相同的行无论中间是否出现过其他维度变更都合并，无法保留历史变化轨迹。这个场景属于典型的连续区间合并（岛屿问题），可以通过窗口函数标记连续同维度分组实现，具体逻辑如下：

实现步骤 #

1. 按EmployeeID分区，按StartDate升序排序，判断当前行的所有维度列（部门ID、岗位ID、任职状态ID）是否和上一行完全一致，不一致则标记为1，一致标记为0
2. 对上述标记做累加求和，得到的结果就是连续同维度行的唯一分组ID
3. 按EmployeeID、所有维度列、分组ID聚合，取最小StartDate和最大EndDate即可

完整SQL代码 #

WITH mark_diff AS (
    SELECT 
        *,
        -- 判断当前行和上一行维度是否一致，不一致打标记1
        CASE WHEN 
            LAG(DepartmentID) OVER(PARTITION BY EmployeeID ORDER BY StartDate) = DepartmentID
            AND LAG(JobID) OVER(PARTITION BY EmployeeID ORDER BY StartDate) = JobID
            AND LAG(PositionStatusID) OVER(PARTITION BY EmployeeID ORDER BY StartDate) = PositionStatusID
        THEN 0 ELSE 1 END AS is_diff
    FROM EmployeeHistory
),
group_continuous AS (
    SELECT 
        *,
        -- 累加标记得到连续同维度分组ID
        SUM(is_diff) OVER(PARTITION BY EmployeeID ORDER BY StartDate) AS group_id
    FROM mark_diff
)
SELECT 
    MAX(EmployeeHistoryID) AS EmployeeHistoryID,
    EmployeeID,
    DepartmentID,
    JobID,
    PositionStatusID,
    MIN(StartDate) AS StartDate,
    MAX(EndDate) AS EndDate
FROM group_continuous
GROUP BY EmployeeID, DepartmentID, JobID, PositionStatusID, group_id
ORDER BY EmployeeID, StartDate;

执行结果验证 #

针对你提供的测试数据，执行后输出结果如下，完全符合需求：

如果你的业务允许历史记录存在时间间隔，仅需合并维度相同的连续行（不管间隔），上述代码无需修改；如果要求仅合并时间连续（下一行StartDate = 上一行EndDate + 1天）的同维度行，可在is_diff的判断逻辑中新增时间连续性校验即可。

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