如何在Redshift中创建指定格式日期表并完成数据插入

一、参考表结构 #

如果你还没完成表结构创建，可以直接使用以下语句：

CREATE TABLE dim_date (
    date DATE PRIMARY KEY,
    year SMALLINT NOT NULL,
    month SMALLINT NOT NULL,
    month_name VARCHAR(20) NOT NULL,
    year_month CHAR(6) NOT NULL,
    month_name_year VARCHAR(30) NOT NULL
);

二、插入数据SQL #

我们可以通过递归CTE生成连续日期序列，一次性插入所有符合格式要求的日期数据：

INSERT INTO dim_date (date, year, month, month_name, year_month, month_name_year)
WITH RECURSIVE date_series AS (
    -- 自定义起始日期
    SELECT '2010-01-01'::DATE AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM date_series
    -- 自定义结束日期
    WHERE dt < '2030-12-31'::DATE
)
SELECT
    dt AS date,
    EXTRACT(YEAR FROM dt)::SMALLINT AS year,
    EXTRACT(MONTH FROM dt)::SMALLINT AS month,
    TRIM(INITCAP(TO_CHAR(dt, 'month'))) AS month_name,
    TO_CHAR(dt, 'YYYYMM') AS year_month,
    LOWER(TRIM(TO_CHAR(dt, 'month'))) || EXTRACT(YEAR FROM dt) AS month_name_year
FROM date_series
-- 解除Redshift默认1000的递归深度限制
OPTION (MAXRECURSION 0);

三、字段逻辑说明 #

• date：直接使用生成的连续日期值
• year：从日期中提取数值类型的年份
• month：从日期中提取数值类型的月份（取值1-12）
• month_name：生成首字母大写的月份全称，通过TRIM去掉Redshift默认补的尾部空格
• year_month：直接通过格式转换生成202001样式的年月字符串
• month_name_year：小写月份全称拼接年份，生成january2020样式的字符串

四、注意事项 #

• 你可以修改递归CTE中的起始、结束日期，自定义生成的日期范围
• 如果表中已有历史数据，插入前可执行TRUNCATE TABLE dim_date;清空表，避免主键冲突
• 如果你使用的Redshift版本支持upsert，可以在INSERT语句末尾加ON CONFLICT (date) DO NOTHING跳过已存在的日期

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