如何在Snowflake中创建排除节假日的工作日日历表并设置次月快照

当然可以在Snowflake中创建满足需求的工作日历表，以下是分步实现方案：

1. 创建基础日历表 #

先生成包含日期范围、工作日标记的基础表，自动排除周末：

CREATE OR REPLACE TABLE WORKDAY_CALENDAR (
    DATE_KEY DATE PRIMARY KEY,
    IS_WORKDAY BOOLEAN,
    MONTH_OF_YEAR INT,
    YEAR INT
);

-- 插入2020-2030年的日期数据（可按需调整范围）
INSERT INTO WORKDAY_CALENDAR (DATE_KEY, IS_WORKDAY, MONTH_OF_YEAR, YEAR)
SELECT
    DATEADD(DAY, SEQ4(), '2020-01-01') AS DATE_KEY,
    -- 排除周六（7）和周日（1，DAYOFWEEK函数的周日为1）
    CASE WHEN DAYOFWEEK(DATEADD(DAY, SEQ4(), '2020-01-01')) NOT IN (1,7) THEN TRUE ELSE FALSE END AS IS_WORKDAY,
    MONTH(DATEADD(DAY, SEQ4(), '2020-01-01')) AS MONTH_OF_YEAR,
    YEAR(DATEADD(DAY, SEQ4(), '2020-01-01')) AS YEAR
FROM TABLE(GENERATOR(ROWCOUNT => 3652))
WHERE DATEADD(DAY, SEQ4(), '2020-01-01') <= '2030-12-31';

2. 维护美国节假日及银行假日表 #

创建独立的节假日表，手动维护联邦节假日和银行假日：

CREATE OR REPLACE TABLE US_HOLIDAYS (
    HOLIDAY_DATE DATE PRIMARY KEY,
    HOLIDAY_NAME VARCHAR(100)
);

-- 插入2024年核心节假日（每年需更新新增年份的节假日）
INSERT INTO US_HOLIDAYS (HOLIDAY_DATE, HOLIDAY_NAME)
VALUES
    ('2024-01-01', 'New Year''s Day'),
    ('2024-01-15', 'Martin Luther King Jr. Day'),
    ('2024-02-19', 'Presidents'' Day'),
    ('2024-05-27', 'Memorial Day'),
    ('2024-07-04', 'Independence Day'),
    ('2024-09-02', 'Labor Day'),
    ('2024-11-28', 'Thanksgiving Day'),
    ('2024-12-25', 'Christmas Day'),
    ('2024-10-14', 'Columbus Day'),
    ('2024-11-11', 'Veterans Day');

然后更新工作日历表，将节假日标记为非工作日：

UPDATE WORKDAY_CALENDAR
SET IS_WORKDAY = FALSE
WHERE DATE_KEY IN (SELECT HOLIDAY_DATE FROM US_HOLIDAYS);

3. 标记每月工作日序号 #

添加列标记每个工作日在当月的顺序，方便快速定位第二个工作日：

ALTER TABLE WORKDAY_CALENDAR ADD COLUMN MONTHLY_WORKDAY_ORDER INT;

UPDATE WORKDAY_CALENDAR
SET MONTHLY_WORKDAY_ORDER = (
    SELECT COUNT(*)
    FROM WORKDAY_CALENDAR WC2
    WHERE WC2.YEAR = WORKDAY_CALENDAR.YEAR
      AND WC2.MONTH_OF_YEAR = WORKDAY_CALENDAR.MONTH_OF_YEAR
      AND WC2.DATE_KEY <= WORKDAY_CALENDAR.DATE_KEY
      AND WC2.IS_WORKDAY = TRUE
)
WHERE IS_WORKDAY = TRUE;

4. 查询下个月的第二个工作日 #

用以下SQL直接获取目标日期：

SELECT DATE_KEY AS NEXT_MONTH_SECOND_WORKDAY
FROM WORKDAY_CALENDAR
WHERE YEAR = YEAR(DATEADD(MONTH, 1, CURRENT_DATE()))
  AND MONTH_OF_YEAR = MONTH(DATEADD(MONTH, 1, CURRENT_DATE()))
  AND MONTHLY_WORKDAY_ORDER = 2;

5. 自动调度快照操作 #

使用Snowflake任务（Task）实现每月第二个工作日自动执行快照：

步骤1：创建快照存储过程（替换为你的实际快照逻辑） #

CREATE OR REPLACE PROCEDURE EXECUTE_DATA_SNAPSHOT()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    -- 示例：将源表数据复制到快照表
    INSERT INTO YOUR_SNAPSHOT_HISTORY_TABLE
    SELECT *, CURRENT_TIMESTAMP() AS SNAPSHOT_TIME
    FROM YOUR_SOURCE_TABLE;
    RETURN 'Monthly snapshot completed successfully';
END;
$$;

步骤2：创建调度任务 #

CREATE OR REPLACE TASK MONTHLY_SECOND_WORKDAY_SNAPSHOT
WAREHOUSE = YOUR_WAREHOUSE_NAME -- 替换为你的仓库名称
SCHEDULE = 'USING CRON 0 9 * * * America/New_York' -- 每天上午9点触发检查
WHEN (
    SELECT DATE_KEY = CURRENT_DATE()
    FROM WORKDAY_CALENDAR
    WHERE YEAR = YEAR(CURRENT_DATE())
      AND MONTH_OF_YEAR = MONTH(CURRENT_DATE())
      AND MONTHLY_WORKDAY_ORDER = 2
)
AS
CALL EXECUTE_DATA_SNAPSHOT();

-- 启用任务
ALTER TASK MONTHLY_SECOND_WORKDAY_SNAPSHOT RESUME;

任务会每天上午9点检查当前日期是否为当月第二个工作日，符合条件则自动执行快照逻辑。

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