Redshift中按日期区间生成行并复制其余列值的实现问询

问题 #

需要编写Redshift脚本处理保单表，根据issue_date（生效日期）和termination_date（终止日期），为两个日期之间的每一年生成一行记录，同时保留原表中其余列的相同值。

已用递归CTE实现日期序列生成，但无法携带其他列信息，当前代码仅能输出日期序列。需解决如何带入其余列，同时针对约1000万行的生成需求给出优化建议。

示例初始数据 #

期望结果 #

当前代码 #

WITH RECURSIVE start_dt AS
(
  SELECT MIN(issue_date) AS s_dt -- step 1: grab start date
  FROM myTable
  WHERE policy_number = '001'
  GROUP BY policy_number
),
end_dt AS
(
  SELECT MAX(effective_date) AS e_dt -- step 2: grab the termination date
  FROM myTable
  WHERE policy_number = '001'
  GROUP BY policy_number
),
dates (dt) AS
(
  -- start at the start date
  SELECT s_dt dt -- selectin start date from step 1
  FROM start_dt
  UNION ALL
  -- recursive lines
  SELECT dateadd(YEAR,1,dt)::DATE dt -- converted to date to avoid type mismatch -- adding annual records until the termination date
         FROM dates
  WHERE dt <= (SELECT e_dt FROM end_dt)
  -- stop at the end date
)
SELECT *
FROM dates

解决方案 #

1. 递归CTE优化版（携带全列信息） #

将原表数据直接带入递归CTE的初始查询，让每一层递归都保留所有保单列信息：

WITH RECURSIVE policy_dates AS (
    -- 初始行：取所有保单的基础信息+初始生效日期作为第一个start_date
    SELECT 
        policy_number,
        issue_date,
        termination_date,
        issue_state,
        product,
        plan_code,
        issue_date AS start_date
    FROM myTable
    -- 单条测试可保留WHERE policy_number = '001'，批量处理则删除
    -- WHERE policy_number = '001'
    
    UNION ALL
    
    -- 递归生成后续年度日期
    SELECT 
        pd.policy_number,
        pd.issue_date,
        pd.termination_date,
        pd.issue_state,
        pd.product,
        pd.plan_code,
        DATEADD(YEAR, 1, pd.start_date)::DATE AS start_date
    FROM policy_dates pd
    -- 终止条件：生成的日期不能超过终止日期
    WHERE DATEADD(YEAR, 1, pd.start_date) <= pd.termination_date
)
-- 补充终止日期作为最后一行（如果最后一个年度加年日期超过终止日期）
SELECT * FROM policy_dates
UNION ALL
SELECT 
    policy_number,
    issue_date,
    termination_date,
    issue_state,
    product,
    plan_code,
    termination_date AS start_date
FROM myTable
WHERE termination_date NOT IN (SELECT start_date FROM policy_dates)
-- 单条测试可加WHERE policy_number = '001'
ORDER BY policy_number, start_date;

2. Cross Join + 数字表方案（适合大数据量） #

递归CTE在Redshift处理千万级数据时性能受限，更高效的方式是用预生成的数字序列表做Cross Join：

步骤1：生成数字序列表

临时生成覆盖最长保单周期的数字序列（比如0到100）：

WITH numbers AS (
    SELECT ROW_NUMBER() OVER () - 1 AS n
    FROM stl_scan LIMIT 101 -- 用Redshift系统表生成足够多的行，可按需调整数量
)
SELECT * FROM numbers;

步骤2：关联保单表生成结果

WITH numbers AS (
    SELECT ROW_NUMBER() OVER () - 1 AS n
    FROM stl_scan LIMIT 101 -- 调整数字范围以覆盖最长保单期限
)
SELECT 
    t.policy_number,
    t.issue_date,
    t.termination_date,
    t.issue_state,
    t.product,
    t.plan_code,
    -- 计算每个年度的start_date，最后一行用终止日期
    CASE 
        WHEN DATEADD(YEAR, n, t.issue_date) <= t.termination_date 
        THEN DATEADD(YEAR, n, t.issue_date)::DATE
        ELSE t.termination_date
    END AS start_date
FROM myTable t
CROSS JOIN numbers n
-- 过滤超出终止日期的无效行
WHERE DATEADD(YEAR, n-1, t.issue_date) <= t.termination_date
-- 单条测试可加WHERE t.policy_number = '001'
ORDER BY t.policy_number, start_date;

优化建议 #

• 优先用数字表方案：Redshift对递归CTE的优化有限，千万级数据下Cross Join预生成数字表的性能更稳定，建议创建持久化的数字表（比如0到200），避免每次临时生成。
• 利用分区排序：如果原表myTable已按policy_number或issue_date分区、排序，关联时会大幅提升性能。
• 先过滤再生成：如果只处理部分保单，先用WHERE子句过滤（比如特定保单号、日期范围），再进行关联生成，减少计算量。
• 避免重复数据：补充终止日期时，用NOT EXISTS或NOT IN过滤已存在的行，防止重复。

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