编写UDF获取指定日期前非周末且不在MY_TABLE的有效日期

你之前的两个函数同名会导致冲突，需要把逻辑整合到一个函数里。以下是完整的实现方案（以Snowflake SQL为例，其他数据库仅需调整星期判断函数即可）：

CREATE OR REPLACE FUNCTION GET_VALID_PREVIOUS_DATE(_INPUT_DATE DATE)
RETURNS DATE
$$
WITH RECURSIVE date_search AS (
    -- 从输入日期的前一天开始查找
    SELECT DATEADD(day, -1, _INPUT_DATE) AS candidate_date
    UNION ALL
    -- 递归向前生成下一个日期，直到找到符合条件的
    SELECT DATEADD(day, -1, candidate_date)
    FROM date_search
    WHERE 
        -- 当前候选日期不符合要求时继续查找：是周末，或者已存在于表中
        DAYOFWEEK(candidate_date) IN (1, 7)
        OR EXISTS (SELECT 1 FROM MY_TABLE WHERE ALL_DATES = candidate_date)
)
-- 筛选出第一个符合条件的日期
SELECT candidate_date
FROM date_search
WHERE 
    DAYOFWEEK(candidate_date) NOT IN (1, 7)
    AND NOT EXISTS (SELECT 1 FROM MY_TABLE WHERE ALL_DATES = candidate_date)
LIMIT 1;
$$;

关键逻辑说明： #

• 递归CTE：date_search 从输入日期的前一天开始，逐个向前生成日期，直到找到满足条件的日期为止。
• 条件判断：
  • 非周末：通过DAYOFWEEK函数判断，Snowflake中1代表周日、7代表周六，因此排除这两个值。
  • 日期不存在于表中：用EXISTS子查询检查MY_TABLE.ALL_DATES中是否有匹配记录。
• 输入类型优化：将参数设为DATE类型比VARCHAR更稳妥，避免字符串转日期的格式错误。如果必须用字符串输入，可在函数内部先转换，比如：TO_DATE(_INPUT_DATE, 'YYYY-MM-DD')（根据实际日期格式调整）。

适配其他数据库的调整： #

如果使用的不是Snowflake，只需修改星期判断的函数：

• MySQL：DAYOFWEEK规则和Snowflake一致（1=周日，7=周六），无需修改。
• PostgreSQL：用EXTRACT(DOW FROM candidate_date)，0=周日、6=周六，条件改为EXTRACT(DOW FROM candidate_date) IN (0, 6)。
• SQL Server：用DATEPART(WEEKDAY, candidate_date)，注意星期起始可能由SET DATEFIRST决定，默认7是周日、1是周一，需根据设置调整排除值。

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