[Looker] テンプレートフィルターのsqlパラメータがなんなのか理解したぁ！ #looker

filter only fieldを作る方のfilterパラメータにsqlパラメータが書けるのは知っていたのですが、どういう挙動をするのかを試してみました。

まずは普通に使ってみる

普通というのは、filterパラメータを作成して、派生テーブルのwhere句に{% condition filter_name %} table.field {% endcondition %}を書く一番シンプルな方法です。

view: dt_filters {
  derived_table: {
    sql: SELECT
          users.state  AS users_state,
          users.gender  AS users_gender,
          COUNT(*) AS users_count
      FROM looker-private-demo.ecomm.users  AS users
      WHERE {% condition select_resion %} users.state {% endcondition %}
      GROUP BY
          1,
          2 ;;
  }
  
  filter: select_resion {
    label: "地域"
    type: string
  }
  :
}

これをExploreでどんなSQLが生成されるかを確認します。

Exploreのフィルターで指定したロジックに合わせてwhere句を生成してくれました。

filterパラメータにsqlを使ってみる

派生テーブルのwhere句に{% condition filter_name %} table.field {% endcondition %}ロジックを書いて挙動を確認できたので、今度はfilterパラメータにsqlを書いて挙動を確認します。

view: dt_filters {
  derived_table: {
    sql: SELECT
          users.state  AS users_state,
          users.gender  AS users_gender,
          COUNT(*) AS users_count
      FROM looker-private-demo.ecomm.users  AS users
      WHERE {% condition select_resion %} users.state {% endcondition %}
      GROUP BY
          1,
          2 ;;
  }
  
  filter: select_resion {
    label: "地域"
    type: string
    sql: {% condition select_resion %} ${users_state} {% endcondition %} ;;
  }
  :
}

Exploreから生成されたSQLを確認します。

（派生テーブル）with区のwhere句に加えて、本体のselect区の方にもwhere句が生成されました。

SQL弱者で申し訳ないのですが、派生テーブルのwhere句だけでなく、外側のwhere句にも必要な場合には使えるの・・・でしょうか？（いるのかな・・・）

派生テーブルじゃないところでfilterパラメータにsqlを書く

派生テーブル内ののfilterパラメータにsqlを書いてしまうと、二重でwhere句が生成されてしまうので、普通のviewでfilterパラメータにsqlを書いてみます。

view: users {
  :
  dimension: state {
    label: "州・都道府県"
    sql: ${TABLE}.state ;;
    drill_fields: [zip, city]
  }
  
  filter: select_resion {
    label: "地域"
    type: string
    suggest_dimension: state
    sql: {% condition select_resion %} ${state} {% endcondition %} ;;
  }
  :
}

ExploreでどのようなSQLが生成されるのかを確認します。

filter only fieldである地域パラメータを選択して、フィルターをかけたい値を入力したらそのロジックでwhere句が生成されました。

これが、sqlがなくなるとどういう挙動をするのかを確認しす。

view: users {
  :
  dimension: state {
    label: "州・都道府県"
    sql: ${TABLE}.state ;;
    drill_fields: [zip, city]
  }
  
  filter: select_resion {
    label: "地域"
    type: string
    suggest_dimension: state
  }
  :
}

これだとどうなるでしょうか？

where句は生成されませんでした。（ですよね。念のため）

filterパラメータで参照するフィールドをhiddenにしてみる

ドキュメントの最後にfilterパラメータで参照するフィールドはhiddenでユーザーから隠すことができるとのことなので、確認しました。

わかりやすいようにfiltersで参照しているフィールド以外は消してしまって、primary_keyもhiddenにしました。

view: users {
　　dimension: id {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.id ;;
    tags: ["user_id"]
  }

  dimension: state {
    label: "州・都道府県"
    sql: ${TABLE}.state ;;
    hidden: yes
  }
  
  filter: select_resion {
    label: "地域"
    type: string
    suggest_dimension: state
    sql: {% condition select_resion %} ${state} {% endcondition %} ;;
  }

  measure: count {
    label: "ユーザー数"
    type: count
  }
}

Exploreで挙動を確認してみます。

ちょっと画像だとわかりづらいのですが、左側のフィールドピッカーにはmeasureのユーザー数とfilter only fieldの地域だけしかありません。

しかしきちんとhiddenされているstateでフィルターがかけられています。

hiddenはユーザーから隠されるだけで、ユーザーからは参照できるので想定通りの挙動ですね。

まとめ

ずっと気になっていてfilterのsqlパラメータについて検証してみました。

SQLのwhere句ににベタ書きでfilterの値を取るだけでいい場合には不要ですが、ユーザーがExploreでfilter　only fieldを選択しただけでwhere句を生成させたい場合には使えますね。すっきり