G-genの菊池です。当記事では、Looker の対称集計機能について解説します。

対称集計とは

対称集計は、Looker の標準機能です。Looker では、複数のテーブルを結合して利用する場合があります。結合するテーブル間の関係が「1対多」の場合、集計関数（カウント、合計、平均など）で値を求めると、一部の値が二重にカウントされる可能性があります。集計結果が誤った値にならないように、本来はテーブルを結合する段階で考慮が必要です。

しかし Looker では、ユーザーが明示的に対処しなくても、対称集計機能を用いることでデータの二重カウントを気にせずにテーブルを結合して集計できます。

参考 : 対称集計について | Looker | Google Cloud

対称集計が必要になる状況

ここでは、対称集計が必要な状況と、対象集計がない場合に発生する問題について説明します。

顧客テーブル（customers）と注文テーブル（orders）の2つのテーブルがあるとします。それぞれのテーブルは、以下の列を持っています。

顧客テーブル（customers）
- 顧客番号（customer_id）
- 名前（name）
- 訪問回数（visits）
注文テーブル（orders）
- 注文番号（order_id）
- 金額（amount）
- 顧客番号（customer_id）

1人の顧客が複数の注文をする可能性があるため、顧客テーブルと注文テーブルの関係は「1対多」です。

各テーブルで SQL を使い COUNT や SUM 演算を行うと、以下のようになります。

顧客テーブルの COUNT(*) で顧客数は 3、sum(visits) で訪問回数の合計は 8
注文テーブルの COUNT(*) で注文数は 4、sum(amount) で金額の合計は 250

顧客ごとの注文数を知るために、2つのテーブルを結合します。2つのテーブルに共通する顧客番号で紐づけて結合するのが一般的です。

1対多のテーブルを結合すると、行数が少ないテーブルの行が重複します。例では、顧客テーブルのAmeliaは2回注文しているため、結合後のテーブルでは2行になります。

ここで、結合後のテーブルで、最初に求めたカウントと合計を計算します。

COUNT(*)=4で注文数と一致するが、顧客数とは一致しない
sum(amount)=250は金額の合計と一致するが、sum(visits)=10は訪問回数の合計とは一致しない

注文テーブルの集計は結合後のテーブルでも一致しますが、顧客テーブルの集計は一致しません。

このように、1対多のテーブルを結合すると、行数が少ないテーブルの数値が重複カウントされ、誤った集計値になります。

Lookerの対称集計はこのような1対多のテーブル結合による重複カウントを防ぐことができます。

対称集計の仕組み

Looker では、顧客テーブル（customers）や注文テーブル（orders）を view ファイルとして保持します。また、顧客ごとの金額を算出するには、model ファイルでテーブルの結合を定義します。

Looker では、Explore で表示したいフィールドを選ぶと、表やグラフで結果を可視化できます。この時、Looker はバックグラウンドで SQL クエリを生成し、データベースに送信してデータを取得します。

訪問回数と金額のフィールドを表示する際、対称集計機能がないと、各列を単純に合計する SQL が生成されます。この SQL では、前述した重複集計の問題が発生します。

しかし、Looker には標準で対称集計機能があるため、SQL クエリは以下のように自動で修正されます。

結合後のテーブルで訪問回数（visits）の合計を正確に集計するための対称集計の計算方法を説明します。

Looker は、MD5 ハッシュ関数で主キーごとに一意の数値を生成し、割り当てます。顧客名が Amelia の2つの行には、同じ big_unique_number が割り当てられます。

そして、正確な訪問回数を計算するために、以下の計算式を使用します。

SUM ( DISTINCT visits + big_unique_number ) - SUM ( DISTINCT big_unique_number )

通常の SUM(DISTINCT) 関数だけで計算する場合、visits の値だけで同じ顧客のレコードかどうかを判断してしまいます。訪問回数が2回になっている3つのレコードを同じ顧客のレコードとみなした結果、訪問回数の合計は「6回」となり、誤った値になります。

ハッシュを付与する理由は、visits の値だけでは同じ顧客のレコードを特定できないためです。主キーである顧客ID（customer_id）ごとに一意のハッシュ値を割り当てることで、訪問回数が同じ2回のレコードでも、Amelia と Charles のレコードを区別できます。

顧客ID（customer_id）ごとの訪問回数+ハッシュ値の合計を算出し、そこからハッシュ値の合計を引くことで、正確な訪問回数の合計を計算します。

対称集計の使い方

必要な設定

これらの計算は Looker が自動で実行するため、ユーザーが理解したり、意識する必要はありません。

Lookerの対称集計機能は標準で有効であり、ユーザーが明示的に有効にする必要はありません。ただし、対称集計を正しく動作させるには、view ファイルと model ファイルの Explore で、以下の2つの項目を正しく定義する必要があります。

一意の主キー（primary_key）
正しい結合関係（relationship）

一意の主キー（primary_key）

レコードを一意に識別できる列が主キーです。

主キーは、図のように primary_key で指定します。対称集計を有効にするには、結合するすべてのテーブルに主キーを設定する必要があります。

主キーが一意なキーとして機能しているか確認するには、テーブルのカウントと主キーのカウントが一致するかを確認します。一致しない場合は、各行を一意に識別できる列を探します。ない場合は、複数の列で一意に識別できるもの（複合主キー）を探します。

ただし、primary_key: yes パラメータを設定できるのは view 内で1つのディメンションのみですので、複合主キーを利用する場合は、必要な列を連結して新しいディメンションを作成します。

正しい結合関係（relationship）

テーブルを結合する際は、テーブル間の正しい関係を定義する必要があります。

結合関係は、図のように model ファイル内で relationship の値を設定します。

relationship には、左右のテーブルという概念があります。join パラメータの前にあるビュー（画像では order）を左側、join パラメータの右にあるビュー（画像では customers）を右側とします。

テーブル間の関係は、 relationship で以下の4つの値のいずれかを指定して定義します。

値	関係
one_to_one	1対1の関係。2つのテーブルが1対1に紐づく。
one_to_many	1対多の関係。左のテーブルの1行が右のテーブルの複数行に紐づく。
many_to_one	多対1の関係。左のテーブルの複数行が右のテーブルの1行に紐づく。
many_to_many	多対多の関係。左のテーブルの複数行が右のテーブルの複数行に紐づく。