如何在SQL Server日期维度表中获取上一个工作日？

核心思路 #

不再依赖固定偏移量的LAG()，而是通过工作日分组的方式，将连续的非工作日（周末+节假日）归入同一组，再关联组内最近的上一个工作日。这种方法基于纯窗口函数实现，性能远优于游标遍历。

实现步骤与代码 #

1. 标记每个日期是否为工作日（非周末且非节假日）
2. 通过累计工作日数量生成分组ID，连续非工作日会共享同一分组
3. 在每个分组内，提取之前所有行中最后一个工作日的日期

WITH CalendarWithWorkDay AS (
    SELECT 
        TheDate,
        TheDayName,
        IsWeekend,
        IsHoliday,
        -- 标记是否为工作日：非周末且非节假日
        CASE WHEN IsWeekend = 0 AND IsHoliday = 0 THEN 1 ELSE 0 END AS IsWorkDay
    FROM [dbo].[TheCalendar]
),
WorkDayGroups AS (
    SELECT 
        *,
        -- 累计工作日数量，连续非工作日会保持相同的分组ID
        SUM(IsWorkDay) OVER (ORDER BY TheDate) AS WorkDayGroup
    FROM CalendarWithWorkDay
)
SELECT 
    TheDate,
    TheDayName,
    -- 提取当前分组之前最近的工作日日期
    MAX(CASE WHEN IsWorkDay = 1 THEN TheDate END) OVER (
        PARTITION BY WorkDayGroup 
        ORDER BY TheDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS LastWorkDay
FROM WorkDayGroups
ORDER BY TheDate;

效果验证 #

针对你提供的示例数据，执行后会得到正确结果：

性能优势 #

该方案完全基于窗口函数实现，属于集合式运算，SQL Server能高效优化执行计划，即使处理数年的日期数据，性能也远高于游标等逐行遍历的方式。

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