「絞る・割る・足す」の順番を守って、フィルタをかけてもずれない合計を出す

先日、社内で Looker を使っている現場の方から、こんな相談がありました 💬

「Looker で店ごと・月ごとの1時間あたり売上を並べて、合計行には各店の値を足した数字を出したい」

（実際に頼まれたのは別の指標ですが、この記事では飲食チェーン店の売上に置き換えて説明します）

Looker の合計行は、通常は「全店の売上 ÷ 全店の営業時間」というふうに計算されるため、各店の値を足した数字にはなりません。かといって、店ごとに割ってから足す計算は、普通の measure では書けません。

最初は、BigQuery で店ごとの値を事前に計算し、それを足せばよいと考えました。しかし、この方法では「ランチタイムだけ」のように特定の時間帯で絞り込むと、正しい合計値を得られません 😢

この記事では、なぜ作っておいた値だとずれるのかを整理したうえで、Looker の ネイティブ派生テーブル（NDT）と bind_all_filters を使って、割る前にフィルタで絞る形にした方法を紹介します。

Looker の合計行は店ごとの値を足してくれない

例として、A店とB店のある1か月の売上と営業時間で考えます。

	売上	営業時間	1時間あたり売上
A店	100,000円	10時間	10,000円
B店	180,000円	90時間	2,000円
合計行	280,000円	100時間	2,800円

現場の方が見たかったのは 10,000 + 2,000 = 12,000円（各店の1時間あたり売上を合算した値）ですが、Looker の合計行は 2,800円 となります。

これは Looker の仕様で、合計行では店ごとのグループ化を外し、対象データ全体で measure を計算し直すためです。（売上の合計）÷（営業時間の合計）なので、全体の平均になってしまいます。

合計値がテーブルの値と一致しない理由  |  Looker  |  Google Cloud Documentation

docs.cloud.google.com

表計算の sum() を使えば各店の値を合計できますが、表示する行に依存するため、汎用的な指標としては扱いにくいという課題があります。

また、複数の人が繰り返し使う予定だったので、毎回表計算を書かなくても合計行やタイルでそのまま出せるよう、measure（集計値の定義）として用意することにしました。

店ごとの値を先に作っておくと、時間帯で絞っても合計が変わらない

まず考えたのは、店×月の1時間あたり売上を BigQuery に作っておき、Looker では足すだけにする方法です。

ところが、「ランチタイムだけ」のように時間帯で絞ろうとすると困ります。

例えば、ランチタイムだけで見た1時間あたりの売上が A店 8,000円、B店 1,000円だったとすると、足すと 9,000円になるはずですが、作っておいた値は時間帯をまとめて割ったあとの値なので、12,000円のままで変わりません。

店や月で絞るフィルタは効くのですが、作るときにまとめてしまった時間帯のフィルタは効きません。

フィルタをかけたうえで合計を見たいときの正しい順番は「絞る → 店ごとに割る → 足す」です。店×月で計算済みの値だけでは、時間帯で絞った結果を再計算できません。

解決策：NDT と bind_all_filters で「絞る → 割る → 足す」を Looker の中でやる

やりたいのは、フィルタで絞り込みが決まってから、店ごとに割って足すことです。クエリのたびに Looker で SQL を生成し、BigQuery 側で店ごとの値を計算させれば、これができます。

そのために使ったのが、ネイティブ派生テーブル（NDT）です。NDT は、Explore（Looker でフィールドを選んで集計する画面）で組んだクエリの結果を、1つのテーブルのように扱える仕組みです。今回は「店×月ごとの1時間あたり売上」を出すクエリを NDT にしました。

ただし、NDT はそのままだと、ユーザーが Explore で指定したフィルタが効きません。そこで bind_all_filters: yes を付けます。これで、ランチタイムで絞れば NDT の中もその時間帯だけで集計されます。

あとは、この NDT を元の Explore に JOIN し、店ごとの値を足す measure を置けば完成です。そうすると、いつも使っている Explore からこの measure を選べるようになり、店でもエリアでも、行のまとまりに含まれる店の値を足せます 👏

簡略化した LookML はこちらです。（store_sales.sales_per_hour は「売上の合計 ÷ 営業時間の合計」を計算する measure、sales_month は BigQuery の DATE 型を想定）

view: store_hourly_sales_for_join {
  derived_table: {
    explore_source: store_sales {
      column: store_name { field: store_sales.store_name }
      column: sales_month { field: store_sales.sales_month }
      column: sales_per_hour { field: store_sales.sales_per_hour }
      bind_all_filters: yes
    }
  }

  dimension: store_month_key {
    primary_key: yes
    hidden: yes
    sql: CONCAT(${TABLE}.store_name, '|', CAST(${TABLE}.sales_month AS STRING)) ;;
  }

  dimension: store_name { hidden: yes }
  dimension: sales_month { hidden: yes }

  measure: sales_per_hour_sum {
    hidden: yes
    type: sum
    sql: ${TABLE}.sales_per_hour ;;
  }

  measure: month_count {
    hidden: yes
    type: count_distinct
    sql: ${sales_month} ;;
  }

  measure: sales_per_hour_sum_display {
    label: "1時間あたり売上（店ごとの値の合計）"
    type: number
    sql: CASE WHEN ${month_count} = 1 THEN ${sales_per_hour_sum} END ;;
  }
}

explore: store_sales {
  join: store_hourly_sales_for_join {
    type: left_outer
    relationship: many_to_one
    sql_on: ${store_sales.store_name} = ${store_hourly_sales_for_join.store_name}
      AND ${store_sales.sales_month} = ${store_hourly_sales_for_join.sales_month} ;;
  }
}

bind_all_filters を付けると、生成される SQL では NDT が WITH 句になり、Explore で指定した絞り込みが その WITH 句の WHERE にも入ります。「割る前に絞る」が Looker の中でできるということです。

フィールドを選んで絞る通常のフィルタだけでなく、カスタムフィルタ（Looker 式で条件を自由に書くフィルタ）で絞った場合も、同じように WITH 句に入ることを確認しました。

これで、ランチタイムで絞っても、合計行が各店の値を足した 9,000円になりました！

補足：実装で気をつけたこと

• 主キーを付ける：JOIN 後に同じ値が重複して集計される可能性があります。Looker が重複を考慮して正しく集計できるよう、NDT に店×月の組み合わせを一意に識別する主キーを設定しました。
• 月が1つのときだけ値を出す：A店の1月と2月の1時間あたり売上を足しても、意味のある数字になりません。そこで count_distinct で月の数を数え、1か月分のときだけ値を出すようにしました（複数月をまたぐ合計行は空欄になります）。
• JOIN できるのは同じ Explore だけ：bind_all_filters を使う NDT は、explore_source と同じ Explore にしか JOIN できません。

まとめ

足してから割る値は、売上と営業時間を作っておけば後から出すことができます。一方で割ってから足す値は、どの軸で絞るかが先に決まっていないと、作っておけません。

「割る」と「絞る」のどちらを先にするかで、BigQuery に作っておけるか、Looker でクエリのたびに計算するかが変わります。割り算を含む指標を作るときは、どの軸で絞られるかを先に確認しておくことが大切ですね ➗🔁➕