対称集計について

Looker の対称集計は強力な機能です。ただし、対称集計は少し複雑で、背後で実行されるため、わかりにくい場合があります。このページでは、対称集計について次の情報を提供します。

対称集計が必要な理由

データ分析言語である SQL は非常に強力です。しかし、優れた性能には大きな責任が伴います。アナリストには、合計、平均値、カウントなど、誤った集計が誤って計算されないようにする責任があります。

このような計算が誤って実行されることは驚くほど多く、このような計算が間違っていると、アナリストが不満を抱く可能性があります。次の例は、その失敗方法を示しています。

たとえば、orders と order_items の 2 つのテーブルがあるとします。order_items テーブルは、注文のアイテムごとに 1 行を記録するため、テーブル間の関係は 1 対多になります。1 つの注文に複数のアイテムを含めることができるが、各アイテムは 1 つの注文の一部にしかできないため、関係は 1 対多です。結合の正しい関係を決定する方法については、relationship パラメータを適切に取得するベスト プラクティスのページをご覧ください。

この例では、orders テーブルが次のようになっているとします。

order_id user_id total order_date
1 100 $ 50.36 2017-12-01
2 101 $ 24.12 2017-12-02
3 137 $ 50.36 2017-12-02

この orders テーブルでは、total 列（SUM(total)）の値の合計は 124.84 になります。

order_items テーブルに 6 つの行が含まれているとします。

order_id item_id quantity unit_price
1 50 1 $ 23.00
1 63 2 $ 13.68
2 63 1 $ 13.68
2 72 1 $ 5.08
2 79 1 $ 5.36
3 78 1 $ 50.36

注文した商品の数を簡単に取得できます。quantity 列（SUM(quantity)）の値の合計は 7 です。

ここで、共有列 order_id を使用して、orders テーブルと order_items テーブルを結合するとします。これにより、次の表が得られます。

order_id user_id total order_date item_id quantity unit_price
1 100 $ 50.36 2017-12-01 50 1 $ 23.00
1 100 $ 50.36 2017-12-01 63 2 $ 13.68
2 101 $ 24.12 2017-12-02 63 1 $ 13.68
2 101 $ 24.12 2017-12-02 72 1 $ 5.08
2 101 $ 24.12 2017-12-02 79 1 $ 5.36
3 137 $ 50.36 2017-12-02 78 1 $ 50.36

上記の表は、12 月 1 日に 2 つの品目が注文され（order_date 列の 2017-12-01）、12 月 2 日に 4 つの品目が注文された（2017-12-02）といった新しい情報を示しています。前述の計算の一部（SUM(quantity) 計算など）は引き続き有効です。ただし、費用の合計を計算しようとすると、問題が発生します。

前の計算 SUM(total) を使用すると、order_id の値が 1 である行の新しいテーブルの 50.36 の合計値が 2 回カウントされます。これは、順序に 2 つの異なるアイテム（item_id の値が 50 と 63）が含まれているためです。この順序には 3 つの異なる項目が含まれるため、order_id が 2 の行の合計 24.12 は 3 回とカウントされます。そのため、このテーブルの計算 SUM(total) は正しい答え（124.84）ではなく 223.44 になります。

2 つの小さなテーブルを処理している場合、この種のミスは容易に回避できますが、実際の状況では、大量のテーブルと大量のデータを使用するこの問題の解決ははるかに複雑になります。これは、誰かに気づかれずに行うことができる計算の誤りです。これが、対称集計によって解決される問題です。

対称集計の仕組み

対称集計により、アナリストや Looker を使用している他のユーザーが合計、平均値、数などの集計が誤って計算されるのを防ぐことができます。対称集計は、ユーザーが誤ったデータで課金するのを防げるため、アナリストの負担を大幅に軽減します。対称集計では、計算に含まれる各ファクトが正しい回数をカウントしていることを確認し、計算対象を追跡することでこれを行います。

上記の例では、対称集計関数は total が（order_items ではなく）orders のプロパティであることを認識しているため、正しい答えを得るためには各注文の合計を 1 回だけカウントする必要があります。この処理を行うため、アナリストが Looker で定義した一意の主キーを使用します。つまり、結合テーブルで Looker が計算を行う場合、order_id の値が 1 の行が 2 つあるとしても、合計がすでに合計されているため合計を 2 回カウントすべきではないと認識することになります。計算に含める必要があります。また、order_id の値が 2 の 3 行の合計を 1 回だけカウントする必要があります。

対称集計は一意の主キーと、モデルに指定される正しい結合関係に依存することに注意してください。正しく表示されない場合は、アナリストに相談して、すべての設定が正しいことを確認してください。

対称集計が複雑になる理由

対称集計の外観は、少し不可解になる可能性があります。対称集計がない場合、Looker は通常、適切に動作する SQL を次のように作成します。

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price"
FROM order_items AS order_items

GROUP BY 1,2
ORDER BY 1
LIMIT 500

対称集計の場合、SQL Looker の書き込みは次の例のようになります。

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price",
  (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
  *(1000000*1.0)) AS DECIMAL(38,0))) +
  CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) )
  - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) AS DOUBLE PRECISION)
  / CAST((1000000*1.0) AS DOUBLE PRECISION), 0)
  / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id
  ELSE NULL END), 0)) AS "users.average_age
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

対称集計の厳密な形式は Looker が作成している SQL の言語によって異りますが、すべての形式で同じ基本的な処理が行われる（複数の行が同じ主キーを持つ場合）対称集計関数ではそれらのカウントのみが行われる 1 回だけです。これを行うには、SQL 標準の一部であるあまり知られていない SUM DISTINCT 関数と AVG DISTINCT 関数を使用します。

これを確認するには、すでに行った計算を、対称集計で計算します。結合したテーブルの 7 つの列のうち、集計する列（total）と注文の一意の主キー（order_id）は 2 つあれば十分です。

order_id total
1 $ 50.36
1 $ 50.36
2 $ 24.12
2 $ 24.12
2 $ 24.12
3 $ 50.26

対称集計は主キー（この場合は order_id）を受け取り、それぞれに非常に大きな数値を作成します。これは一意の値であることが保証され、同じ入力に対して常に同じ出力が生成されます。（これは一般にハッシュ関数で行われますが、その詳細はこのページの範囲外です。）結果は次のようになります。

big_unique_number total
802959190063912 $ 50.36
802959190063912 $ 50.36
917651724816292 $ 24.12
917651724816292 $ 24.12
917651724816292 $ 24.12
110506994770727 $ 50.36

各行に対して、Looker は次の処理を行います。

SUM(DISTINCT big_unique_number + total) - SUM(DISTINCT big_unique_number)

これにより、合計を正確に集計し、合計を正確にカウントできます。Looker の対称集計関数は、繰り返しの行や、合計が同じである複数の注文によって混乱することはありません。対称集計の仕組みを知るには、自分で計算してみてください。

これを行うのに必要な SQL は容易ではありません。CAST()、md5()、SUM(DISTINCT)、STRTOL() で、SQL を手作業で作成したいとは思わないでしょう。ただし、Looker が SQL を自動的に記述できます。

対称集計を必要とせずに集計が適切に機能する場合、Looker ではこの機能が自動的に検出され、関数は使用されません。対称集計によってパフォーマンス コストが生じるため、Looker は使用すべきタイミングと使用すべきタイミングを判断でき、Looker で生成される SQL は最適化され、正しい答えが得られることを保証しながら、可能な限り効率的に処理されます。