SQL Server如何为月度员工缺勤表添加近12个月连续病假判定列

核心思路 #

先按员工+年月维度聚合数据解决单员工单月多记录的问题，再用窗口函数计算滑动12个月的累计值对比，最后关联回原表给所有行打标记，40余万行数据量下无性能压力，支持全表历史数据计算。

具体实现代码 #

WITH MonthlyPersonAgg AS (
    -- 第一步：按员工+年月聚合，计算单月总缺勤、总应出勤，解决单月多条记录问题
    SELECT 
        PersonID,
        YEAR,
        MONTH,
        SUM(Absence1 + Absence2 + Absence3) AS TotalAbsence,
        SUM([WORKING DAYS OF THE MONTH]) AS TotalWorkingDays,
        DATEFROMPARTS(YEAR, MONTH, 1) AS MonthDate
    FROM 你的表名 -- 替换为实际表名
    GROUP BY PersonID, YEAR, MONTH
),
YearlyCalculate AS (
    -- 第二步：计算每个月对应的过去12个月累计值，打标记
    SELECT 
        PersonID,
        YEAR,
        MONTH,
        CASE 
            WHEN SUM(TotalAbsence) OVER (
                PARTITION BY PersonID 
                ORDER BY MonthDate
                RANGE BETWEEN INTERVAL '12' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING
            ) = SUM(TotalWorkingDays) OVER (
                PARTITION BY PersonID 
                ORDER BY MonthDate
                RANGE BETWEEN INTERVAL '12' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING
            ) THEN '已病假满1年及以上'
            ELSE ''
        END AS SickLeaveOver1YearFlag
    FROM MonthlyPersonAgg
)
-- 第三步：关联回原表，给所有行（含同员工同月不同部门/职业的行）统一打标记
SELECT 
    t.*,
    y.SickLeaveOver1YearFlag
FROM 你的表名 t
INNER JOIN YearlyCalculate y 
    ON t.PersonID = y.PersonID 
    AND t.YEAR = y.YEAR 
    AND t.MONTH = y.MONTH

兼容及优化说明 #

• 如果你使用的是SQL Server 2022之前的版本，不支持RANGE语法的INTERVAL参数，可以先给每个员工的月份按时间排序生成序号，改用ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING实现滑动窗口
• 如果存在员工某月份无任何记录的情况，建议先生成所有年月的维度表，左关联补全该员工当月缺勤为0、应出勤为对应月度总天数的记录，避免计算窗口缺数
• 仪表板使用时可以把计算结果保存为定时更新的物理表，不用每次查询实时计算，进一步提升加载速度

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