如何在Amazon Redshift中创建自动生成重复公假日日期的表？

我来给你详细讲讲怎么在Redshift里搞定自动生成重复公假日的需求，这个方案我之前在项目里实际用过，亲测好用：

第一步：先建一个基础配置表 #

首先得有个地方存那些每年固定日期的假日信息——别存年份，只存月份、日期和假日名称就行，这样后续才能动态生成不同年份的日期。

建表语句示例：

CREATE TABLE public.holiday_config (
    holiday_id INT PRIMARY KEY,
    holiday_name VARCHAR(100) NOT NULL,
    holiday_month INT NOT NULL CHECK (holiday_month BETWEEN 1 AND 12),
    holiday_day INT NOT NULL CHECK (holiday_day BETWEEN 1 AND 31)
);

然后把你的示例假日插进去：

INSERT INTO public.holiday_config (holiday_id, holiday_name, holiday_month, holiday_day)
VALUES
    (1, '自定义固定假日', 6, 16),
    (2, '圣诞节', 12, 25);

第二步：生成指定年份范围的公假日 #

接下来用Redshift的generate_series函数生成你需要覆盖的年份序列，再和基础配置表做交叉连接，把年份和月日拼接成完整的假日日期。

比如要生成2018到2030年的所有重复假日，用这个查询：

WITH year_range AS (
    -- 生成2018到2030的年份序列，可按需调整起止年份
    SELECT generate_series(2018, 2030) AS holiday_year
)
SELECT
    y.holiday_year,
    c.holiday_month,
    c.holiday_day,
    TO_DATE(
        CONCAT(y.holiday_year, '-', c.holiday_month, '-', c.holiday_day),
        'YYYY-MM-DD'
    ) AS holiday_date,
    c.holiday_name
FROM year_range y
CROSS JOIN public.holiday_config c
-- 过滤掉无效日期，比如非闰年的2月29日、2月30日这种
WHERE TO_DATE(
    CONCAT(y.holiday_year, '-', c.holiday_month, '-', c.holiday_day),
    'YYYY-MM-DD'
) IS NOT NULL
ORDER BY holiday_date;

第三步：处理特殊情况（比如闰年2月29日） #

如果你的假日里有2月29日这种闰年专属日期，上面的查询会自动过滤非闰年的情况。但如果需要在非闰年把这个假日调整到2月28日或者3月1日，可以加个CASE逻辑处理：

比如把非闰年的2月29日替换成2月28日：

WITH year_range AS (
    SELECT generate_series(2018, 2030) AS holiday_year
)
SELECT
    y.holiday_year,
    c.holiday_month,
    c.holiday_day,
    CASE
        -- 判断是否是2月29日且当前年份不是闰年
        WHEN c.holiday_month = 2 AND c.holiday_day = 29 
             AND NOT EXISTS (
                 SELECT 1 FROM (SELECT 1) t 
                 WHERE EXTRACT(DAY FROM TO_DATE(CONCAT(y.holiday_year, '-02-29'), 'YYYY-MM-DD')) = 29
             ) 
        THEN TO_DATE(CONCAT(y.holiday_year, '-02-28'), 'YYYY-MM-DD')
        ELSE TO_DATE(CONCAT(y.holiday_year, '-', c.holiday_month, '-', c.holiday_day), 'YYYY-MM-DD')
    END AS holiday_date,
    c.holiday_name
FROM year_range y
CROSS JOIN public.holiday_config c
ORDER BY holiday_date;

第四步：持久化生成的假日表（可选） #

如果需要把生成的假日表长期保存，可以创建一个正式表并插入数据：

CREATE TABLE public.holidays (
    holiday_date DATE PRIMARY KEY,
    holiday_name VARCHAR(100) NOT NULL
);

INSERT INTO public.holidays (holiday_date, holiday_name)
WITH year_range AS (
    SELECT generate_series(2018, 2030) AS holiday_year
)
SELECT
    TO_DATE(
        CONCAT(y.holiday_year, '-', c.holiday_month, '-', c.holiday_day),
        'YYYY-MM-DD'
    ) AS holiday_date,
    c.holiday_name
FROM year_range y
CROSS JOIN public.holiday_config c
WHERE TO_DATE(
    CONCAT(y.holiday_year, '-', c.holiday_month, '-', c.holiday_day),
    'YYYY-MM-DD'
) IS NOT NULL;

额外小提示 #

• generate_series在Redshift里支持生成整数序列，你可以随时调整起止年份，比如要生成未来5年的假日，直接改参数就行。
• 要是需要定期更新假日表（比如每年新增下一年的假日），可以写个存储过程，或者用Redshift的调度工具、外部定时任务来自动执行生成逻辑。

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