SQL Server 按最新修改优先级拆分重叠的员工任职日期记录

问题背景 #

需要处理一批维护质量较差的员工任职日期表，表中存储员工在指定时间段内的任职岗位信息，目前存在大量日期重叠的任职记录：即同一员工同一时间被记录持有多个岗位，不符合业务实际逻辑。
当前数据校正规则：修改时间越晚的记录优先级越高，视为对旧数据的修正。需要消除任意时间区间内的重复任职记录，在岗位记录对应的起止日期范围内，仅保留最新修改的记录作为有效数据，结果无需返回修改时间字段。

示例数据 #

脏数据说明 #

脏数据示意图：

测试表构造SQL #

DROP TABLE IF EXISTS #Test;
CREATE TABLE #Test
( Id INT IDENTITY(1,1)
 ,Person    INT
 ,Job       INT
 ,JobStart  DATE
 ,JobEnd    DATE
 ,Modified  DATE
);

INSERT #Test
VALUES
 (1,1,'2020-01-10','2020-01-20',GETDATE())
,(1,2,'2020-01-03','2020-01-10',DATEADD(DAY,-1,GETDATE()))
,(1,3,'2020-01-03','2020-01-13',DATEADD(DAY,-2,GETDATE()))
,(1,4,'2020-01-11','2020-01-20',DATEADD(DAY,-3,GETDATE()))
,(1,5,'2020-01-15','2020-01-25',DATEADD(DAY,-4,GETDATE()))
,(1,6,'2020-01-01','2020-01-30',DATEADD(DAY,-5,GETDATE()))

预期输出效果 #

清洗后数据示意图：

预期输出结果：

可行解决方案 #

之前使用日期表的思路是正确的，问题出在对同岗位多段区间的分组逻辑缺失，补充间断识别+分组合并的逻辑即可解决问题，完整SQL如下：

-- 第一步：如果没有现成的日期维度表，先生成临时日期表覆盖所有任职日期范围
DECLARE @MinDate DATE, @MaxDate DATE;
SELECT @MinDate = MIN(JobStart), @MaxDate = MAX(JobEnd) FROM #Test;

DROP TABLE IF EXISTS #ListOfDates;
CREATE TABLE #ListOfDates ([Date] DATE PRIMARY KEY);

WITH Dates_CTE AS (
    SELECT @MinDate AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM Dates_CTE
    WHERE [Date] < @MaxDate
)
INSERT INTO #ListOfDates ([Date])
SELECT [Date] FROM Dates_CTE OPTION (MAXRECURSION 0);

-- 第二步：计算每个日期下优先级最高的岗位
WITH DailyTopJob AS (
    SELECT
        t.Person,
        d.[Date],
        t.Job,
        ROW_NUMBER() OVER (PARTITION BY t.Person, d.[Date] ORDER BY t.Modified DESC) AS rn
    FROM #Test t
    INNER JOIN #ListOfDates d 
        ON d.[Date] BETWEEN t.JobStart AND t.JobEnd
),
-- 第三步：过滤出每个日期的最高优先级岗位，生成间断标记
DailyValidJob AS (
    SELECT
        Person,
        [Date],
        Job,
        -- 同员工同岗位日期不连续时，标记为新分组起点
        CASE WHEN LAG(Job) OVER (PARTITION BY Person ORDER BY [Date]) = Job THEN 0 ELSE 1 END AS IsNewGroup
    FROM DailyTopJob
    WHERE rn = 1
),
-- 第四步：对连续的同岗位日期分组
JobGroups AS (
    SELECT
        Person,
        [Date],
        Job,
        SUM(IsNewGroup) OVER (PARTITION BY Person ORDER BY [Date] ROWS UNBOUNDED PRECEDING) AS GroupId
    FROM DailyValidJob
)
-- 第五步：按分组合并日期区间得到最终结果
SELECT
    ROW_NUMBER() OVER (ORDER BY MIN([Date])) AS 记录序号,
    Person AS 员工ID,
    Job AS 岗位ID,
    MIN([Date]) AS 任职开始日期,
    MAX([Date]) AS 任职结束日期
FROM JobGroups
GROUP BY Person, Job, GroupId
ORDER BY 任职开始日期;

-- 清理临时表
DROP TABLE IF EXISTS #ListOfDates;
DROP TABLE IF EXISTS #Test;

方案说明 #

• 先通过日期维度表把所有任职区间拆分为单日维度，按修改时间倒序取每日优先级最高的岗位，解决重叠记录的优先级判断问题
• 新增间断识别逻辑：通过LAG窗口函数判断当前日期的岗位和前一天是否一致，不一致则标记为新分组的起点
• 对同员工同岗位的连续日期分组后合并起止日期，即可自动拆分出同岗位的多段不连续任职区间，完美适配岗位6需要分两段返回的场景
• 兼容SQL Server 2012及以上版本，适配V13.0.4（SQL Server 2016）版本使用

内容的提问来源于stack exchange，提问作者High Plains Grifter