Oracle 12c中查询连续相同分组对应最小生效日期的实现方法

该需求属于典型的序列「岛屿问题」，核心是为连续相同部门的任职时段生成唯一分组标识，再分组取最小生效日期即可实现，员工返回原部门的场景会自动生成独立分组。

实现SQL #

WITH step1 AS (
  SELECT 
    EMP_ID,
    EFF_DT,
    DEPT_ID,
    -- 标记部门变更行：首行或部门与上一行不同时标记为1
    CASE WHEN DEPT_ID != LAG(DEPT_ID,1) OVER(PARTITION BY EMP_ID ORDER BY EFF_DT) 
          OR LAG(DEPT_ID,1) OVER(PARTITION BY EMP_ID ORDER BY EFF_DT) IS NULL 
         THEN 1 
         ELSE 0 
    END AS change_flag
  FROM EMP_DEPT_HIST -- 此处替换为实际业务表名
),
step2 AS (
  SELECT 
    EMP_ID,
    EFF_DT,
    DEPT_ID,
    -- 累加标记生成连续周期分组ID，同一连续任职周期的group_id完全相同
    SUM(change_flag) OVER(PARTITION BY EMP_ID ORDER BY EFF_DT) AS group_id
  FROM step1
)
-- 分组取每个连续周期的最小生效日期
SELECT 
  EMP_ID,
  MIN(EFF_DT) AS EFF_DT,
  DEPT_ID
FROM step2
GROUP BY EMP_ID, group_id, DEPT_ID
ORDER BY EMP_ID, EFF_DT;

逻辑说明 #

• 第一步标记变更行：按EMP_ID分区、EFF_DT升序排序，用LAG函数获取上一行的DEPT_ID，首行或部门发生变更的行标记为1，其余行标记为0。
• 第二步生成分组ID：对标记值做累加求和，同一连续任职周期内的所有行累加值完全相同，即得到唯一的周期分组ID。
• 第三步分组取数：按员工ID、分组ID、部门ID分组，取最小的EFF_DT即为该段任职的起始生效日期。

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