[Looker] 既存のmeasureを使って新しい計算するmeasureを定義したときにハマった話

奈良県でリモートワーク中の玉井です。

LookMLでは、既に定義されたmeasure同士を計算させて、新しいmeasureを定義することができますが、それをするときにハマったポイントがあるので、それについて記します。

私がハマったケースその1

例えば、下記のように「売上」と「利益」という2つのmeasureがあったとします（元となるdimentionが別途ある前提）。

measure: total_sales {
  type: sum
  sql: ${sales} ;;
}

measure: total_profit {
  type: sum
  sql: ${profit} ;;
}

この2つを使って、新たに「利益率」というmeasureを定義したいとします。どう書きますか？

何も考えずに書くと…

私は最初、何も考えずに下記のように書きました。

measure: profit_rate {
  type: sum
  sql: ${total_profit} / ${total_sales} ;;
}

上記のように書いても、LookML上でエラーは起こりません。しかし、このmeasureをExploreで使おうとすると、エラーが発生します。

エラーの原因

LookML自体のバリデーションは通るだけに、「は？？？？なんで？？？？？」ってなるエラーなのですが、落ち着いて考えればわかります。

割り算する前のmeasure（売上と利益）それぞれをもう一度確認してみましょう。typeがsumですよね。つまり、このmeasureの時点でSUM関数が走ります。そして、それらのmeasure同士を割り算している、上記の利益率というmeasureを改めてチェックすると、こちらもtypeはsumです。

つまり、「SUM関数が走るカラム」をさらにSUMしているので、SQL上で集計の入れ子となり、クエリエラーとなります。

対処法

集計関数の入れ子が原因となっているので、集計を2重に行わせないようにする必要があります。結論から言ってしまうと、measure同士を計算させるmeasureのtypeをnumberにすれば、エラーは回避できます。なぜなら、numberは集計を行わないtypeだからです。

measure: profit_rate {
  type: number
  sql: ${total_profit} / ${total_sales} ;;
}

上記の場合、SUM関数で集計した2つのmeasureを割り算した値をそのまま表示するだけになるので、集計の入れ子状態は回避され、エラーは発生しない…という形となります。

ちなみに、Looker公式情報にも、このエラーに関する記事があります。

そのmeasureに必要な計算は何か、事前に考えておく必要がある

新たに定義するmeasureのtypeをnumberにする以上、そのmeasureに必要な計算（SUMなのかAVGなのか等）は、元となるmeasure側で事前に決めておく必要があります。

上記のケースの場合は、元々の「売上」と「利益」もtypeがsumなので、新たに定義する「利益率」は、SUMした利益 / SUMした売上となります。この計算式が本当にやりたい計算と合っているかどうかチェックする必要があります。

例えば、「利益率の平均を出したい」という要件だった場合は、元のmeasureのtypeをavgにする必要があります。

繰り返しになりますが、「求められている数値はどういう計算で出すのが正しいのか」ということについて、事前にしっかり確認しておきましょう。

私がハマったケースその2

measureにはフィルターを定義することができます。

measure: field_name {
    filters: [dimension_name: "filter expression", dimension_name: "filter expression", …]
  }

LookML上で「条件に該当するレコードだけで集計する」…というフィルタリングをかけたmeasureを定義することが可能となっています。

で、冒頭に挙げたケースのように、measure同士を計算させて作った新しいmeasureにもフィルターを定義したい、ということは要件としてよくあります。

何も考えずに書くと…

普通に書くと、下記のように書きたくなると思います。

measure: profit_rate {
  type: number
  filters: [dimension_name: "フィルタする値" …]
  sql: ${total_profit} / ${total_sales} ;;
}

しかし、上記のように書くとエラーになります。

エラーの原因

measure内に定義するfilters（フィルターメジャーと呼称することにします）は、typeがnumberのmeasureには使えません。

「あらあら、そうなの。じゃ、typeを変えますか…」ってなりそうですが、そうは問屋がおろしません。普通のmeasureであればいいのですが、今回フィルターメジャーを使いたいmeasureは、「他のmeasureを計算して作ったmeasure」です。こういうmeasureはtypeをnumberにする必要があります。でもnumberにするとフィルターメジャーは使えない…あああああ！

「2つの仕様が相反している…打つ手がない…！」って、なりそうですが、冷静に考えれば、普通に対応することができます。

対処法

実は上記の公式ドキュメントに書いてあるのですが、答えは「元のmeasureそれぞれで先にフィルターメジャーを書いておく」です。

LookMLを見れば一目瞭然なので、公式ドキュメントのサンプルを引用します。

measure: total_food_profit {
  type: number
  sql: ${total_food_revenue} - ${total_food_cost} ;;
}
measure: total_food_revenue {
  type: sum
  sql: ${revenue} ;;
  filters: [segment: "food"]
}
measure: total_food_cost {
  type: sum
  sql: ${cost} ;;
  filters: [segment: "food"]
}

計算前のmeasureでフィルターメジャーを定義しておき（先にフィルタリングしておく）、その後で計算させる、という方法です。

おわりに

言われてみれば簡単ですが、知らないと結構ハマるエラーを紹介しました。

ちなみに、あまりにも複雑な計算が飛び交いまくるようでしたら、いっそのこと派生テーブルで計算済テーブルを定義してしまうのも手です。