PostgreSQL使用CTE按休假优先级更新考勤表休假字段

该方案完全基于CTE、窗口函数等PostgreSQL原生语法实现，无需创建存储过程或自定义函数，采用集合运算替代逐行处理，可高效支撑百万级数据批量更新。

涉及业务表字段说明 #

• 员工休假余额表 employee_leave_balance
  • emp_code：员工唯一编码
  • leave_type：休假类型
  • yearmonth：所属统计月度，格式为YYYYMM
  • balance：对应休假类型当月剩余可抵扣额度
  • priority：休假抵扣优先级，数值越小优先级越高
• 考勤表 attendance
  • emp_code：员工唯一编码
  • date：考勤日期
  • yearmonth：所属统计月度
  • attendance：考勤状态标识，值为0代表当日缺勤
  • leave：待填充的休假类型字段

核心实现逻辑 #

1. 筛选所有attendance=0的缺勤记录，按员工、所属月度分组，将缺勤记录按日期从早到晚编排序号
2. 将员工当月所有休假额度按优先级从高到低排序，计算每类休假可覆盖的缺勤序号区间
3. 通过区间关联匹配每个缺勤日对应的休假类型，超出总额度的缺勤日不匹配任何类型，字段留空
4. 基于关联结果批量回写考勤表的leave字段，全程无逐行循环操作

可直接执行的SQL代码 #

WITH absent_rank AS (
    -- 为缺勤记录按时间先后排序编号
    SELECT
        emp_code,
        date,
        yearmonth,
        ROW_NUMBER() OVER (
            PARTITION BY emp_code, yearmonth
            ORDER BY date ASC
        ) AS absent_seq
    FROM attendance
    WHERE attendance = 0
),
leave_range AS (
    -- 计算各优先级休假对应的抵扣序号区间
    SELECT
        emp_code,
        yearmonth,
        leave_type,
        COALESCE(
            SUM(balance) OVER (
                PARTITION BY emp_code, yearmonth
                ORDER BY priority ASC
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ),
            0
        ) AS range_start_base,
        SUM(balance) OVER (
            PARTITION BY emp_code, yearmonth
            ORDER BY priority ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS range_end
    FROM employee_leave_balance
),
matched_result AS (
    -- 关联匹配每个缺勤日对应的休假类型
    SELECT
        ar.emp_code,
        ar.date,
        lr.leave_type
    FROM absent_rank ar
    LEFT JOIN leave_range lr
        ON ar.emp_code = lr.emp_code
        AND ar.yearmonth = lr.yearmonth
        AND ar.absent_seq > lr.range_start_base
        AND ar.absent_seq <= lr.range_end
)
-- 批量更新考勤表字段
UPDATE attendance a
SET leave = mr.leave_type
FROM matched_result mr
WHERE a.emp_code = mr.emp_code
AND a.date = mr.date;

效果验证与性能提示 #

• 规则匹配符合需求：以员工当月有2天PL（优先级0）、1天SL（优先级1），共4天缺勤为例，更新后前2个最早缺勤日标记为PL，第3个缺勤日标记为SL，第4个缺勤日leave字段为空
• 性能优化：执行更新前建议创建两个索引，可将百万级数据处理耗时压缩到秒级：
  • CREATE INDEX idx_att_emp_ym_date ON attendance(emp_code, yearmonth, date);
  • CREATE INDEX idx_bal_emp_ym_pri ON employee_leave_balance(emp_code, yearmonth, priority);
• 安全校验：正式执行更新前，可将最后UPDATE语句替换为SELECT * FROM matched_result，预览匹配结果确认无误后再执行更新操作
• 异常兼容：余额为0的休假记录会自动在区间计算中被过滤，无需额外添加判断逻辑

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