sql - Redshift：将每个查询的结果列链接到一个表中

Question

我在生产中有大约 50 个查询。每个查询都返回两列 - 用户 ID 和一些计算列

要求是所有这些查询都应该通过 userid 将计算列加入结果表中。

例如：-

查询 1st 返回包含数据的以下列：-

-------------
userid | c1 |
-------------

同样查询第二个：-

-------------
userid | c2 |
-------------

查询第 50 个：-

-------------
userid | c50 |
-------------

现在，每个查询的计算列输出应附加到生成的主表中，例如：-

--------------------------------------------------
userid | c1 | c2 | c3 |..............| c49 | c50 |
--------------------------------------------------

现在最天真的方法是将每个查询的输出写入单独的临时表，并将用户 ID 上的每个表连接到单个结果表。这个问题我不想通过创建临时表来污染命名空间到这种程度，因为这是红移并且表真的很大，提交每个临时表本身将在存储方面产生重大成本。

我认为下一个解决方案是使用“with”CTE：-

insert into Resultant
with rel1 as (select userid, c1 from t1 ),
rel2 as (select userid,c2 from t2 ),
.
.
rel50 as (select userid, c50 from t50)
select * from rel1 inner join rel2 on rel1.userid = rel2.userid 
inner join rel3 on rel2.userid = rel3.userid
...
...
inner join rel50 on rel49.userid = rel50.userid

我不知道这个解决方案的优化程度如何。Redshift文档提到：-

在可能的情况下，多次引用的 WITH 子句子查询被优化为公共子表达式；也就是说，可以评估一次 WITH 子查询并重用其结果。

另一种方法可能是使用 50 个更新连接：-

update resultant set resultant.col1 = (select Q.col1 from ("inner query that generates userid and col1) Q inner join resultant on resultant.userid = Q.userid );
update resultant set resultant.col2 = (select Q.col2 from ("inner query that generates userid and col1) Q inner join resultant on resultant.userid = Q.userid );
update resultant set resultant.col50 = (select Q.col50 from ("inner query that generates userid and col1) Q inner join resultant on resultant.userid = Q.userid );

这种方法会产生更多成本吗？由于 Redshift 是基于列的，我希望它不必更新整行。

有没有办法在存储过程中执行此操作，这将比这两种方法更优化？理想情况下，如果绝对没有必要，我想避免存储过程。每个查询返回大约 50GB 的结果集。

标签： sqlstored-proceduresamazon-redshiftcommon-table-expression

Answer 1

有趣的问题。绝对不要走 UPDATE 路径 - Redshift 是柱状的，这会使表格变得一团糟。

加入会起作用，但我也不认为它会是理想的。您所说的 JOIN 数量可能会与查询中的 Redshift 最大独立部分数相冲突。您可以通过使用临时表来解决这个问题。我仍然认为这不会是出色的表现。如果数据很小，这可能是可以接受的。

我会考虑通过使用查询标识符对查询结果进行联合处理。然后在外部选择中使用 GROUP BY 构建宽格式。这会创建最少数量的新表结构并使用更快的分组方法（比连接）。如果数据量很大，则为 UNION 结果创建一个具有 userid 分布键的临时表是有意义的。这将允许 GROUP BY 操作运行切片本地与需要重新分配数据。制作临时表的费用只有在数据量大的情况下才会抵消。

UNION 输出将类似于：

userid | queryid | value

然后是这样的查询：

select userid, min(decode(queryid, 1, value)) as c1, min(decode(queryid, 2, value)) as c2, ...
from union_data
group by userid;

我希望这比您概述的任何一种方法都快，但可能还有更好的方法。