如何在Oracle SQL中查询员工主管身份的起止日期

原始数据表 #

期望输出表 #

实现SQL #

WITH unique_records AS (
    -- 去重，保留员工唯一的职位状态变动记录
    SELECT DISTINCT
        ID,
        Employee,
        Position_Title,
        Effective_Date,
        Supervisor_Status
    FROM employee_positions
),
sorted_records AS (
    -- 按员工ID分组，日期倒序排序，同时获取上一条记录的状态
    SELECT
        *,
        LAG(Supervisor_Status) OVER (PARTITION BY ID ORDER BY Effective_Date DESC) AS prev_status
    FROM unique_records
),
status_groups AS (
    -- 根据状态变化划分连续的状态区间
    SELECT
        *,
        SUM(CASE WHEN Supervisor_Status != COALESCE(prev_status, Supervisor_Status) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ID ORDER BY Effective_Date DESC) AS group_id
    FROM sorted_records
)
-- 计算每个主管任期的起止日期
SELECT
    sg.ID,
    sg.Employee,
    sg.Position_Title,
    MIN(sg.Effective_Date) AS Supervisor_Start,
    -- 如果下一个状态是非主管，取其最早日期减1天作为结束日期；否则为空
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM status_groups sg_next
            WHERE sg_next.ID = sg.ID
              AND sg_next.group_id = sg.group_id + 1
              AND sg_next.Supervisor_Status = 'Non-Supervisor'
        ) THEN DATE_SUB((SELECT MIN(sg_next.Effective_Date) 
                         FROM status_groups sg_next 
                         WHERE sg_next.ID = sg.ID 
                           AND sg_next.group_id = sg.group_id + 1), INTERVAL 1 DAY)
        ELSE NULL
    END AS Supervisor_End
FROM status_groups sg
WHERE sg.Supervisor_Status = 'Supervisor'
GROUP BY sg.ID, sg.Employee, sg.Position_Title, sg.group_id
ORDER BY sg.ID, Supervisor_Start DESC;

逻辑说明 #

1. 去重处理：原始数据存在大量重复记录，先通过DISTINCT筛选出每个员工唯一的职位状态变动记录，避免干扰后续分组。
2. 状态区间分组：利用窗口函数LAG()获取上一条记录的状态，通过状态变化标记连续的同一状态区间（group_id），把同一员工连续的主管/非主管状态归为一组。
3. 计算任期起止：
   • 主管任期的开始日期为该状态组内最早的生效日期；
   • 如果该主管状态之后紧接着是非主管状态，取非主管状态的最早生效日期减1天作为任期结束日期；
   • 如果没有后续状态变更，说明员工当前仍处于主管状态，结束日期留空。

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