Oracle SQL中按任职顺序隔离结果计算员工岗位编号方案

你当前的写法直接按Role分组，会把所有同部门的任职记录合并，自然无法区分中间有断层的同部门任职段，这类连续序列分组的问题属于典型的序列孤岛问题，可以通过窗口函数标记转岗节点的方式解决。

1. 用LAG()窗口函数获取员工上一条任职记录的部门，对比当前部门，标记是否发生转岗
2. 对转岗标记做累加求和，得到连续任职段的分组ID
3. 基于分组ID生成岗位编号

WITH emp_records AS (
    -- 原始任职记录
    SELECT 'Bob' name, 1 years_at_company, 'Sales' role FROM DUAL
    UNION ALL
    SELECT 'Bob', 2, 'Sales' FROM DUAL
    UNION ALL
    SELECT 'Bob', 3, 'Sales' FROM DUAL
    UNION ALL
    SELECT 'Bob', 4, 'IT' FROM DUAL
    UNION ALL
    SELECT 'Bob', 5, 'Sales' FROM DUAL
    UNION ALL
    SELECT 'Bob', 6, 'Marketing' FROM DUAL
),
mark_transfer AS (
    -- 标记转岗节点：和上一条记录部门不同则记为1，否则为0
    SELECT 
        name,
        years_at_company,
        role,
        CASE WHEN role = LAG(role) OVER(PARTITION BY name ORDER BY years_at_company) 
             THEN 0 ELSE 1 END is_transfer
    FROM emp_records
),
role_group AS (
    -- 累加转岗标记得到连续任职段的分组ID
    SELECT 
        name,
        years_at_company,
        role,
        SUM(is_transfer) OVER(PARTITION BY name ORDER BY years_at_company) group_id
    FROM mark_transfer
)
-- 生成最终岗位编号，每个分组对应唯一岗位
SELECT 
    DENSE_RANK() OVER(PARTITION BY name ORDER BY group_id) JOB_NO,
    name,
    role,
    MIN(years_at_company) start_year,
    MAX(years_at_company) end_year
FROM role_group
GROUP BY name, role, group_id
ORDER BY JOB_NO;

完全符合你给出的任职流程示例的编号规则。

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