基于相邻行delta值生成员工雇佣记录序列编号的SQL实现问题

实现思路 #

你遇到的是典型的连续区间分组场景，使用「标记位+累加求和」的窗口函数组合即可稳定实现，逻辑简单且全量数据运行性能和准确率都有保障：

1. 先给每条记录打新序列标记：每个员工的第一条记录、或当前记录与上一条记录间隔超过1天，标记为1，其余情况标记为0
2. 对标记按员工分组、按雇佣记录的时间顺序做累加求和，得到的结果就是所需的序列编号

完整实现代码 #

WITH contract_mark AS (
    SELECT 
        *,
        CASE 
            WHEN Delta IS NULL THEN 1
            WHEN Delta > 1 THEN 1
            ELSE 0 
        END AS new_seq_flag
    FROM Contracts
)
SELECT 
    Employee,
    Contract,
    Unit,
    Start,
    End,
    Delta,
    SUM(new_seq_flag) OVER (
        PARTITION BY Employee 
        ORDER BY Contract ASC
    ) AS Sequence
FROM contract_mark
ORDER BY Employee, Contract ASC;

效果验证 #

代入你提供的示例数据运算：

• 第一条记录Delta为NULL，标记为1，累加得到Sequence=1
• 第二条记录Delta=31>1，标记为1，累加得到Sequence=2
• 第三条、第四条记录Delta均为1，标记为0，累加后Sequence保持为2
  运算结果完全匹配预期输出。

扩展：计算连续序列的起止时间 #

如果需要进一步统计每个连续雇佣序列的最小开始时间和最大结束时间，在上述结果基础上按员工和序列分组聚合即可：

WITH contract_mark AS (
    SELECT 
        *,
        CASE 
            WHEN Delta IS NULL THEN 1
            WHEN Delta > 1 THEN 1
            ELSE 0 
        END AS new_seq_flag
    FROM Contracts
),
contract_seq AS (
    SELECT 
        *,
        SUM(new_seq_flag) OVER (
            PARTITION BY Employee 
            ORDER BY Contract ASC
        ) AS Sequence
    FROM contract_mark
)
SELECT 
    Employee,
    Sequence,
    MIN(Start) AS Group_Start,
    MAX(End) AS Group_End
FROM contract_seq
GROUP BY Employee, Sequence
ORDER BY Employee, Sequence;

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