Redshift中使用generate_series生成日期关联事件表报错问询

Redshift里generate_series函数没法直接和表关联使用，所以得换个方式实现需求，下面给两种实用方案：

方案一：用递归CTE生成日期序列 #

如果没有现成的日期维度表，可以用递归CTE生成所需的连续日期范围，再和事件表关联：

WITH date_series AS (
    -- 自动取tableA里事件的最早开始日期和最晚结束日期作为序列范围
    SELECT MIN(start_date) AS date_val FROM tableA
    UNION ALL
    SELECT DATEADD(day, 1, date_val)
    FROM date_series
    WHERE date_val < (SELECT MAX(end_date) FROM tableA)
)
SELECT ds.date_val, ta.event_id
FROM date_series ds
JOIN tableA ta ON ds.date_val BETWEEN ta.start_date AND ta.end_date
ORDER BY ds.date_val, ta.event_id;

要是你需要固定日期范围（比如指定2023全年），可以把递归的起始和结束日期改成固定值：

WITH date_series AS (
    SELECT '2023-01-01'::DATE AS date_val
    UNION ALL
    SELECT DATEADD(day, 1, date_val)
    FROM date_series
    WHERE date_val < '2023-12-31'::DATE
)
SELECT ds.date_val, ta.event_id
FROM date_series ds
JOIN tableA ta ON ds.date_val BETWEEN ta.start_date AND ta.end_date
ORDER BY ds.date_val, ta.event_id;

注意：如果日期跨度特别大（比如好几年），递归CTE可能会有性能瓶颈，这时候优先用下面的方案。

方案二：用预定义的日期维度表 #

数据仓库里一般都会提前建一个日期维度表（比如叫dim_date，包含连续的date字段），直接用它关联效率更高：

SELECT dd.date, ta.event_id
FROM dim_date dd
JOIN tableA ta ON dd.date BETWEEN ta.start_date AND ta.end_date
-- 过滤出tableA涉及的日期范围，避免全表扫描
WHERE dd.date >= (SELECT MIN(start_date) FROM tableA)
  AND dd.date <= (SELECT MAX(end_date) FROM tableA)
ORDER BY dd.date, ta.event_id;

如果还没建日期维度表，可以一次性生成好未来几年的日期，以后复用就行。

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