如何提取属性变更行：含岗位、地点及地点主表Status变更的SQL实现需求

实现思路 #

核心是处理慢变化维度（SCD2）表的区间合并+多维度变更识别，步骤如下：

• 第一步：将员工任职表和地点主表做区间交集关联，拆分出所有因地点Status变更产生的新时间区间
• 第二步：识别Job、Location、Status三个字段完全一致的连续区间（即经典的岛屿问题场景）
• 第三步：每个连续区间内取EdDt最大的行标记为Y，其余标记为N，即可匹配需求输出

表定义约定 #

• 员工任职记录表：emp_job_hist
• 地点主表：loc_mst

单条SQL实现（兼容Oracle/Spark SQL/Hive/PostgreSQL等支持窗口函数的主流数据库） #

WITH split_intervals AS (
    -- 关联两张SCD2表，拆分得到重叠后的细粒度时间区间
    SELECT
        GREATEST(e.StDt, l.StDt) AS StDt,
        LEAST(e.EdDt, l.EdDt) AS EdDt,
        e.Job,
        e.Location,
        l.Status
    FROM emp_job_hist e
    JOIN loc_mst l 
        ON e.Location = l.Location
        AND e.StDt <= l.EdDt
        AND e.EdDt >= l.StDt
),
group_flags AS (
    -- 判断当前行和上一行的三个维度是否有变化，生成连续分组ID
    SELECT
        StDt,
        EdDt,
        Job,
        Location,
        Status,
        SUM(CASE WHEN Job = LAG(Job,1,'') OVER(ORDER BY StDt) 
                  AND Location = LAG(Location,1,'') OVER(ORDER BY StDt)
                  AND Status = LAG(Status,1,'') OVER(ORDER BY StDt)
            THEN 0 ELSE 1 END) OVER(ORDER BY StDt) AS group_id
    FROM split_intervals
),
rank_in_group AS (
    -- 每个分组内按结束时间倒序排序，标记最大行
    SELECT
        StDt,
        EdDt,
        Job,
        Location,
        Status,
        CASE WHEN ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY EdDt DESC) = 1 THEN 'Y' ELSE 'N' END AS Required_Rows
    FROM group_flags
)
SELECT * FROM rank_in_group ORDER BY StDt;

关键逻辑说明 #

• 区间拆分用GREATEST和LEAST取两个重叠区间的交集部分，是SCD2表关联的标准写法
• 用SUM() OVER()计算分组ID是解决连续区间问题的通用方案，可以准确识别三个维度任意一个发生变更的临界点
• 每个分组内取EdDt最大的行，完全匹配需求中「提取每次切换时对应区间的最大行记录」的要求

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