如何编写UDF获取指定日期后的第N个非节假日工作日

需要编写一个用户定义函数（UDF），接收日期输入和整数N，返回该日期之后的第N个工作日——工作日指非周六、周日且不在节假日表MY_TABLE的holiday_date列表中的日期。

节假日表MY_TABLE结构：

示例：输入日期2023-06-30、N=10，期望返回2023-07-17（需跳过周六、周日及7月4日）。

现有代码仅处理了周末筛选，未考虑节假日，且逻辑无法正确跳过不符合条件的日期以获取第N个工作日：

CREATE OR REPLACE FUNCTION GET_NTH_WEEKDAY(DATE_INPUT VARCHAR, NTH_DAY INTEGER)
RETURNS DATE
AS
$$
SELECT 
    DATEADD(DAY, 1 * VALUE, TO_DATE(DATE_INPUT, 'YYYY-MM-DD')) AS MY_DATE, 
    DAYNAME(MY_DATE) AS DAY_OF_WEEK
FROM TABLE (FLATTEN(INPUT => ARRAY_GENERATE_RANGE(1, NTH_DAY, 1)))
WHERE DAY_OF_WEEK NOT IN ('Sat', 'Sun') 
$$;

需要完善逻辑，实现同时排除周末和节假日的需求。

核心思路是生成足够多的候选日期（预留跳过周末/节假日的冗余天数），筛选出符合条件的工作日后取第N个。

以下是修正后的UDF代码：

CREATE OR REPLACE FUNCTION GET_NTH_WORKDAY(DATE_INPUT VARCHAR, NTH_DAY INTEGER)
RETURNS DATE
AS
$$
WITH candidate_dates AS (
    -- 生成输入日期后1到N*2天的候选日期，预留足够跳过空间
    SELECT DATEADD(DAY, value, TO_DATE(DATE_INPUT, 'YYYY-MM-DD')) AS my_date
    FROM TABLE(FLATTEN(INPUT => ARRAY_GENERATE_RANGE(1, NTH_DAY * 2, 1)))
),
valid_workdays AS (
    -- 筛选非周末且非节假日的日期，并按顺序编号
    SELECT 
        my_date,
        ROW_NUMBER() OVER (ORDER BY my_date) AS day_rank
    FROM candidate_dates
    WHERE 
        DAYNAME(my_date) NOT IN ('Sat', 'Sun')
        AND my_date NOT IN (SELECT holiday_date FROM MY_TABLE)
)
-- 提取第N个符合条件的工作日
SELECT my_date FROM valid_workdays WHERE day_rank = NTH_DAY
$$;

关键逻辑说明 #

1. 候选日期生成：用ARRAY_GENERATE_RANGE生成N*2天的候选日期，确保有足够天数覆盖需要跳过的周末和节假日（若节假日密集，可将倍数调整为N*3）。
2. 工作日筛选：通过DAYNAME排除周六、周日，同时用子查询排除MY_TABLE中的节假日。
3. 日期排名取值：用ROW_NUMBER()为符合条件的日期按时间顺序编号，最终取编号等于N的日期。

示例验证 #

输入DATE_INPUT='2023-06-30'、NTH_DAY=10时：

• 从2023-07-01开始筛选，跳过7-01（周六）、7-02（周日）、7-04（节假日），最终第10个工作日为2023-07-17，符合预期。

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