LinkedIn
Twitter
※ 本投稿はLooker Advent Caledar 2021 5日目の記事です。
派生テーブル
LookerにはNative Derived Table（ネイティブ派生テーブル）という強力な機能があります。これを活用すると、わざわざ新規にSQLを書かなくても、既存のExploreを使うことで、かなり複雑なケースにも対応した派生クエリーを書くことができます。
一方で、SQLとは異なりLookMLで記述する必要があるため、どうしてもSQLの方が書きやすいということで敬遠されることがあると感じています。そこで、ネイティブ派生テーブルがどのように利用されると良いか、今日は単純化した例を用いて説明させていただこうと思います。
ネイティブ派生テーブルの活用
以下のような注文ビュー(order_items)を基底ビューとしたシンプルな注文Exploreで考えてみます。注文Viewに対して、ユーザー情報、在庫製品、製品のビューをそれぞれ結合しています。
explore: order_items {
join: users {
sql_on: ${order_items.user_id} = ${users.id} ;;
type: left_outer
relationship: many_to_one
}
join: inventory_items {
sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;
type: left_outer
relationship: many_to_one
}
join: products {
sql_on: ${inventory_items.product_id} = ${products.id} ;;
type: left_outer
relationship: many_to_one
}
}ここで、たとえばカテゴリーごとの集計値を取りたい場合、どのようにすれば良いでしょうか。Exploreを使えば以下のような結果が簡単に手に入ります。
Explore実行結果1
次に、カテゴリー及び個別のブランド別の集計が欲しい場合はどうでしょうか。この場合は、単純にブランドディメンションを追加すればOKです。
Explore実行結果2
次に、これらのブランドがそれぞれのカテゴリー内でどのくらいの割合を占めているのかを算出するためにはどうすれば良いでしょうか。
SQLで書くと次のようなイメージの分析です。
SELECT
products.category AS products_category,
products.brand AS products_brand,
COALESCE(SUM(order_items.sale_price ), 0) AS order_items_total_sale_price,
order_items_total_sale_price,
FROM `ecomm.order_items`
AS order_items
LEFT JOIN `ecomm.inventory_items`
AS inventory_items ON order_items.inventory_item_id = inventory_items.id
LEFT JOIN `ecomm.products`
AS products ON inventory_items.product_id = products.id
LEFT JOIN (
SELECT
products.category AS products_category,
COALESCE(SUM(order_items.sale_price ), 0) AS order_items_total_sale_price
FROM `ecomm.order_items`
AS order_items
LEFT JOIN `ecomm.inventory_items`
AS inventory_items ON order_items.inventory_item_id = inventory_items.id
LEFT JOIN `ecomm.products`
AS products ON inventory_items.product_id = products.id
GROUP BY
1) AS total_sale_price_by_product_category
ON products.category = total_sale_price_by_product_category.products_category
GROUP BY
1,2,4SQLにするとずいぶんややこしく見えますが、要するに、粒度の異なるクエリーを二つ発行して、カテゴリーでJOINしているだけです。
内側のクエリーでまず「カテゴリーごと」の集計を取得し、次に、外側のクエリーで「カテゴリー及びブランドごと」の集計を取得して、最後にカテゴリーをキーにしてJOINしているわけです。
これにより以下のように、各行でブランドごとの集計値とカテゴリーの集計値を取得することができますので、あとは割合を算出するメジャーを追加すればできそうです。
ではこれを実装しましょう。先ほど作ったSQLを元にSQL派生テーブルを作成すればOKなのですが、よくよく見ると、先ほどのExplore実行結果1と2は、自分で書いたSQLの中身とよく似ていることに気づくと思います。
要するに、今からやろうとしていることは、もともとあるExploreから作成できるクエリーになっています。わざわざSQLを再発明するのはDRY原則に背いている可能性がありますので、このようなケースではネイティブ派生テーブルを使うべきです。
このような時は、以下のようにします。まず、Explore実行結果2で、「Get LookML」をクリックします。
次に、「Derived Table」タブを開き、コードをコピーします。
プロジェクトに新たにViewを作成して、`order_by_category` という名前で保存します。この時、View名を上書きするのを忘れないでください。（デフォルトでは、add_a_unique_name_xxxという名称になっています）
view: order_by_category {
derived_table: {
explore_source: order_items {
column: category { field: products.category }
column: total_sale_price {}
}
}
dimension: category {}
dimension: total_sale_price {
value_format: "$#,##0.00"
type: number
}
}そしてこちらを、元のExploreにJOINさせてみてください。
explore: order_items {
join: users {
sql_on: ${order_items.user_id} = ${users.id} ;;
type: left_outer
relationship: many_to_one
}
join: inventory_items {
sql_on: ${order_items.inventory_item_id} = ${inventory_items.id} ;;
type: left_outer
relationship: many_to_one
}
join: products {
sql_on: ${inventory_items.product_id} = ${products.id} ;;
type: left_outer
relationship: many_to_one
}
join: order_by_category {
sql_on: ${order_by_category.category} = ${products.category} ;;
type: left_outer
relationship: many_to_one
}
}これで、カテゴリーごとの集計をJOINすることが可能になります。
実行されたSQLを確認していただくと、意図通りのSQLが発行されていることが確認できます。このように、ネイティブ派生テーブルを活用すれば、SQLを新たに作らなくてもかなりの応用ができることがわかると思います。
派生テーブルのディメンションをメジャー化
実際には、上記のカテゴリー集計値はまだDimensionの状態になっているので、このままではメジャーとして取り扱うことができていません。ですので、今度はこれをメジャーにしてみます。
メジャーにするためには、派生テーブルに以下のような変更を加えます。
view: order_by_category {
view_label: "Order Items"
derived_table: {
explore_source: order_items {
column: category { field: products.category }
column: sale_price { field: order_items.total_sale_price }
}
}
dimension: category {
primary_key: yes
hidden: yes
}
dimension: sale_price {
value_format: "$#,##0.00"
type: number
hidden: yes
}
measure: total_sale_price {
label: "total_sale_price_by_category"
type: sum
sql: ${sale_price} ;;
value_format_name: usd
}
}ポイントは以下の5つです。1,2,3,4のステップは必ずしも不要ですが、これをやっておくと、メジャーの名称をtotal_total_sale_priceなどのようにしなくて良いので、見通しが良くなります。
- view_labelをつける
- field パラメータを追加してフィールド名を編集
- PKを設定
- 集計メジャーから派生したディメンションのフィールド名を編集
- 新たに集計メジャーを作成
Exploreは次のようになります。目的のクエリーが発行できるようになりました。あとは、割合を算出するメジャーなどを作成すれば良いだけです。
実はこの時、ネイティブ派生テーブルで記述された部分は、実際にはJOINによってFan Out（結合によって行が増えてしまう事象）が発生していますので、SQLを自分で書くと重複した値を集計してしまうのですが、Lookerには対称集計（シンメトリックアグリゲーション）という機能が備わっているので、PKとrelationshipを適切に設定しておけば、この問題も解決されています。
以上見ていただいたように、集計粒度を変えたサブクエリーを元のクエリーにJOINするという、SQLの世界では一般的なプラクティスを、自然な形でLookMLのモデルに組み込むことができます。見ていただいたように、ここまでSQLをほとんど書いていません。
bind_filterの併用
分析する上では、ほとんどのケースで日付の絞り込みが必要になると思います。ネイティブ派生テーブルはもちろんこのようなケースも想定してあり、必要なLookMLパラメータとして `bind_filter` が準備されています。
たとえば、Exploreに日付フィルターを設定してクエリーすると、次のように動作します。フィルターは、結合した後に最後WHERE句が追加されますが、CTEとして表現されるネイティブ派生テーブル（カテゴリー集計を算出する部分）には適用されません。
この場合、カテゴリーの集計値は全期間を対象に集計されますが、Order_Itemsから取得する他の項目には年度の絞り込みが入ってしまいます。
これを防ぐには、Lookerのテンプレートフィルターを使用して、日付フィルターの内容を派生テーブル側にも渡してあげる必要があります。SQL派生テーブルでは、
{% condition date_filter %} order_items.created_at {% endcondition %}以上のように記述することでフィルターを渡すことができました。これと同じことがネイティブ派生テーブルでも機能としてサポートされています。NDTでは、テンプレートフィルターは次のように `bind_filter` パラメータを記述することで実装できます。
derived_table: {
explore_source: order_items {
column: category { field: products.category }
column: sale_price { field: order_items.total_sale_price }
bind_filters: {
to_field: order_items.created_date
from_field: order_items.created_date
}
}
}これにより、フィルターがバインドされ、CTE（ネイティブ派生テーブル）側のクエリーにもちゃんと日付フィルターが適用されるようになりました。
とはいえ、たとえばcreated_date以外の項目がフィルターに指定された場合はどうでしょうか。以下のように、性別をフィルターに加えてみるとどうなるかみてみましょう。
発行されるSQLは次のようになります。残念ながら`Gender` はフィルターとしてバインドされませんので、やはりカテゴリー集計値は男女問わず、全体を対象に集計されてしまいます。
bind_all_filter
では、フィルターをバインドするためには、ユースケースで想定されるパターンの全てに対して `bind_filter` を指定しておく必要があるのでしょうか？
もちろんそれも一つの考え方ですが、実は、今回の例のような「元のExploreにNDTを自己結合する」パターンに限って、 `bind_all_filter` という別の便利なパラメータが用意されています。このパラメータを設定しておくと、元のExploreで指定されたフィルターを全てNDT側にバインドしてくれます。
ただし、このパラメータは、NDTと元のExploreが同じである必要があるため、別のExploreから作成されたNDTでは適用できません（エラーになります）。
ユースケースによっては、サブクエリーにフィルターをかけたくない（例えばユーザーのLTVを算出したいが、それはExplore上で設定されたフィルター期間によらず全期間にわたって集計したいようなケース）場合は、 `bind_all_filter` ではなく、 `bind_filter` を使ったり、あえてテンプレートフィルターを設定しないなどの工夫もできます。
Derived_columnの活用
ここまででもかなり複雑なケースに対応できることがお分かりいただけたかと思います。
さらに複雑なユースケース、たとえば、カテゴリーのランキングなどを取りたい場合はどうでしょうか？ SQLであればRANK()などのウィンドウ関数を使えばできそうですが、ネイティブ派生テーブルにはこのように書くことができます。
view: order_by_category {
view_label: "Order Items"
derived_table: {
explore_source: order_items {
column: category { field: products.category }
column: sale_price { field: order_items.total_sale_price }
derived_column: rank {
sql: RANK() OVER (ORDER BY sale_price DESC) ;;
}
# bind_filters: {
# to_field: order_items.created_date
# from_field: order_items.created_date
# }
bind_all_filters: yes
}
}
dimension: category {
hidden: yes
primary_key: yes
}
dimension: brand {
hidden: yes
}
dimension: rank {
label: "Ranking by Category"
type: number
}
dimension: sale_price {
value_format: "$#,##0.00"
type: number
hidden: yes
}
measure: total_sale_price {
label: "total_sale_price_by_category"
type: sum
sql: ${sale_price} ;;
value_format_name: usd
}
}これでカテゴリー別の売り上げランキングが取得できます。
もちろんこうした項目は、派生テーブル以外の方法、例えばテーブル計算などでも取得できますが、テーブル計算の場合、取得する行数がExploreの上限に引っかかったり、ディメンションの組み合わせが変更されたりすると、予期した結果が取得できないことがあります。しかし、NDTなどの派生テーブルを使うとサブクエリーで結果を事前に取得できますので、常に意図した結果を得ることができます。
以上、ネイティブ派生テーブルの使用について、簡単な例をもとに記事を書いてみました。（少しだけ書くつもりがだいぶ長くなってしまい、アドベントカレンダーではない気がしますが。。。）
皆様の参考になれば幸いです。
