Zenn
🍉

【Looker】テンプレートフィルタを使っていい感じに指標をdimension化する

2021/08/18に公開
Looker
tech

Lookerで特定の指標をフィルタを効かせながらdimension化したので、そのやり方について解説したいと思います。
フィルタを効かせながら、というのが今回のポイントです。

利用するテーブル

sales_orderをいう受注テーブルを元に解説していきます。

ベースとなるView

view: sales_order {
  sql_table_name: `sales_order` ;;
  
  # 注文ID
  dimension: order_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

  # ユーザID
  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  # 注文日時
  dimension_group: order_at {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.order_at ;;
  }

  # 商品ID
  dimension: product_id {
    type: number
    sql: ${TABLE}.product_id ;;
  }

  # 商品カテゴリ
  dimension: product_category {
    type: number
    sql: ${TABLE}.product_category ;;
  }

  # 売上
  measure: sales {
    type: sum
    sql: ${TABLE}.sales ;;
  }
}

ユーザーごとの売上をディメンションにする

ユーザーごとの売上をディメンションにします。
具体的には、derived_tableを利用しユーザーごとに集計した売上をJOINします。
実装としては、以下のようになります。

derived_table: {
    sql:
      SELECT
        t0.order_id,
        t0.user_id,
        t0.order_at,
        t0.product_id,
        t0.product_category,
        t0.sales,
        t1.sales_per_user
      FROM `sales_order` AS t0
      INNER JOIN (
        SELECT
          user_id,
          SUM(sales) as sales_per_user
        FROM `sales_order`
        GROUP BY user_id
      ) as t1
      ON t0.user_id = t1.user_id
    ;;
  }

  dimension: sales_per_user {
    type: number
    sql: ${TABLE}.sales_per_user ;;
  }

と、簡単にできましたが、ここで１つ問題があります。

product_id や product_category でフィルタを利用してもユーザーごとの売上には、フィルタがかかりません。

ユーザーごとの売上にもフィルタを適用する

Templated filtersを使って、指定されたフィルタをサブクエリ内にも適用することで解決することができます。

具体的には以下のように実装をします。

derived_table: {
    sql:
      SELECT
        t0.order_id,
        t0.user_id,
        t0.order_at,
        t0.product_id,
        t0.product_category,
        t0.sales,
        t1.sales_per_user
      FROM `sales_order` AS t0
      INNER JOIN (
        SELECT
          user_id,
          SUM(sales) as sales_per_user
        FROM `sales_order`
        WHERE {% condition product_id %} product_id {% endcondition %}
          AND {% condition product_category %} product_category {% endcondition %}
        GROUP BY user_id
      ) as t1
      ON t0.user_id = t1.user_id
    ;;
  }

てっきり、Templated filtersはfilterにしか利用できないと思っていました…

【注意】dimension_groupでのテンプレートフィルタ利用

dimension_groupでテンプレートフィルタを利用する際は注意が必要です。
以下のdimensionに対してテンプレートフィルタを利用するには

dimension_group: order_at {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.order_at ;;
  }

{% condition time_raw %} order_at {% endcondition %}

とするだけでは日付や月を指定した際にフィルタされません。
timeframesで指定したすべてを入れてあげる必要があります

{% condition time_raw %} order_at {% endcondition %}
AND {% condition time_time %} order_at {% endcondition %}
AND {% condition time_date %} order_at {% endcondition %}
AND {% condition time_week %} order_at {% endcondition %}
AND {% condition time_month %} order_at {% endcondition %}
AND {% condition time_year %} order_at {% endcondition %}

まとめ

  • Templated filtersを使っていい感じに指標をディメンション化
  • Templated filtersはdimensionにも適用できる
    • 正確には”LookMLフィールドをフィルタ条件として利用できる”
  • ただし、dimension_groupを利用するにはtimeframesすべてをいれる必要がある

Discussion