如何从同一张员工雇佣记录表中获取有效任职日期区间？

核心思路 #

无需拆分表，直接利用窗口函数关联每个员工的雇佣/离职记录，一次性计算出每次任职周期的时长，再过滤掉不足一年的记录。这种方法适合处理多次雇佣-离职循环（10-20次完全适配），性能比自连接或拆分表更高效。

数据前提 #

假设记录序列符合逻辑：rehired（或初始雇佣）在前，termed在后；若存在初始离职记录（无前置雇佣记录），这类无完整周期的记录可直接跳过。

具体实现（以PostgreSQL为例） #

WITH employee_hire_cycles AS (
    SELECT
        Employee,
        Status,
        Date,
        -- 获取当前记录的下一条记录的日期和状态
        LEAD(Date) OVER (PARTITION BY Employee ORDER BY Date) AS next_record_date,
        LEAD(Status) OVER (PARTITION BY Employee ORDER BY Date) AS next_record_status
    FROM
        employee_hire_records
)
SELECT
    Employee,
    Date AS hire_start_date,
    next_record_date AS hire_end_date,
    -- 计算任职时长（格式为年-月-日）
    AGE(next_record_date, Date) AS tenure_duration
FROM
    employee_hire_cycles
WHERE
    -- 只取雇佣起始记录，且对应有离职记录
    Status = 'rehired' 
    AND next_record_status = 'termed'
    -- 过滤任职时长满1年的记录
    AND EXTRACT(YEAR FROM AGE(next_record_date, Date)) >= 1
ORDER BY
    Employee, Date;

适配其他数据库的调整 #

• MySQL 8.0+：用TIMESTAMPDIFF(YEAR, Date, next_record_date) >= 1替代时长判断逻辑，窗口函数用法一致。
• SQL Server：用DATEDIFF(YEAR, Date, next_record_date) >= 1，窗口函数同样支持LEAD。

处理特殊情况（初始记录为termed） #

如果存在无前置雇佣记录的离职记录，可通过LAG函数补充前置信息，过滤掉无完整周期的记录：

WITH employee_hire_cycles AS (
    SELECT
        Employee,
        Status,
        Date,
        LAG(Date) OVER (PARTITION BY Employee ORDER BY Date) AS prev_record_date,
        LEAD(Date) OVER (PARTITION BY Employee ORDER BY Date) AS next_record_date,
        LAG(Status) OVER (PARTITION BY Employee ORDER BY Date) AS prev_record_status,
        LEAD(Status) OVER (PARTITION BY Employee ORDER BY Date) AS next_record_status
    FROM
        employee_hire_records
)
SELECT
    Employee,
    CASE
        WHEN Status = 'rehired' THEN Date
        WHEN Status = 'termed' AND prev_record_status = 'rehired' THEN prev_record_date
    END AS hire_start_date,
    CASE
        WHEN Status = 'rehired' AND next_record_status = 'termed' THEN next_record_date
        WHEN Status = 'termed' THEN Date
    END AS hire_end_date,
    TIMESTAMPDIFF(YEAR, 
        CASE
            WHEN Status = 'rehired' THEN Date
            WHEN Status = 'termed' AND prev_record_status = 'rehired' THEN prev_record_date
        END,
        CASE
            WHEN Status = 'rehired' AND next_record_status = 'termed' THEN next_record_date
            WHEN Status = 'termed' THEN Date
        END
    ) AS tenure_years
FROM
    employee_hire_cycles
WHERE
    -- 只保留完整的雇佣-离职周期
    ((Status = 'rehired' AND next_record_status = 'termed') 
     OR (Status = 'termed' AND prev_record_status = 'rehired'))
    AND TIMESTAMPDIFF(YEAR, 
        CASE
            WHEN Status = 'rehired' THEN Date
            WHEN Status = 'termed' AND prev_record_status = 'rehired' THEN prev_record_date
        END,
        CASE
            WHEN Status = 'rehired' AND next_record_status = 'termed' THEN next_record_date
            WHEN Status = 'termed' THEN Date
        END
    ) >= 1
GROUP BY
    Employee, hire_start_date, hire_end_date, tenure_years
ORDER BY
    Employee, hire_start_date;

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