cumulative table designダウンロード - cumulative table designソースコードのダウンロード

cumulative table design

その他のソースコード

ダウンロード

累積テーブル設計

累積テーブル設計は、すべてのデータエンジニアが知っておくべき非常に強力なデータエンジニアリングツールです。

この設計により、任意の長さ (最大数千日) の時間枠で効率的な分析を提供できるテーブルが生成されます。

このパターンの高レベルのパイプライン設計の図は次のとおりです。

累積テーブル図

私たちは最初に、エンティティが何であれ、その基本となる毎日のメトリクステーブルを作成します。このデータは、上流にあるあらゆるイベントソースから派生します。

日次メトリクスを取得したら、昨日の累積テーブルと今日の日次データをFULL OUTER JOIN 、各ユーザーのメトリクス配列を構築します。これにより、すべてをスキャンすることなく、新しい履歴を取り込むことができます。 (パフォーマンスが大幅に向上します)

これらのメトリック配列を使用すると、 ARRAY_SUMなどを使用してすべてのユーザーの履歴に関するクエリに簡単に答えることができ、配列で許可される時間枠に関係なく、必要なメトリックを計算できます。

分析の期間が長くなるほど、このパターンはより重要になります。

累積されたユーザーアクティビティとエンゲージメントの例

すべてのクエリ構文は Presto/Trino 構文と関数を使用します。この例は、他の SQL バリアントに合わせて変更する必要があります。
日付を使用します。
2022 年 1 月 1 日、 Airflow 用語では今日は{{ ds }}となります
2021-12-31 は、Airflow テンプレート用語では昨日と同じであり、これは{{ yesterday_ds}}です

この例では、日次、週次、月次のアクティブユーザーと、ユーザーの「いいね！」、コメント、シェアを計算するためのこの設計を構築する方法を検討します。

この場合のソーステーブルはeventsです。

ユーザーは、その日にイベントを生成した場合、その日にアクティブになります。
テーブルには、 comment 、 share 、またはview like event_typeがあります。

これに対する解決策は次のようなパイプラインを実行することだと考えたくなります。

    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 日分のイベントデータをスキャンしていることです。かなり無駄ですが、シンプルなパイプラインです。イベントデータを 1 回スキャンするだけで済み、過去 29 日間の結果と組み合わせる方法があるはずですよね。データサイエンティストがデータをクエリして、過去 N 日間にユーザーが実行したアクションの数を簡単に知ることができるデータ構造を作成できますか?

このデザインは非常にシンプルで、ステップは 3 つだけです。

デイリーテーブルステップ

このステップでは、今日のイベントだけを集計して、誰が毎日アクティブであるかを確認します。テーブルスキーマはここにあります
このクエリは非常にシンプルで簡単です。
- GROUP BY user_id 、イベントがある場合は毎日のアクティブとしてカウントします。
- いくつかのCOUNT(CASE WHEN event_type = 'like' THEN 1 END)ステートメントを追加して、毎日のいいね、コメント、シェアの数も把握します。

累積ステップ

このステップのテーブルスキーマはここにあります
このステップのクエリははるかに複雑です。ここにあります
- このステップでは、日次テーブルから今日のデータを取得し、累積テーブルから昨日のデータを取得します。
- これら 2 つのデータセットをtoday.user_id = yesterday.user_idでFULL OUTER JOIN 。
- ユーザーがまったく新しい場合、そのユーザーは昨日のデータには含まれません。また、ユーザーが今日アクティブでなかった場合、そのユーザーは今日のデータには含まれません。
- したがって、すべてのユーザーを追跡するには、 COALESCE(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では、配列サイズを 30 に制限しているため、月間アクティブ数が得られます。
  - CASE WHEN ARRAY_SUM(SLICE(activity_array, 1, 7)) > 0 THEN 1 ELSE 0 END配列の最初の 7 要素(つまり、最後の 7 日間)のみをチェックするため、毎週のアクティブが得られます。
- 過去 7 日間のいいね、コメント、シェアの数を合計するのも簡単です
  - ARRAY_SUM(SLICE(like_array, 1, 7)) as num_likes_7dこのユーザーが過去 7 日間に行った「いいね！」の数を示します。
  - ARRAY_SUM(like_array) as num_likes_30d配列がそのサイズに固定されているため、このユーザーが過去 30 日間に行った「いいね！」の数を示します。