编写SQL查询提取SUPERVISOR_ID变更记录（取最小EFFDT）

编写SQL查询语句，仅展示SUPERVISOR_ID发生变更的记录，且仅保留每组变更对应的最小EFFDT值。

方案一：直接识别变更记录 #

利用LAG()窗口函数对比当前与上一条记录的上级ID，筛选变更点：

WITH ranked_data AS (
    SELECT 
        EMPLID,
        '',
        EFFDT,
        SUPERVISOR_ID,
        LAG(SUPERVISOR_ID) OVER (PARTITION BY EMPLID ORDER BY EFFDT) AS prev_supervisor
    FROM your_table_name
)
SELECT 
    EMPLID,
    '',
    EFFDT,
    SUPERVISOR_ID
FROM ranked_data
WHERE prev_supervisor IS NULL OR SUPERVISOR_ID != prev_supervisor
ORDER BY EFFDT;

方案二：分组取最早生效日期 #

先对连续相同的上级ID分组，再提取每组最小EFFDT：

WITH grouped_data AS (
    SELECT 
        EMPLID,
        '',
        EFFDT,
        SUPERVISOR_ID,
        SUM(CASE WHEN SUPERVISOR_ID != LAG(SUPERVISOR_ID) OVER (PARTITION BY EMPLID ORDER BY EFFDT) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY EMPLID ORDER BY EFFDT) AS group_tag
    FROM your_table_name
),
min_effdt_groups AS (
    SELECT 
        EMPLID,
        '',
        MIN(EFFDT) AS EFFDT,
        SUPERVISOR_ID
    FROM grouped_data
    GROUP BY EMPLID, group_tag, SUPERVISOR_ID
)
SELECT * FROM min_effdt_groups ORDER BY EFFDT;

两种方案均可得到预期结果，方案一适合快速定位变更节点，方案二更适合处理连续相同上级的批量记录。

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