SQL实现各自然月工作日序号计算 周末序号匹配前一个周五

实现逻辑说明 #

要实现按月生成工作日序号、周末沿用前一个周五序号的需求，可以按以下逻辑开发：

1. 先标记每个日期是否为工作日（周一至周五为工作日，周六、周日为非工作日）
2. 给当月所有工作日生成连续的递增序号
3. 非工作日直接取最近一个前置工作日的序号即可

完整SQL代码 #

WITH temp_calendar AS (
    SELECT 
        Date,
        -- 标记是否为工作日：1=工作日，0=周末，适配默认DATEFIRST=7的配置（周日返回1，周六返回7）
        CASE WHEN DATEPART(DW, Date) IN (1,7) THEN 0 ELSE 1 END AS is_workday,
        DATENAME(DW, Date) AS week_day_name,
        -- 提取月份作为分组依据，确保每个月序号独立计算
        DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS month_start
    FROM [STAGING_4_6_DIM_CALENDAR_V2]
),
work_day_seq AS (
    SELECT 
        *,
        -- 仅给工作日生成序号，非工作日先留空，按月分组排序
        CASE WHEN is_workday = 1 THEN SUM(is_workday) OVER (PARTITION BY month_start ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END AS work_day_num
    FROM temp_calendar
)
SELECT 
    Date,
    week_day_name,
    -- 非工作日取最近一个前置工作日的序号
    LAST_VALUE(work_day_num) IGNORE NULLS OVER (PARTITION BY month_start ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS work_day_serial
FROM work_day_seq
ORDER BY Date ASC

低版本适配方案 #

如果你的数据库版本不支持LAST_VALUE的IGNORE NULLS参数，可以改用以下方式实现序号填充：

WITH temp_calendar AS (
    SELECT 
        Date,
        CASE WHEN DATEPART(DW, Date) IN (1,7) THEN 0 ELSE 1 END AS is_workday,
        DATENAME(DW, Date) AS week_day_name,
        DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS month_start
    FROM [STAGING_4_6_DIM_CALENDAR_V2]
)
SELECT 
    t1.Date,
    t1.week_day_name,
    MAX(CASE WHEN t2.is_workday=1 THEN t2.work_day_num END) AS work_day_serial
FROM (
    SELECT *, SUM(is_workday) OVER (PARTITION BY month_start ORDER BY Date) AS work_day_num
    FROM temp_calendar
) t1
LEFT JOIN (
    SELECT *, SUM(is_workday) OVER (PARTITION BY month_start ORDER BY Date) AS work_day_num
    FROM temp_calendar
) t2 ON t1.month_start = t2.month_start AND t2.Date <= t1.Date
GROUP BY t1.Date, t1.week_day_name
ORDER BY t1.Date ASC

效果示例 #

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