[Analytic Block]派生テーブルのパターン

mariko_nishimi · 10-21-2019 01:30 AM

🇺🇸 [Analytic Block] Derived Tables Pattern in English 🇺🇸

このブロックについて

多くの場合、私達のデータベースは分析のために最適化された情報を保持していません。必要なものを計算するための非効率なディメンションやメジャーを記述するのではなく、派生テーブルを使用すると、私達のデータベースの中に直接一時テーブルを作成できます。派生テーブルを使用すると、中間集計を必要とする分析を実行し、複雑なクエリのクエリパフォーマンスを向上させ、データをクレンジングまたは正規化するためのパターンを作成できます。

簡単に言えば、派生テーブルは、データベースに存在しない新しいテーブルを作成するLookerの方法です。 結果セットが派生テーブルそのものになるSQLクエリを提供することによってこれを定義します。

Lookerは派生テーブルに一時的 か 永続的の２つのオプションを提供しています。一時的な派生テーブルはデータベースに保存されません。Lookerは共通のテーブル式を利用するか、この派生テーブルが参照されるたびに毎回一時テーブルを生成します。あるいは、永続派生テーブル（PDTs)はディスクに書き込まれ、選択した頻度で更新されます。このプロセスを行うために、データベース内のスクラッチスキーマにLookerの書き込み権限を提供する必要があります。PDTはマテリアライズド・ビューによく似ています。

理想的なデータ型

すべてのどんなデータでも、派生テーブルの恩恵を受けることができます。この記事の終わり近くに、派生テーブルを活用するデザインパターンへのリンクがあります。データベースによっては、PDTは利用できない場合がありますので注意してください。ただし、Lookerでサポートされているすべてのダイアレクトでは、一時派生テーブルをサポートしています。

期待される出力

下記は、以下のコードで書かれている派生テーブルのExploreです。機能的には通常のビューと同じであることに注目してください。

Explore Data in Full Screen

使い方

標準的なEコマースの例を使い、同じユーザーによって複数の注文が入ってくる”orders”というテーブルがあるとします。各ユーザーによって行われた注文のサブセットに対して何らかの分析を行いたい場合、派生テーブルを活用して’user_id’でグループ化しながら注文データの詳細に対していくつかの集計を実行できます。以下に例を示します:

- view: user_order_facts
  derived_table:
    sql: |
      SELECT orders.user_id AS user_id                           
              , COUNT(DISTINCT order_id) as lifetime_orders
              , COUNT(*) AS lifetime_order_items
              , MIN(NULLIF(orders.created_at,0)) AS first_order
              , MAX(NULLIF(orders.created_at,0)) AS latest_order
              , DATEDIFF(MAX(NULLIF(orders.created_at,0)),MIN(NULLIF(orders.created_at,0))) AS days_as_customer
              , DATEDIFF(CURDATE(),MAX(NULLIF(orders.created_at,0))) AS days_since_purchase
              , COUNT(DISTINCT CONCAT(MONTH(NULLIF(orders.created_at,0))),YEAR(NULLIF(orders.created_at,0))) AS number_of_distinct_months_with_orders
              , SUM(CASE WHEN orders.created_at <= users.created_at + INTERVAL 24 HOUR THEN order_items.sale_price ELSE 0 END) AS 24_hour_revenue
              , SUM(CASE WHEN orders.created_at <= users.created_at + INTERVAL 30 DAY THEN order_items.sale_price ELSE 0 END) AS 30_day_revenue
              , SUM(CASE WHEN orders.created_at <= users.created_at + INTERVAL 90 DAY THEN order_items.sale_price ELSE 0 END) AS 90_day_revenue
              , SUM(CASE WHEN orders.created_at <= users.created_at + INTERVAL 365 DAY THEN order_items.sale_price ELSE 0 END) AS 365_day_revenue
            FROM order_items 
            LEFT JOIN orders ON orders.id = order_items.order_id
            LEFT JOIN users ON users.id = orders.user_id
            GROUP BY user_id
    persist_for: 2 hours
    indexes: [user_id, lifetime_orders]

派生テーブルのいくつかのフィールドを見てみましょう:

user_id : グループ化する列。この派生テーブルの各行は、一意のuser_idになります。
lifetime_orders : count(distinct order_id) 各ユーザーごとにすべての一意の注文をカウント。
first_order : 各ユーザーの最も古い注文の日付（可能な場合）
latest_order : 各ユーザーの最も最近の注文の日付（可能な場合）
days_as_customer : 各ユーザーの最新の注文と最も古い注文間の差
days_since_purchase : 今日の日付と各ユーザーの最新の注文日の差
number_of_distinct_months_with_orders : 各ユーザーが注文した個別の月数
xxx_day_revenue : 指定された時間枠での売上収益

Lookerはこのクエリの結果をどのように生成するかがわかったので、これらのフィールドを直接参照するか、またはLooker内でこれらのフィールドを操作することができます。以下は、派生テーブルからフィールドを参照する２つのディメンションです。１つは層構造を作成し、もう１つは不等式評価を実行します。

  - dimension: lifetime_number_of_orders_tier
    type: tier
    style: integer
    tiers: [0,1,2,3,5,10]
    sql: ${lifetime_orders}

  - dimension: repeat_customer 
    type: yesno
    sql: ${lifetime_orders} > 1

これらは、派生テーブルからデータを操作する２つの簡単な例です。派生テーブルメカニズムを利用して最初に結果が取得されているため、複雑な計算を実行できるようになりました。

最後に、派生テーブルのいくつかのパラメーターについて説明します。

データ鮮度: PDTはデータベースのスクラッチスキーマに格納されるため、最新のデータセットに基づいていない可能性があります。これを、persist_for や sql_trigger_value などのパラメータで制御して、再クエリの頻度と派生テーブルによって生成されるデータの鮮度を管理することができます。上記の例ではトリガー値2 hoursを指定しており、これはデータを更新するために２時間毎に元になるクエリを再送信するようにLookerに指示しています。
データ分散: Lookerはデータベースに応じて、ユーザー指定の indexes またはsort_key/dist_keyによってPDTの結果を分散できます。上記の例では、user_id及びlifetime_ordersの分散インデックスを指定しています。これは、クエリの「GROUP_BY」ステートメントが「user_id」の一意性を確立し、lifeteme_orders で頻繁にフィルタリングするため最適な選択です。適切にインデックス付されたPDTは、パフォーマンスの高いアクセスをもたらします。

試してみましょう！

users_orders_facts の例の場合、派生テーブルのビュー定義はこのモデルにあります。 here . しかし、おそらくより実用的なアプリケーションは、別の設計パターンで活用された派生テーブルを見てみることです。派生テーブルを利用するこれらの他のLookerブロックを参照ください。:

追加情報

派生テーブルについて覚えておくべき重要事項を次に示します:

派生テーブルはLookML内でビューとして定義されているため、他のビューと同様にモデルで利用できます。派生テーブルを他のビューに簡単に join でき、列を制限し, 必須フィルターの設定ができます。
永続的派生テーブルは管理パネルの「PDTs」カテゴリで管理できます。ここで、インスタンス全体のすべてのPDTに関する高レベルの情報を確認できます。
派生テーブルは templated filters を活用できます。これは、派生テーブルの基なるクエリを動的に操作して、パラメータまたは条件に基づいた WHERE 句を変更できる機能です。