如何按POSITION列的变动分组并整理日期等相关列数据

你的初始查询group by name, position会将所有相同职位的记录合并，包括非连续的（比如示例中前后两段Technician），这不符合需求。我们需要识别连续的相同职位组（即"岛屿"），再对每个组聚合日期，这属于经典的"间隙与岛屿"问题。

实现步骤 #

1. 为每个员工的记录按入职日期排序，生成全局行号；
2. 为每个员工的同一职位记录按入职日期排序，生成职位内的行号；
3. 两个行号的差值会为连续的相同职位记录生成唯一分组标识；
4. 按员工姓名、职位、分组标识聚合，取每组的最小入职日期和最大离职日期。

完整SQL代码 #

WITH ranked_records AS (
    SELECT
        name,
        position,
        start_date,
        end_date,
        -- 按员工+入职日期排序的全局行号
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY start_date) AS global_rn,
        -- 按员工+职位+入职日期排序的职位内行号
        ROW_NUMBER() OVER (PARTITION BY name, position ORDER BY start_date) AS position_rn
    FROM employee_table
)
SELECT
    name,
    position,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM ranked_records
-- 用行号差值作为连续职位组的标识
GROUP BY name, position, (global_rn - position_rn)
ORDER BY MIN(start_date);

逻辑说明 #

• 当职位连续不变时，global_rn和position_rn同步递增，差值保持不变，属于同一组；
• 当职位发生变化时，position_rn重置为1，差值产生变化，形成新的分组；
• 最终通过分组聚合得到连续相同职位的合并结果，与你期望的输出一致。

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