SQL Server中Row_Number()未跳过NULL/0值计算工作日的问题

在SQL Server的日期维度表中，需要生成当月第n个工作日的序号字段，已通过CASE将工作日标记为1、周末为0，但使用ROW_NUMBER()时会包含所有日期（包括周末），导致工作日序号错误（例如20140407的序号被计算为7，实际应为5）。

原SQL代码 #

SELECT 
    DATE_KEY_YYYYMM,
    [Date KEY],
    [Day of Week Short Name],
    [Day of Week Number],
    CASE 
        WHEN [Day of Week Number] IN (1, 2, 3, 4, 5) 
            THEN 1 
            ELSE 0 
    END AS BusinessDay,
    CASE 
        WHEN [Day of Week Number] IN (6, 7) 
            THEN NULL 
            ELSE ROW_NUMBER() OVER (PARTITION BY [Date_Key_YYYYMM] ORDER BY [Date Key]) 
    END AS [RowCount]
FROM
    [DIM].[Call Date]

当前输出 #

期望输出 #

解决方案 #

问题核心是ROW_NUMBER()会对分区内所有行计数，无论是否为工作日。改用累计计数的方式，仅统计当前日期及之前的工作日数量：

方法1：基于工作日条件计数 #

SELECT 
    DATE_KEY_YYYYMM,
    [Date KEY],
    [Day of Week Short Name],
    [Day of Week Number],
    CASE 
        WHEN [Day of Week Number] IN (1, 2, 3, 4, 5) 
            THEN 1 
            ELSE 0 
    END AS BusinessDay,
    CASE 
        WHEN [Day of Week Number] IN (6, 7) 
            THEN NULL 
            ELSE COUNT(CASE WHEN [Day of Week Number] IN (1,2,3,4,5) THEN 1 END) 
                 OVER (PARTITION BY DATE_KEY_YYYYMM ORDER BY [Date Key] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    END AS [RowCount]
FROM
    [DIM].[Call Date]

方法2：基于已生成的BusinessDay字段求和（更简洁） #

SELECT 
    DATE_KEY_YYYYMM,
    [Date KEY],
    [Day of Week Short Name],
    [Day of Week Number],
    CASE 
        WHEN [Day of Week Number] IN (1, 2, 3, 4, 5) 
            THEN 1 
            ELSE 0 
    END AS BusinessDay,
    CASE 
        WHEN [Day of Week Number] IN (6, 7) 
            THEN NULL 
            ELSE SUM(BusinessDay) OVER (PARTITION BY DATE_KEY_YYYYMM ORDER BY [Date Key])
    END AS [RowCount]
FROM
    [DIM].[Call Date]

原理说明 #

• 两种方法均使用窗口函数实现累计统计，仅对工作日行计数/求和
• 周末行保留NULL，工作日行得到连续的当月工作日序号
• SUM(BusinessDay)的写法直接复用已生成的标记字段，代码更简洁

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