如何处理某月特定星期几的节假日？Snowflake如何过滤工作日数据？

完全可以在Snowflake中实现这类需求，核心是先计算出动态节假日的日期，再结合周末判断过滤出有效工作日。以下是具体实现步骤：

1. 计算动态节假日（以感恩节为例） #

对于像“11月第4个星期四”这类规则的节假日，可以通过DATE_TRUNC和DATEADD组合计算：

• 先生成当年11月1日的日期
• 用DATE_TRUNC('WEEK', ...)找到该周的起始日（Snowflake默认周日为一周第1天）
• 加3天得到当月第一个周四，再加上3周（21天）就是第4个周四

示例SQL：

SELECT 
  DATEADD(DAY, 21, DATE_TRUNC('WEEK', DATE_FROM_PARTS(2024, 11, 1)) + INTERVAL '3 days') AS thanksgiving_2024;

2. 生成全量节假日列表 #

如果需要处理多个动态/固定节假日，可以用CTE生成年度节假日集合，或者创建持久化的节假日表。

方法一：用CTE临时生成 #

WITH yearly_holidays AS (
  SELECT 
    year,
    -- 感恩节：11月第4个周四
    DATEADD(DAY, 21, DATE_TRUNC('WEEK', DATE_FROM_PARTS(year, 11, 1)) + INTERVAL '3 days') AS thanksgiving,
    -- 劳动节：9月第1个周一
    DATEADD(DAY, 0, DATE_TRUNC('WEEK', DATE_FROM_PARTS(year, 9, 1)) + INTERVAL '1 day') AS labor_day,
    -- 固定节假日：圣诞节
    DATE_FROM_PARTS(year, 12, 25) AS christmas
  FROM (SELECT DISTINCT YEAR(business_date) AS year FROM your_business_table) years
),
all_holidays AS (
  SELECT thanksgiving AS holiday_date FROM yearly_holidays
  UNION ALL
  SELECT labor_day FROM yearly_holidays
  UNION ALL
  SELECT christmas FROM yearly_holidays
)
SELECT * FROM all_holidays;

方法二：创建持久化节假日表（推荐） #

如果节假日规则稳定，创建专门的表维护更高效：

CREATE OR REPLACE TABLE company_holidays (
  holiday_date DATE PRIMARY KEY,
  holiday_name VARCHAR(50) NOT NULL
);

-- 批量插入动态节假日
INSERT INTO company_holidays (holiday_date, holiday_name)
SELECT 
  DATEADD(DAY, 21, DATE_TRUNC('WEEK', DATE_FROM_PARTS(year, 11, 1)) + INTERVAL '3 days'), 'Thanksgiving'
FROM (SELECT 2023 AS year UNION ALL SELECT 2024 UNION ALL SELECT 2025) years
UNION ALL
SELECT DATEADD(DAY, 0, DATE_TRUNC('WEEK', DATE_FROM_PARTS(year, 9, 1)) + INTERVAL '1 day'), 'Labor Day'
FROM (SELECT 2023 AS year UNION ALL SELECT 2024 UNION ALL SELECT 2025) years;

-- 插入固定节假日
INSERT INTO company_holidays (holiday_date, holiday_name)
SELECT DATE_FROM_PARTS(year, 1, 1), 'New Year''s Day'
FROM (SELECT 2023 AS year UNION ALL SELECT 2024 UNION ALL SELECT 2025) years;

3. 过滤有效工作日 #

结合周末判断（Snowflake中DAYOFWEEK返回1=周日，7=周六）和节假日列表，过滤出符合要求的数据：

用CTE的方式过滤 #

WITH yearly_holidays AS (
  SELECT 
    year,
    DATEADD(DAY, 21, DATE_TRUNC('WEEK', DATE_FROM_PARTS(year, 11, 1)) + INTERVAL '3 days') AS thanksgiving,
    DATEADD(DAY, 0, DATE_TRUNC('WEEK', DATE_FROM_PARTS(year, 9, 1)) + INTERVAL '1 day') AS labor_day,
    DATE_FROM_PARTS(year, 12, 25) AS christmas
  FROM (SELECT DISTINCT YEAR(business_date) AS year FROM your_business_table) years
),
all_holidays AS (
  SELECT thanksgiving AS holiday_date FROM yearly_holidays
  UNION ALL
  SELECT labor_day FROM yearly_holidays
  UNION ALL
  SELECT christmas FROM yearly_holidays
)
SELECT *
FROM your_business_table
WHERE 
  DAYOFWEEK(business_date) NOT IN (1, 7) -- 排除周末
  AND business_date NOT IN (SELECT holiday_date FROM all_holidays); -- 排除节假日

用持久化表的方式过滤 #

SELECT b.*
FROM your_business_table b
LEFT JOIN company_holidays h 
  ON b.business_date = h.holiday_date
WHERE 
  DAYOFWEEK(business_date) NOT IN (1, 7)
  AND h.holiday_date IS NULL; -- 未匹配到节假日即有效工作日

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