対称集計について

Looker の対称集計は非常に強力な機能です。ただし、対称集計は少し複雑に見え、ほとんどがバックグラウンドで行われるため、対称集計に遭遇すると混乱することがあります。このページでは、対称集計について次の情報を提供します。

対称集計が必要な理由

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

このような計算を間違って行うことは驚くほど簡単です。このような計算ミスは、アナリストにとって大きなストレスの原因になる可能性があります。次の例は、問題が発生する仕組みを示しています。

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

この例では、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 は orders のプロパティであり（order_items ではない）、正しい解を得るには各注文の合計値を 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

対称集計を使用する場合、Looker が生成した SQL は次の例のようになります。

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 を手作業で作成したいとは思わないでしょう。幸い、SQL を記述する必要はありません。Looker が SQL を自動的に作成します。

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