SQL按连续合同变更分组计算员工各合同段起止工作日期

问题背景 #

• 处理18万行电子表格数据时遇到性能瓶颈，已规划数据库替代方案，在数据规范化清洗阶段遇到SQL分组逻辑问题
• 目标表RD1存储员工每日合同记录，字段为WorkDate（工作日期，连续无缺失）、EmpNo（员工编号）、Contract（合同类型）
• 同一员工合同类型可多次变更，甚至重复出现历史合同类型，例：员工123的合同序列为16→40→16
• 需求为拆分每次合同变更的连续区间，输出EmpNo、Contract、StartDate（区间起始日期）、EndDate（区间结束日期）

错误实现问题 #

直接按员工、合同类型分组取最大最小日期的写法会合并所有同类型合同区间，无法区分变更后重复出现的同类型合同，以上述员工123为例仅能返回2条结果，不符合需求，错误SQL如下：

SELECT          EmpNo, Contract, 
                MIN(WorkDate) AS StartDate, 
                MAX(WorkDate) AS EndDate
FROM            RD1
GROUP BY        EmpNo, Contract  

已尝试方案的问题 #

基于日期连续的特性，曾尝试两次自连接RD1标记连续段起止标识、再关联匹配起止日期的方案，可基本得到预期的分段结果，但单日合同段的结束日期存在计算错误。

该问题属于典型的**连续区间孤岛（Gaps and Islands）**问题，核心是给每一段连续的同类型合同生成唯一分组标识，再按标识聚合即可，不会出现同类型跨区间合并、单日段计算错误的问题。

支持窗口函数的数据库写法（MySQL8.0+、PostgreSQL、SQL Server、SQLite等） #

WITH contract_seg AS (
    SELECT
        EmpNo,
        Contract,
        WorkDate,
        -- 按员工分区、日期排序，合同类型与上一条记录不一致时，分组号累加
        SUM(
            CASE WHEN Contract = LAG(Contract) OVER (PARTITION BY EmpNo ORDER BY WorkDate)
            THEN 0 ELSE 1 END
        ) OVER (PARTITION BY EmpNo ORDER BY WorkDate) AS seg_id
    FROM RD1
)
SELECT
    EmpNo,
    Contract,
    MIN(WorkDate) AS StartDate,
    MAX(WorkDate) AS EndDate
FROM contract_seg
GROUP BY EmpNo, Contract, seg_id
ORDER BY EmpNo, StartDate;

逻辑说明：

1. 用LAG()窗口函数取同员工上一工作日的合同类型，与当前行比对：一致则属于同一连续段，记0；不一致则进入新段，记1
2. 对标记值做窗口累计求和，每一段连续的同类型合同会得到唯一的seg_id，即使合同类型重复出现，只要中间存在变更，seg_id就会不同，不会被合并
3. 最后按员工、合同类型、分段ID聚合，取段内最小、最大日期作为起止日期，单日长度的段会自动返回相同的起止日期，无计算错误

低版本MySQL（5.x）不支持窗口函数的写法 #

通过用户变量模拟窗口函数生成分组标识：

SELECT
    EmpNo,
    Contract,
    MIN(WorkDate) AS StartDate,
    MAX(WorkDate) AS EndDate
FROM (
    SELECT
        WorkDate,
        EmpNo,
        Contract,
        @seg_id := IF(
            @last_emp = EmpNo AND @last_contract = Contract,
            @seg_id,
            @seg_id + 1
        ) AS seg_id,
        @last_emp := EmpNo,
        @last_contract := Contract
    FROM RD1, (SELECT @seg_id := 0, @last_emp := NULL, @last_contract := NULL) AS var_init
    ORDER BY EmpNo, WorkDate
) AS t
GROUP BY EmpNo, Contract, seg_id
ORDER BY EmpNo, StartDate;

内容的提问来源于stack exchange，提问作者Darren Bartrup-Cook