AWS Redshift按年拆分日期区间生成多行SQL实现方案

问题描述 #

现有包含user_id、start_date、end_date三列的数据，示例如下：

user_id     start_date    end_date
  1         2022-07-30    2025-07-30
  2         2022-05-25    2027-05-25

需要基于每个用户的日期区间，按每年同一天拆分成多行数据，预期结果：

user_id     start_date    end_date
  1         2022-07-30    2023-07-30
  1         2023-07-30    2024-07-30
  1         2024-07-30    2025-07-30

  2         2022-05-25    2023-05-25
  2         2023-05-25    2024-05-25
  2         2024-05-25    2025-05-25
  2         2025-05-25    2026-05-25
  2         2026-05-25    2027-05-25

限制条件：使用AWS Redshift环境，处于长查询中间，无法使用递归CTE（递归CTE需以WITH子句开头），需支持不同用户的动态日期区间长度。

实现方案 #

Redshift可以利用数字生成表替代递归CTE的功能，具体SQL代码如下：

SELECT
    t.user_id,
    DATEADD(year, n.num, t.start_date) AS start_date,
    DATEADD(year, n.num + 1, t.start_date) AS end_date
FROM
    -- 替换为你的原始数据表名
    your_table t
JOIN (
    -- 生成0到100的数字序列，可根据实际最大年数调整上限
    SELECT ROW_NUMBER() OVER () - 1 AS num
    FROM SVV_TABLES
    LIMIT 101
) n ON DATEADD(year, n.num + 1, t.start_date) <= t.end_date
ORDER BY
    t.user_id,
    n.num;

代码说明 #

• 使用Redshift系统视图SVV_TABLES生成数字序列，也可替换为其他行数足够的系统表（如STV_BLOCKLIST），只要行数能覆盖所有用户的最大区间年数即可
• DATEADD(year, n.num, t.start_date)计算当前拆分区间的起始日期，n.num为从0开始的年份偏移量
• DATEADD(year, n.num + 1, t.start_date)计算当前拆分区间的结束日期
• JOIN条件DATEADD(year, n.num + 1, t.start_date) <= t.end_date过滤掉超出原始用户区间的无效行，确保拆分结果符合原始日期范围

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