累积表设计是所有数据工程师都应该知道的极其强大的数据工程工具。
此设计生成的表格可以对任意大（最多数千天）的时间范围进行有效分析
以下是此模式的高级管道设计图：
我们最初构建了每日指标表，该表与我们的实体无关。该数据源自我们上游的任何事件源。
获得每日指标后，我们将昨天的累积表与今天的每日数据FULL OUTER JOIN ，并为每个用户构建指标数组。这使我们能够引入新的历史记录，而无需扫描所有历史记录。 （性能大幅提升）
这些指标数组使我们能够轻松回答有关所有用户历史记录的查询，使用ARRAY_SUM之类的东西来计算我们在数组允许的任何时间范围内想要的任何指标。
分析的时间范围越长，这种模式就变得越关键！
所有查询语法均使用 Presto/Trino 语法和函数。该示例需要针对其他 SQL 变体进行修改！
我们将使用日期：
- 2022 年 1 月 1 日至今，用 Airflow 术语来说是
{{ ds }}- 2021 年 12 月 31 日是 Airflow 模板术语中的昨天，这是
{{ yesterday_ds}}
在此示例中，我们将研究如何构建此设计来计算每日、每周和每月的活跃用户以及用户的点赞、评论和分享。
在本例中，我们的源表是events 。
event_type ， like 、 comment 、 share或view人们很容易认为解决这个问题的方法是运行类似的管道
SELECT
COUNT(DISTINCT user_id) as num_monthly_active_users,
COUNT(CASE WHEN event_type = 'like' THEN 1 END) as num_likes_30d,
COUNT(CASE WHEN event_type = 'comment' THEN 1 END) as num_comments_30d,
COUNT(CASE WHEN event_type = 'share' THEN 1 END) as num_shares_30d,
...
FROM events
WHERE event_date BETWEEN DATE_SUB('2022-01-01', 30), AND '2022-01-01'
问题是我们每天都会扫描 30 天的事件数据来生成这些数字。一个相当浪费但简单的管道。应该有一种方法，我们只需扫描一次事件数据，并结合过去 29 天的结果，对吗？我们能否创建一个数据结构，让数据科学家可以查询我们的数据并轻松了解用户在过去 N 天内执行的操作数量？
这个设计非常简单，只有 3 个步骤：
GROUP BY user_id ，然后将其计为每日活跃（如果有任何事件）COUNT(CASE WHEN event_type = 'like' THEN 1 END)语句来计算每日点赞、评论和分享的数量today.user_id = yesterday.user_id上对这两个数据集FULL OUTER JOINCOALESCE(today.user_id, yesterday.user_id) as user_id来跟踪所有用户activity_array列。我们只希望activity_array存储最近30天的数据CARDINALITY(activity_array) < 30来了解我们是否可以将今天的值添加到数组的前面，或者我们是否需要在将今天的值添加到前面之前从数组的末尾切掉一个元素COALESCE(t.is_active_today, 0)将零值放入数组中activity_array非常相似的模式，但也适用于点赞、评论和分享！CASE WHEN ARRAY_SUM(activity_array) > 0 THEN 1 ELSE 0 END为我们提供每月活动，因为我们将数组大小限制为 30CASE WHEN ARRAY_SUM(SLICE(activity_array, 1, 7)) > 0 THEN 1 ELSE 0 END为我们提供每周活动，因为我们只检查数组的前 7 个元素（即最后 7 天）ARRAY_SUM(SLICE(like_array, 1, 7)) as num_likes_7d我们提供了该用户在过去 7 天内点赞的数量ARRAY_SUM(like_array) as num_likes_30d为我们提供了自数组固定为该大小以来该用户在过去 30 天内点赞的数量depends_on_past: True
