在Redshift中重复行N次并递增整数年份字段

现有数据集包含对象的获取年份（year）和生命周期（lifespan），需要为每个对象的生命周期内每一年生成一行数据，且每行的year字段逐年递增。PostgreSQL中可以用generate_series()轻松实现，但Redshift的该函数行为不同，无法直接复用PG的方法。

方法1：递归CTE实现 #

Redshift支持递归CTE，可通过递归方式逐行生成扩展数据：

WITH RECURSIVE expanded_data AS (
    -- 初始查询：取原始数据，标记当前为生命周期第0年
    SELECT year, lifespan, 0 AS offset
    FROM data
    UNION ALL
    -- 递归生成后续年份，直到覆盖完整生命周期
    SELECT ed.year + 1, ed.lifespan, ed.offset + 1
    FROM expanded_data ed
    WHERE ed.offset < ed.lifespan - 1
)
SELECT year, lifespan
FROM expanded_data
ORDER BY year, lifespan;

说明：

• 初始CTE获取原始数据，offset字段标记当前是生命周期的第几年（从0开始）
• 递归部分每次将年份加1、offset加1，直到offset达到lifespan-1（原始行已对应第0年，需额外生成lifespan-1行）
• 最终按年份排序得到目标结果

方法2：数字辅助表（适合大生命周期场景） #

如果数据的lifespan数值较大，递归CTE性能可能受限，可预先创建数字辅助表关联生成结果：

1. 创建数字表（生成0到100的连续整数，可根据实际最大生命周期调整范围）：

CREATE TEMP TABLE numbers AS
SELECT row_number() OVER () - 1 AS num
FROM stl_scan
LIMIT 100;

2. 关联原始数据生成扩展行：

SELECT d.year + n.num AS year, d.lifespan
FROM data d
JOIN numbers n ON n.num < d.lifespan
ORDER BY year, lifespan;

说明：

• 数字表提供连续的基准数值，数量需覆盖所有对象的最大生命周期
• 通过JOIN条件n.num < d.lifespan，为每个原始行生成lifespan条数据，年份为原始年份加上数字表的num值

执行结果 #

两种方法最终都会输出符合要求的数据：

year   | lifespan
-------+----------
 2000  |        3
 2001  |        3
 2002  |        3
 2012  |        2
 2013  |        2
 2021  |        1

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