PostgreSQL如何实现将上一行结束日期作为下一行起始日期

前提假设 #

我们假设存储员工历史数据的表名为 emp_records，核心字段如下：

• emp_id：员工唯一ID，用于区分不同员工
• hire_date：员工入职日期
• effectivity_date：本次记录的生效日期
• 其余员工属性字段（如姓名、岗位等）可根据实际需求保留

实现SQL #

SELECT
    emp_id,
    -- 可按需添加其他需要保留的员工字段
    hire_date,
    effectivity_date,
    -- 计算生效区间起始日期
    CASE
        WHEN LAG(effectivity_date) OVER w IS NULL THEN hire_date
        ELSE LAG(effectivity_date) OVER w
    END AS start_date,
    -- 计算生效区间结束日期
    CASE
        WHEN LEAD(effectivity_date) OVER w IS NULL THEN '2030-12-31'::DATE
        ELSE LEAD(effectivity_date) OVER w - 1
    END AS end_date
FROM emp_records
-- 窗口定义复用，避免重复写分区排序逻辑
WINDOW w AS (PARTITION BY emp_id ORDER BY effectivity_date)
ORDER BY emp_id, effectivity_date;

逻辑说明 #

• 用PARTITION BY emp_id按员工拆分数据，每个员工的记录单独计算日期区间
• 用ORDER BY effectivity_date保证同一员工的记录按生效时间先后排序，确保窗口函数取到正确的上下行记录
• LAG窗口函数取同一员工上一条记录的生效日期：首条记录无上行数据，返回NULL，此时直接取入职日期作为起始日期；其余记录直接取上一行的生效日期作为当前行起始日期
• LEAD窗口函数取同一员工下一条记录的生效日期：最后一条记录无下行数据，返回NULL，此时直接赋值2030-12-31作为结束日期；其余记录取下一行生效日期减1天，避免出现日期重叠
• 这里用WINDOW关键字复用窗口定义，简化代码，也可以直接把窗口逻辑写在每个LAG/LEAD函数后面

如果需要持久化计算结果，直接在SELECT语句前加CREATE TABLE 目标表名 AS即可：

CREATE TABLE emp_effective_period AS
-- 上述查询SQL

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