解决GROUP BY合并非连续重复SUPERVISOR_ID的最优方案

我遇到的问题较难描述，以下通过数据说明：

原始数据 #

我尝试使用以下SQL查询来获取所需结果：

SELECT
    EMPLOYEE_ID,
    SUPERVISOR_ID,
    MIN(EFFECTIVE_START) AS "EFFECTIVE_START",
    MAX(EFFECTIVE_END) AS "EFFECTIVE_END"
FROM
    EMP_TABLE
GROUP BY
    1, 2
ORDER BY
    EFFECTIVE_START ASC

当前错误结果 #

问题在于SUPERVISOR_ID为73280的记录被错误合并，出现重复分组结果：

期望结果 #

按时间连续的同一主管进行分组，正确结果如下：

解决方案 #

这属于典型的连续相同分组（岛屿问题），需要用窗口函数识别主管变化的节点，再对连续的相同主管分组处理：

WITH ranked_data AS (
    SELECT
        EMPLOYEE_ID,
        EMPLOYEE_SUPERVISOR_ID AS SUPERVISOR_ID,
        EFFECTIVE_START_DATE AS EFFECTIVE_START,
        EFFECTIVE_END_DATE AS EFFECTIVE_END,
        -- 标记主管变化的行，当当前主管与上一行不同时，生成新分组ID
        SUM(CASE WHEN LAG(EMPLOYEE_SUPERVISOR_ID) OVER (PARTITION BY EMPLOYEE_ID ORDER BY EFFECTIVE_START_DATE) = EMPLOYEE_SUPERVISOR_ID THEN 0 ELSE 1 END) 
        OVER (PARTITION BY EMPLOYEE_ID ORDER BY EFFECTIVE_START_DATE) AS group_id
    FROM EMP_TABLE
)
SELECT
    EMPLOYEE_ID,
    SUPERVISOR_ID,
    MIN(EFFECTIVE_START) AS EFFECTIVE_START,
    MAX(EFFECTIVE_END) AS EFFECTIVE_END
FROM ranked_data
GROUP BY EMPLOYEE_ID, SUPERVISOR_ID, group_id
ORDER BY EFFECTIVE_START;

逻辑说明：

1. 用LAG函数获取当前行的上一行主管ID，判断是否与当前主管相同；
2. 用SUM累加变化标记，生成唯一的分组ID，同一连续主管的记录会被分到同一个group_id；
3. 最后按EMPLOYEE_ID、SUPERVISOR_ID和group_id分组，取每组的最小开始时间和最大结束时间。

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