Redshift中使用随机数据创建临时表失败，求解决办法

问题场景 #

执行以下查询可正常返回结果：

select * from
    (select now()::date - generate_series(0, 29) as date,
       CAST(RANDOM() * 10 AS INT)  as nmb) a;

但执行创建临时表的语句时触发错误：

create temp table nmbr_dates as
    (select GETDATE() - generate_series(0, 29) as date,
       CAST(RANDOM() * 10 AS INT)  as nmb);

错误信息：[0A000] ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables

简便解决方法 #

方法1：用子查询包装生成序列逻辑 #

将generate_series的生成逻辑放在子查询中，再关联计算日期和随机数：

CREATE TEMP TABLE nmbr_dates AS
SELECT (GETDATE()::DATE - gs.days) AS date,
       CAST(RANDOM() * 10 AS INT) AS nmb
FROM (SELECT generate_series(0, 29) AS days) gs;

方法2：先建空表再插入数据 #

先创建结构匹配的空临时表，再通过INSERT语句插入数据：

-- 先创建空表
CREATE TEMP TABLE nmbr_dates (date DATE, nmb INT);
-- 插入数据
INSERT INTO nmbr_dates
SELECT GETDATE()::DATE - generate_series(0, 29) AS date,
       CAST(RANDOM() * 10 AS INT) AS nmb;

原因说明 #

Redshift的CREATE TABLE AS (CTAS)操作采用并行执行策略，generate_series（行生成函数）和RANDOM()（节点级随机函数）在顶层查询中使用时，会因跨节点的函数分布逻辑冲突触发报错。通过子查询包装序列生成逻辑，或是改用INSERT方式，能让查询先在单个节点完成序列生成，再执行后续计算，规避分布冲突问题。

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