我在 Redshift 中有许多(1000 万行)事实表,每个都有一个自然键 memberid
,每个都有一个列 timestamp
。假设我有三个表:transactions
、messages
、app_opens
,transactions
看起来像这样(所有其他表都具有类似的结构):
memberid | 收入 | 时间戳 |
---|---|---|
374893978 | 3.99 | 2021-02-08 18:34:01 |
374893943 | 7.99 | 2021-02-08 19:34:01 |
我的目标是创建一个像这样的每日 per-memberid 聚合表,每个 memberid 和日期都有一行:
memberid | 日期 | daily_revenue | daily_app_opens | daily_messages |
---|---|---|---|---|
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,NULL AS daily_revenue,count(*) daily_app_opens,NULL AS daily_messages
FROM app_opens
GROUP BY 1,count(*) daily_messages
FROM messages
GROUP BY 1,2
)
GROUP BY memberid,date
这工作正常并产生预期的输出,但我想知道这是否是执行此类查询的最有效方法。我也使用 FULL OUTER JOIN
代替 UNION ALL
,但性能基本相同。
在 Redshift 中实现这一目标的最有效方法是什么?