Oracle数据库如何按条件获取任职记录最小生效日期

问题本质 #

这类需求属于典型的SQL间隙与岛屿（Gaps and Islands）场景：员工任职历史表按生效日期存储快照，同一员工多次进出同一职位时，会产生多段被其他职位记录隔开的同职位数据，直接按员工ID+职位ID分区取最小/最大日期，会把所有同职位记录合并成一段，无法区分不同任职周期。

核心补充逻辑 #

不能仅用员工、职位作为分组维度，需要给每一段连续的同职位任职记录生成唯一的分段标识，将这个标识加入分组条件，即可实现分段统计。

实现步骤 #

• 标记段起点：按员工分组、按生效日期排序，用LAG()函数取上一条记录的职位ID，和当前行职位ID不一致时，说明当前行是新任职段的起点，标记为1，否则标记为0。
• 生成分段ID：对段起点标记值做累计求和，同一段连续任职的累计值固定不变，中间插入其他职位记录后，再次回到原职位时累计值会递增，自然区分开不同的任职周期。
• 聚合计算起止日期：按员工ID、职位ID、分段ID分组，取组内最小生效日期为任职开始日期；如果表仅存储生效日期，可取下一段任职的开始日期减1作为当前段的结束日期，最后一段无后续记录时结束日期可置为NULL表示当前在职。

参考实现代码 #

假设任职历史表名为emp_job_history，核心字段为：emp_id（员工ID）、job_id（职位ID）、eff_dt（记录生效日期）

WITH mark_segment AS (
    SELECT
        emp_id,
        job_id,
        eff_dt,
        -- 标记是否为新任职段起点
        CASE
            WHEN LAG(job_id) OVER (PARTITION BY emp_id ORDER BY eff_dt) = job_id THEN 0
            ELSE 1
        END AS new_segment_flag
    FROM emp_job_history
),
gen_segment_id AS (
    SELECT
        emp_id,
        job_id,
        eff_dt,
        -- 累计求和生成唯一分段ID
        SUM(new_segment_flag) OVER (PARTITION BY emp_id ORDER BY eff_dt) AS seg_id
    FROM mark_segment
)
SELECT
    emp_id,
    job_id,
    MIN(eff_dt) AS position_start_date,
    LEAD(MIN(eff_dt)) OVER (PARTITION BY emp_id ORDER BY MIN(eff_dt)) - 1 AS position_end_date
FROM gen_segment_id
GROUP BY emp_id, job_id, seg_id
ORDER BY emp_id, position_start_date;

注意：如果原表本身存储了记录失效日期eff_end_dt，最后一步聚合时直接取MAX(eff_end_dt)作为任职结束日期即可，不需要用LEAD函数推算。

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