sql - 在 Redshift 中高效地连接和聚合大量事实表

Question

我在 Redshift 中有许多（10M+ 行）事实表，每个都有一个自然键memberid，每个都有一个 column timestamp。假设我有三个表：transactions, messages, app_opens,transactions看起来像这样（所有其他表都具有相似的结构）：

会员ID	收入	时间戳
374893978	3.99	2021-02-08 18:34:01
374893943	7.99	2021-02-08 19:34:01

我的目标是创建一个看起来像这样的每日 per-memberid 聚合表，每个 memberid 和日期都有一行：

会员ID	日期	每日收入	daily_app_opens	每日消息
374893978	2021-02-08	4.95	31	45
374893943	2021-02-08	7.89	23	7

我目前为此使用的 SQL 如下，其中涉及合并单独的子查询：

SELECT memberid,
       date,
       max(NVL(daily_revenue,0)) daily_revenue,
       max(NVL(daily_app_opens,0)) daily_app_opens,
       max(NVL(daily_messages,0)) daily_messages
FROM 
 (
 SELECT memberid,
        trunc(timestamp) as date,
        sum(revenue) daily_revenue,
        NULL AS daily_app_opens,
        NULL AS daily_messages
 FROM transactions
 GROUP BY 1,2

 UNION ALL

 SELECT memberid,
        trunc(timestamp) as date,
        NULL AS daily_revenue,
        count(*) daily_app_opens,
        NULL AS daily_messages
 FROM app_opens
 GROUP BY 1,2

 UNION ALL

 SELECT memberid,
        trunc(timestamp) as date,
        NULL AS daily_revenue,
        NULL AS daily_app_opens,
        count(*) daily_messages
 FROM messages
 GROUP BY 1,2
)
GROUP BY memberid, date

这可以正常工作并产生预期的输出，但我想知道这是否是执行此类查询的最有效方式。我也使用FULL OUTER JOIN了 in place of UNION ALL，但性能基本相同。

在 Redshift 中实现这一目标的最有效方法是什么？

标签： sqldatabaseamazon-redshiftdata-warehouse

Answer 1

查看 EXPLAIN 计划会有所帮助，因为它可以让我们了解查询中最昂贵的部分是什么。基于对 SQL 的快速阅读，它看起来相当不错。扫描事实表的成本可能很有意义，但这是您必须承受的成本。如果您可以使用 where 子句限制读取的数据量，这可以减少，但这样做可能无法满足您的需求。

您应该查看的一个地方是这些表的分布。由于您是按 accountid 分组的，因此将其作为分发键将使此过程更快。分组需要将具有相同 accountid 值的行放在一起，对这些值进行分配将大大减少集群内的网络流量。

在大数据大小和其他所有优化的情况下，我希望 UNION ALL 能够执行 FULL OUTER JOIN 但这将取决于许多因素（例如 accountid 聚合减少了多少数据大小）。10M 行在 Redshift 方面并不是很大（我在一个最小集群上有 160M 行宽数据），所以我认为在这些大小的这些计划之间不会有太大差异。