如何在Redshift中高效生成日期序列

背景 #

PostgreSQL原生支持generate_series()函数快速生成日期序列，但Redshift中的该函数仅允许在主节点运行，在临时表中使用或关联查询时会触发报错。

现有方案说明 #

你当前使用的是依赖任意表生成行号再推导日期的方案，可实现生成当前时间往前推200个月的月度日期序列，代码如下：

SELECT
  DATEADD('month', -n, DATE_TRUNC('month', add_months(CURRENT_DATE,12))) AS gdate
FROM (SELECT ROW_NUMBER() OVER () AS n FROM one_table LIMIT 200) n

运行结果示例：

更高效稳定的替代方案 #

以下两种方案不需要依赖额外的业务物理表，兼容性和执行效率更优：

方案1：递归CTE实现（适合短序列，如<1000个日期点） #

直接通过递归生成数字序列再推导日期，无额外表依赖：

WITH RECURSIVE date_series AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM date_series WHERE n < 200
)
SELECT
    DATEADD('month', -n, DATE_TRUNC('month', add_months(CURRENT_DATE, 12))) AS gdate
FROM date_series

注：Redshift默认递归CTE深度上限为1000，若需要生成更长序列可调整max_recursion_depth参数，或使用方案2。

方案2：笛卡尔积生成数字序列（适合长序列，无递归深度限制） #

通过少量常量的笛卡尔积快速生成足够行数，性能稳定且无长度限制：

WITH num_series AS (
    SELECT ROW_NUMBER() OVER () - 1 AS n
    FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) t1(n)
    CROSS JOIN (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) t2(n)
    CROSS JOIN (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) t3(n)
    LIMIT 200
)
SELECT
    DATEADD('month', -n, DATE_TRUNC('month', add_months(CURRENT_DATE, 12))) AS gdate
FROM num_series

两种方案都不会出现因业务表数据量不足导致序列长度不够的问题，同时兼容临时表存储和后续关联查询场景，执行效率优于原依赖业务表的实现。

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