派生テーブルは、既存のテーブルやビューに対するクエリ結果を、実際のテーブルのように使用できるようにする機能です。
例えば、「ユーザーごとの初回購入日と最終購入日を予め計算しておき、他のデータと組み合わせたい」といったケース、ありますよね。

データベースに慣れた方は、サブクエリやCTEをイメージしていただけると分かりやすいかと思います。
派生テーブルは、主に下記のようなケースで利用します

• 集計値を基にしたディメンションの作成
• ウィンドウ関数の利用
• 事前集計・フィルタをすることでテーブル結合時の計算コスト削減

Lookerの派生テーブルには、SQLで記述する「SQL派生テーブル」と、主にLookMLで記述する「ネイティブ派生テーブル」の2つがあります。

SQL派生テーブル

• SQLを直接記述するため、複雑なクエリを自由に書ける
• 既存のSQL資産がある場合に、流用しやすい
• SQLに慣れたエンジニアには直感的に利用できる

view: user_order_summary_sql {

  derived_table: {

    sql: 

      SELECT 

        user_id,

        COUNT(DISTINCT order_id) as total_orders,

        SUM(amount) as total_spent,

        MAX(created_at) as last_order_date

      FROM orders

      GROUP BY user_id ;;

  }

}

ネイティブ派生テーブル

• LookMLのExploreを基に構築するため、既存のビジネスロジックを再利用できる
• 基となるExploreの変更が自動的に反映されるため、保守性が高い
• さまざまなオプションにより、柔軟なクエリを動的に構築できる

view: user_order_summary_native {

  derived_table: {

    explore_source: orders {

      column: user_id {}

      column: total_orders { 

        field: orders.count 

      }

      column: total_spent { 

        field: orders.total_amount 

      }

      column: last_order_date { 

        field: orders.max_created_date 

      }

    }

  }

ネイティブ派生テーブルのメリットを詳しく見ていきましょう。

1. 既存のビジネスロジックの再利用

ネイティブ派生テーブルは、すでに定義済みのviewのフィールドをそのまま活用できます。
これにより、複雑な計算ロジックやビジネスルールを重複して記述する必要がありません。
以下のようなviewがあるとします。(一部省略しています)

view: orders {

  dimension: order_id {

    primary_key: yes

  }

  

  dimension: status {

    type: string

  }

  

  measure: net_revenue {

    type: sum

    sql: ${amount} * (1 - ${discount_rate}) * (1 - ${tax_rate}) ;;

    filters: [status: "completed, shipped"]

  }

  

  measure: average_days_to_ship {

    type: average

    sql: DATEDIFF(day, ${created_date}, ${shipped_date}) ;;

    value_format_name: decimal_1

  }

}

上記を利用したネイティブ派生テーブルがこちらです。
net_revenueの箇所は、ネイティブ派生テーブル側ではシンプルな記述ですが、定義済みの計算式やフィルタが自動的に反映されます。
そのため、ビジネスロジックの変更に伴って計算式やフィルタに変更する際も、ネイティブ派生テーブル側のLookMLを変更する必要がありません。

view: summary {

  derived_table: {

    explore_source: orders {

      column: net_revenue {}     # 複雑なロジックやフィルタ条件も含めて、そのまま再利用

      column: average_days_to_ship {}

    }

  }

}

2. 保守性の高さ

分かりやすい例として、access_filterによるフィルタの動作を見てみましょう。
以下のようなExploreがあるとします。

explore: sales_transactions {

  access_filter: {

    field: category

    user_attribute: user_attribute_category

  }

}

このExploreを利用して、下記のような簡単なネイティブ派生テーブルを作ります。

view: ndt_sales_transactions {

  derived_table: {

    explore_source: sales_transactions {

      column: status {}

      column: count {}

    }

  }

  dimension: status {}

  dimension: count {}

}

このネイティブ派生テーブルをExplore化して、フィールドを選択すると次のようなクエリが得られます。

赤枠で囲った部分が、基となったExploreで定義していたaccess_filterによるものです。
このように、ネイティブ派生テーブルでは、既存のExploreへの設定を再定義することなく利用できます。

3. オプションによる柔軟なクエリ

SQL派生テーブルとの大きな違いとして、bind_filtersの存在があります。
bind_filtersは、ネイティブ派生テーブルをExplore上で他のviewと結合して使う際に、「ユーザーがExploreで指定したフィルタ条件を、ネイティブ派生テーブルにも引き継がせる」機能です。
少し分かりづらいと思いますので、実際の例を見ていきましょう。

下記のExploreを基に、ネイティブ派生テーブルを構成します。

explore: sales_transactions {}

view: ndt_sales_transactions {

  derived_table: {

    explore_source: sales_transactions {

      column: customer_id {}

      column: status {}

      column: total_amount {}

    }

  }

  dimension: customer_id {}

  dimension: status {}

  dimension: total_amount {}

}

ネイティブ派生テーブルを結合しておきます。

explore: sales_transactions {

  join: ndt_sales_transactions {

    type: inner

    sql_on: ${sales_transactions.customer_id} = ${ndt_sales_transactions.customer_id} ;;

    relationship: many_to_one

  }

}

こちらを基にExploreでフィルタを設定してみると、下記のようなクエリが得られます。

ネイティブ派生テーブルによって構成されているWITH句内ではフィルタが掛かっていないことが確認できます。
次に、ネイティブ派生テーブル側にbind_filtersを追加します。

view: ndt_sales_transactions {

  derived_table: {

    explore_source: sales_transactions {

      column: customer_id {}

      column: status {}

      column: total_amount {}

      bind_filters: {

        to_field: sales_transactions.status

        from_field: ndt_sales_transactions.status

      }

    }

  }

  dimension: customer_id {}

  dimension: status {}

  dimension: total_amount {}

}

再度クエリを見てみましょう。

WITH句内にもフィルタが適用されていますね。
今回は、かなりシンプルな例で、bind_filtersの動作をご紹介いたしましたが、テンプレートフィルタなども活用することで、より柔軟な構成が可能です。
特に、BigQueryではbind_filtersとテンプレートフィルタを組み合わせて、パーティション分割テーブルのスキャン範囲を動的に制御するといったことが実現できます。

ネイティブ派生テーブルは、Lookerの価値を最大限に高める強力な機能です。
既存のLookMLを活用しながら、パフォーマンスや保守性を両立させ、より効率的なデータモデリングを実現できます。
ネイティブ派生テーブルを活用して、Lookerの魅力を存分に引き出していきましょう！
参考URL:
https://cloud.google.com/looker/docs/derived-tables?hl=ja
https://cloud.google.com/looker/docs/creating-ndts?hl=ja

システムサポートでは、Google CloudやLookerの導入や活用を支援しております。
Lookerを導入したい・導入したけど使いこなせていない…という方は、お気軽にご相談ください！