Snowflake SQL计算澳大利亚各州下一个工作日问题排查

核心问题定位 #

你的代码中下一个工作日计算逻辑存在根本性错误，以下是具体排查方向和修正建议：

1. PARTITION BY的逻辑完全错误 #

你当前使用PARTITION BY "ACT Work Day" ='Work Day'来分组，这会把数据拆成两个独立分组：

• 一组是"ACT Work Day"='Work Day'的所有工作日
• 另一组是"ACT Work Day" IS NULL的所有非工作日

在非工作日的分组里调用LEAD("Report Date")，只会返回该分组内的下一个非工作日日期，完全达不到"找下一个工作日"的目的。

2. "ACT Work Day"字段定义不完整 #

当前CASE语句仅在非假期且非周末时标记为'Work Day'，其余情况该字段为NULL。这会导致后续窗口函数和判断逻辑出现空值异常，应该明确区分工作日和非工作日：

CASE 
    WHEN "ACT Public Holiday" IS NULL THEN 'Work Day'
    ELSE 'Non-Work Day'
END AS "ACT Work Day"

3. DISTINCT可能干扰窗口函数结果 #

如果V_D_CALENDAR表是每日一条数据，DISTINCT是多余的；如果假期表存在重复匹配（比如同一天有多个假期记录），DISTINCT会导致日期行被去重，打乱LEAD函数的连续日期序列，进而计算错误。

4. 假期匹配的验证 #

需要确认ACT州的假期关联是否正确：

• 检查IS_ALL_NODES='Y'的全国假期是否正确关联到ACT州
• 验证NODE_ID='ACT'的专属假期是否和日历日期正确匹配
• 可以单独执行以下语句排查假期匹配结果：

SELECT C."Report Date", ACT.holiday_name
FROM "ODS"."DWBI_DATAHUB"."V_D_CALENDAR" C
LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" ACT 
  ON C."Report Date" = ACT.HOLIDAY_DATE 
  AND (ACT.IS_ALL_NODES = 'Y' OR ACT.NODE_ID = 'ACT')
WHERE C."Report Date" BETWEEN '2024-01-01' AND '2024-01-10'
ORDER BY C."Report Date";

正确的下一个工作日计算思路 #

方法1：使用窗口函数标记并取后续第一个工作日 #

先标记所有工作日，再用LEAD结合条件判断找到下一个工作日：

WITH calendar_with_workday AS (
    SELECT 
        "Report Date",
        "Calendar Day in Week",
        -- 标记ACT州的非工作日：周末+公共假期
        CASE 
            WHEN ACT.holiday_name IS NOT NULL OR "Calendar Day in Week" IN ('Sat','Sun') 
            THEN 'Non-Work Day'
            ELSE 'Work Day'
        END AS "ACT Work Day"
    FROM "ODS"."DWBI_DATAHUB"."V_D_CALENDAR" C
    LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" ACT 
        ON C."Report Date" = ACT.HOLIDAY_DATE 
        AND (ACT.IS_ALL_NODES = 'Y' OR ACT.NODE_ID = 'ACT')
    WHERE "Report Date" IS NOT NULL
),
workday_next AS (
    SELECT 
        *,
        -- 取当前日期之后第一个工作日
        LEAD(CASE WHEN "ACT Work Day" = 'Work Day' THEN "Report Date" END) 
            OVER (ORDER BY "Report Date") AS "ACT Next Work Day"
    FROM calendar_with_workday
)
-- 填充连续非工作日的下一个工作日
SELECT 
    "Report Date",
    "Calendar Day in Week",
    "ACT Work Day",
    LAST_VALUE("ACT Next Work Day") IGNORE NULLS 
        OVER (ORDER BY "Report Date" ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS "ACT Next Work Day"
FROM workday_next
ORDER BY "Report Date";

方法2：使用Snowflake的递归CTE（适合批量计算） #

如果需要处理大量日期，递归CTE可以逐个日期查找下一个工作日：

WITH RECURSIVE calendar_base AS (
    SELECT 
        "Report Date",
        CASE 
            WHEN ACT.holiday_name IS NOT NULL OR "Calendar Day in Week" IN ('Sat','Sun') 
            THEN FALSE
            ELSE TRUE
        END IS_WORKDAY
    FROM "ODS"."DWBI_DATAHUB"."V_D_CALENDAR" C
    LEFT JOIN "ODS"."ODS"."DS_ALLIANCE_HOLIDAY" ACT 
        ON C."Report Date" = ACT.HOLIDAY_DATE 
        AND (ACT.IS_ALL_NODES = 'Y' OR ACT.NODE_ID = 'ACT')
    WHERE "Report Date" IS NOT NULL
),
next_workday AS (
    SELECT 
        "Report Date",
        IS_WORKDAY,
        CASE WHEN IS_WORKDAY THEN "Report Date" ELSE NULL END AS NEXT_WORKDAY
    FROM calendar_base
    UNION ALL
    SELECT 
        cb."Report Date",
        cb.IS_WORKDAY,
        nw.NEXT_WORKDAY
    FROM calendar_base cb
    JOIN next_workday nw 
        ON cb."Report Date" = DATEADD(DAY, -1, nw."Report Date")
    WHERE cb.IS_WORKDAY = FALSE AND nw.NEXT_WORKDAY IS NOT NULL
)
SELECT DISTINCT "Report Date", IS_WORKDAY, NEXT_WORKDAY AS "ACT Next Work Day"
FROM next_workday
ORDER BY "Report Date";

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