如何在Redshift中用CTE生成全量日期时分维度表？

问题背景 #

需要生成包含所有日期（来自DATE_TABLE）、每个日期的0-23小时、每个小时的0-59分钟的维度表，无法使用表函数，且generate_series在关联查询时触发“Function not supported”报错。

可行方案：递归CTE + 笛卡尔关联 #

Redshift支持递归CTE，我们可以分别递归生成小时序列和分钟序列，再与DATE_TABLE做笛卡尔积，得到完整的时间维度数据。

1. 生成分钟序列的递归CTE

WITH RECURSIVE minute_cte AS (
    SELECT 0 AS minute
    UNION ALL
    SELECT minute + 1
    FROM minute_cte
    WHERE minute < 59
)
SELECT * FROM minute_cte;

2. 生成小时序列的递归CTE

WITH RECURSIVE hour_cte AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1
    FROM hour_cte
    WHERE hour < 23
)
SELECT * FROM hour_cte;

3. 关联日期表生成完整维度表

将DATE_TABLE、小时CTE、分钟CTE进行笛卡尔关联，得到所有日期的每小时每分钟数据：

WITH RECURSIVE minute_cte AS (
    SELECT 0 AS minute
    UNION ALL
    SELECT minute + 1
    FROM minute_cte
    WHERE minute < 59
),
hour_cte AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1
    FROM hour_cte
    WHERE hour < 23
)
SELECT
    dt.date_dt AS "Date",
    h.hour AS "Hour",
    m.minute AS "Minute"
FROM DATE_TABLE dt
CROSS JOIN hour_cte h
CROSS JOIN minute_cte m
-- 如需限制日期范围，可添加WHERE条件
-- WHERE dt.date_dt BETWEEN '2001-01-01' AND '2001-12-31'
ORDER BY dt.date_dt, h.hour, m.minute;

原递归CTE问题解析 #

你之前的递归CTE逻辑有误：将小时递归和分钟序列直接关联，会导致每个分钟值重复24次，但没有正确拆分小时、分钟的独立生成逻辑，最终无法和日期表正确关联。上述方案通过拆分独立的小时、分钟序列，再与日期表做笛卡尔积，逻辑清晰且符合Redshift语法要求。

替代方案：使用数字辅助表（如果已有） #

如果库中存在包含0-59数字的辅助表（比如numbers表），可直接替代递归CTE，写法更简洁：

SELECT
    dt.date_dt AS "Date",
    n1.num AS "Hour",
    n2.num AS "Minute"
FROM DATE_TABLE dt
CROSS JOIN (SELECT num FROM numbers WHERE num BETWEEN 0 AND 23) n1
CROSS JOIN (SELECT num FROM numbers WHERE num BETWEEN 0 AND 59) n2
ORDER BY dt.date_dt, n1.num, n2.num;

内容的提问来源于stack exchange，提问作者Phil C.