モチベーション

Lookerのviewで、どうしてもmeasure内にBigQueryのwindow関数を使いたくなることがあるかと思います。

hoge_view.lookml

view: hoge_view {
  derived_table: {
    sql: select ~~~ （略）
  }

  dimension: d1_for_partition {(略)}
  dimension: d2_for_partition {(略)}
  dimension: d3_for_order {(略)}
  measure: m1 {(略)}
  measure: m_with_window {
    sql:
      first_value(${m1}) over (
          partition by ${d1_for_partition}, ${d2_for_partition}
          order by ${d3_for_order} desc)
      ;;
    required_fields: [d3_for_order]
  }

このとき、SQL内のpartition句に記述した d1_for_partition, d2_for_partition がexplore時に選択されていないと、
SELECTしていない列でpartitionしようとしてSQLエラーとなります。
ユーザーがexplore時に d1_for_partition を選択したときには ``d1_for_partitionのみでpartition by` する＆＆両方選択したときは両方 `partition by` する＆＆どちらも選択していないときはpartitionしないようなmeasureが定義できると便利そうです。

実現方法

view: hoge_view {
  derived_table: {
    sql: select ~~~ （略）
  }

  dimension: d1_for_partition {(略)}
  dimension: d2_for_partition {(略)}
  dimension: d3_for_order {(略)}
  measure: m1 {(略)}
  measure: m_with_window {
    sql:
      {% assign partition_expr = '1' %}
      {% if hoge_view.d1_for_partition._in_query %}
        {% assign partition_expr = partition_expr | append: ",${d1_for_partition}" %}
      {% endif %}
      {% if hoge_view.d2_for_partition._in_query %}
        {% assign partition_expr = partition_expr | append: ",${d2_for_partition}" %}
      {% endif %}
      
      first_value(${m1}) over (
          partition by {{partition_expr}}
          order by ${d3_for_order} desc)
      ;;
    required_fields: [d3_for_order]
  }

partition by のあとに続くexpressionを、liquid expressionで生成しています。

liquid式の assign と、lookerの _in_query を使用しています。
- https://shopify.dev/docs/themes/liquid/reference/tags/variable-tags#assign
- https://docs.looker.com/ja/reference/liquid-variables
bigqueryのpartition expressionは、 partition by d1_for_partition と partition 1,d1_for_partition が同値なため、 partition_expr を 1 で初期化し、dimensionが選択されるたびに append: ",${d1_for_partition}" して、partition用の文字列を生成しています。

選択される可能性のあるdimensionの数だけ

      {% if hoge_view.d1_for_partition._in_query %}
        {% assign partition_expr = partition_expr | append: ",${d1_for_partition}" %}
      {% endif %}

の部分が必要となるため、あまり多用するとlookmlがめちゃくちゃ長くなってしまいますが、それでもwindow関数がmeasureで使いたい場合はこうやってmeasureを定義するのが良さそうです。

具体的には、↓でどうしてもmeasureでwindow関数を使う必要があった。

余談

Lookerのサポートの方にこのことを質問したところ…

measureでのwindow関数の使用については、公式からのサポートは得られないと考えたほうが良さそうです。
自己責任でお使いください。