在Redshift中使用PostgreSQL生成动态季度的问题求助

问题背景 #

现有表结构：

ID   Start_date    season
1     01/01/2022    1
1     01/01/2023    2

需求：将每个season拆分为季度，直到下一个season开始，期望输出：

ID   Start_date    end_date       season    status  status_level
1     01/01/2022    31/03/2022    1          air      0
1     01/04/2022    31/06/2022    1          lag      1
1     01/07/2022    31/09/2022    1          off      2
1    01/10/2022     31/12/2022     1          off      3
1    01/01/2023     31/03/2023    2          air      0

规则：

• 每个season的第一个季度标记为air，第二个季度标记为lag，其余季度标记为off
• 进入下一个season后，从其Start_date开始重新按规则生成季度

原SQL问题分析 #

报错信息翻译：

ERROR: 指定的类型或函数（每个INFO消息对应一个）在Redshift表上不支持。

问题点：

1. Redshift中generate_series存在使用限制，直接作为CTE数据源易触发不支持错误，需改用递归CTE生成序列。
2. 原SQL硬编码起始日期，未关联原表的season数据，无法动态处理每个season的时间范围。
3. 全局ROW_NUMBER()未按season分组，导致每个season的状态无法重置。

修正后的Redshift SQL代码 #

WITH season_boundaries AS (
    -- 获取每个season的起始和结束日期（结束日期为下一个season的起始日减1天）
    SELECT 
        ID,
        Start_date,
        season,
        LEAD(Start_date, 1, '9999-12-31'::DATE) OVER (PARTITION BY ID ORDER BY season) AS next_season_start
    FROM your_table_name -- 替换为实际表名
),
recursive_quarters AS (
    -- 递归生成每个season的季度序列
    SELECT 
        ID,
        Start_date AS quarter_start,
        DATE_TRUNC('quarter', Start_date) + INTERVAL '3 months' - INTERVAL '1 day' AS quarter_end,
        season,
        next_season_start,
        1 AS quarter_num
    FROM season_boundaries
    UNION ALL
    SELECT 
        ID,
        quarter_end + INTERVAL '1 day' AS quarter_start,
        LEAST(quarter_end + INTERVAL '3 months', next_season_start - INTERVAL '1 day') AS quarter_end,
        season,
        next_season_start,
        quarter_num + 1
    FROM recursive_quarters
    WHERE quarter_end + INTERVAL '1 day' < next_season_start
)
SELECT 
    ID,
    TO_CHAR(quarter_start, 'DD/MM/YYYY') AS Start_date,
    TO_CHAR(quarter_end, 'DD/MM/YYYY') AS end_date,
    season,
    CASE 
        WHEN quarter_num = 1 THEN 'air'
        WHEN quarter_num = 2 THEN 'lag'
        ELSE 'off'
    END AS status,
    quarter_num - 1 AS status_level
FROM recursive_quarters
ORDER BY ID, season, quarter_start;

代码说明 #

1. season_boundaries CTE：通过LEAD()函数获取每个season的结束边界——下一个season的起始日期减1天，最后一个season用9999-12-31作为默认结束。
2. recursive_quarters CTE：使用递归方式生成每个season内的季度序列，确保每个季度的结束日期不超过下一个season的起始日。
3. 最终SELECT：根据季度在season内的序号（quarter_num）标记状态，并计算status_level，同时格式化日期为需求的DD/MM/YYYY格式。

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