SQL如何提取员工任职表job或location变更时的分组最低行

核心逻辑 #

这是典型的连续序列孤岛（Gaps and Islands） 问题，只要Job或Location任意一个字段和上一条记录不一致，就判定为新的任职分组，最终取每个分组内StDt最小的首条记录即可。

通用SQL实现（兼容所有支持标准窗口函数的引擎，如MySQL8+、PostgreSQL、Oracle、Spark SQL等） #

假设表名为emp_job_history，完整实现代码如下：

WITH lag_data AS (
    -- 步骤1：获取上一条记录的Job和Location
    SELECT 
        *,
        LAG(Job) OVER (ORDER BY StDt) AS prev_job,
        LAG(Location) OVER (ORDER BY StDt) AS prev_loc
    FROM emp_job_history
),
change_flag AS (
    -- 步骤2：标记发生变更的行
    SELECT
        *,
        CASE 
            WHEN prev_job IS NULL OR Job != prev_job OR Location != prev_loc THEN 1
            ELSE 0
        END AS is_change
    FROM lag_data
),
group_id AS (
    -- 步骤3：对变更标记累加，生成连续相同属性的分组ID
    SELECT
        *,
        SUM(is_change) OVER (ORDER BY StDt) AS group_no
    FROM change_flag
),
rank_in_group AS (
    -- 步骤4：每个分组内按生效时间升序排序，取首条
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY group_no ORDER BY StDt ASC) AS rn
    FROM group_id
)
SELECT StDt, EdDt, Job, Location
FROM rank_in_group
WHERE rn = 1
ORDER BY StDt;

结果说明 #

上述代码执行后输出的结果和示例中Required_Rows标记为Y的行完全匹配，能正确覆盖所有Job、Location变更的首个记录。如果存在StDt完全相同的重复记录，可以在窗口函数的ORDER BY后补充主键等唯一字段，避免排序错乱。

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