如何修改SQL查询以正确计算排除周末节假日的dsr_day_number_reporting

核心问题分析 #

原查询的SUM()窗口函数未正确处理非工作日的编号继承逻辑，导致连续编号混乱；同时sales_date_id_reporting未正确关联有效工作日日期。

修正后的SQL代码片段 #

/*...
... some code */
LEFT JOIN (
    SELECT 
        y.date, 
        y.month, 
        y.weekdayname, 
        y.weekday,
        -- 标记当前日期是否为有效工作日
        CASE WHEN y.ISWEEKEND = FALSE AND y.ISHOLIDAY_AUS = FALSE THEN 1 ELSE 0 END AS is_workday,
        -- 正确计算dsr_day_number_reporting
        CASE
            -- 每月第一天强制从1开始
            WHEN DAY(y.date) = 1 THEN 1
            -- 工作日：累计当月从第一天到当前的工作日总数
            WHEN CASE WHEN y.ISWEEKEND = FALSE AND y.ISHOLIDAY_AUS = FALSE THEN 1 ELSE 0 END = 1 THEN
                SUM(CASE WHEN y.ISWEEKEND = FALSE AND y.ISHOLIDAY_AUS = FALSE THEN 1 ELSE 0 END) 
                OVER (PARTITION BY y.yyyymm ORDER BY y.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            -- 非工作日：继承最近的工作日编号
            ELSE
                LAST_VALUE(
                    CASE WHEN y.ISWEEKEND = FALSE AND y.ISHOLIDAY_AUS = FALSE THEN
                        SUM(CASE WHEN y.ISWEEKEND = FALSE AND y.ISHOLIDAY_AUS = FALSE THEN 1 ELSE 0 END) 
                        OVER (PARTITION BY y.yyyymm ORDER BY y.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                    END IGNORE NULLS
                ) OVER (PARTITION BY y.yyyymm ORDER BY y.date)
        END AS dsr_day_number_reporting,
        -- 生成正确的sales_date_id_reporting（仅关联工作日）
        TO_VARCHAR(
            COALESCE(
                -- 工作日直接使用当前日期
                CASE WHEN y.ISWEEKEND = FALSE AND y.ISHOLIDAY_AUS = FALSE THEN y.date END,
                -- 非工作日取最近的前一个工作日日期
                LAST_VALUE(CASE WHEN y.ISWEEKEND = FALSE AND y.ISHOLIDAY_AUS = FALSE THEN y.date END IGNORE NULLS) 
                OVER (PARTITION BY y.yyyymm ORDER BY y.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            ),
            'yyyyMMdd'
        ) AS sales_date_id_reporting
    FROM 
    /*...
    ... some code */
) AS sub
/*...
... some code */

关键修改说明 #

1. dsr_day_number_reporting逻辑优化

   • 保留每月第一天强制为1的规则。
   • 工作日通过SUM()窗口函数累计当月有效工作日数量，确保编号仅在工作日递增。
   • 非工作日使用LAST_VALUE(IGNORE NULLS)获取最近的工作日编号，避免非工作日干扰连续编号。

2. sales_date_id_reporting修正

   • 工作日直接生成当日的日期ID。
   • 非工作日自动关联最近的前一个工作日日期，确保ID仅对应有效工作日，符合业务需求。

可选调整（若需求为“每月第一个工作日从1开始”） #

如果实际业务要求是当月第一个工作日编号为1（而非每月第一天强制为1），可简化dsr_day_number_reporting的逻辑：

CASE
    WHEN is_workday = 1 THEN SUM(is_workday) OVER (PARTITION BY y.yyyymm ORDER BY y.date)
    ELSE NULL -- 非工作日不显示编号，或继承最近工作日编号
END AS dsr_day_number_reporting

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