Redshift日期序列查询求助：年积日获取错误及数字序列生成失败

问题修正说明 #

1. 年积日获取错误：原SQL中DATE_PART(day, yydd)返回的是当月的第几天，要获取年积日（一年中的第几天），需使用DATE_PART(dayofyear, 日期字段)。
2. YYYYDDD格式序列生成：通过拼接年份与补零后的年积日实现，用LPAD函数将年积日补至3位（如1→001），再与年份拼接后转成数字类型。

修正后的SQL（2020-2022年日期范围） #

WITH date_series AS (
    SELECT 
        DATE '2019-12-31' + seq::INTERVAL '1 day' AS full_date
    FROM generate_series(1, (DATE '2023-01-01' - DATE '2020-01-01')) seq
)
SELECT
    full_date,
    DATE_PART(day, full_date) AS day_month_number, -- 当月日期
    DATE_PART(dayofweek, full_date) AS day_week_number, -- 周几（Redshift中1=周日，2=周一…7=周六）
    DATE_PART(dayofyear, full_date) AS day_of_year, -- 年积日
    DATE_PART(quarter, full_date) AS quarter_number, -- 季度
    (DATE_PART(year, full_date)::VARCHAR || LPAD(DATE_PART(dayofyear, full_date)::VARCHAR, 3, '0'))::BIGINT AS yyyyddd -- YYYYDDD格式数字
FROM date_series;

额外说明 #

• 若需要以周一作为一周的第一天，可将day_week_number的计算改为DATE_PART(isodow, full_date)，此时1=周一，7=周日。
• 日期范围可通过调整DATE '2019-12-31'（起始日期前一天）和DATE '2023-01-01'（结束日期后一天）灵活修改。

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