如何提取员工岗位切换后连续组的最早起始日期（年度内）

现有一张存储单个员工记录的表（表中包含示例未展示的其他列），同一岗位对应多条连续记录（比如J1有2条、J2有3条、之后J1又出现2条），员工岗位经历了J1→J2→J1的切换。需要在1-12月范围内从12月倒查，提取每次岗位切换后对应连续岗位组的最小StDt：J1取1 Sep 2021（此前J1出现中断），J2取1 Jul 2021。

示例数据 #

StDt             EdDt           Job      
1-Jan-21      31-Jan-21        J1          
1-Feb-21      30-Jun-21        J1          
1-Jul-21      30-Jul-21        J2          
1-Aug-21      15-Aug-21        J2          
16-Aug-21     31-Aug-21        J2          
1-Sep-21      1-Nov-21         J1          
2-Nov-21      31-Dec-21        J1

解决方案（SQL） #

这是典型的**连续相同分组（孤岛）**问题，用窗口函数标记连续岗位组后即可提取每组最早日期：

WITH job_groups AS (
    SELECT 
        StDt,
        EdDt,
        Job,
        -- 岗位变化时生成新分组ID
        SUM(CASE WHEN Job = LAG(Job) OVER (ORDER BY StDt) THEN 0 ELSE 1 END) 
            OVER (ORDER BY StDt) AS group_id
    FROM employee_job_history
    -- 限定2021年1-12月范围
    WHERE StDt >= DATE '2021-01-01' 
      AND StDt <= DATE '2021-12-31'
),
group_summary AS (
    SELECT 
        Job,
        MIN(StDt) AS earliest_stdt,
        group_id
    FROM job_groups
    GROUP BY Job, group_id
)
-- 按分组ID倒序，实现从12月倒查的视角
SELECT 
    Job,
    earliest_stdt
FROM group_summary
ORDER BY group_id DESC;

逻辑说明 #

1. job_groups CTE：通过LAG()函数对比当前行与上一行的岗位，岗位变化时标记新分组，生成唯一的连续岗位组ID。
2. group_summary CTE：按岗位和分组ID聚合，提取每个连续岗位组的最早生效日期StDt。
3. 最终按分组ID倒序输出，得到从12月倒查时，每次岗位切换后的连续组最早日期。

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