対称集計ってなんなのか

cloud.google.com

Looker の対称集計という機能が気になっているのだけれども、いまいちよく分かっていない。検索してもあまり関連する文献が見つからないし、なんで対称集計という名前なのかも分からない。データウェアハウスで、非正規化されたデータの上で集計をする時に使える概念なのだとは思うのだけれども。

とりあえず、実験してみる。Lookerのドキュメントに載っているデータ例をErqで取り込む。(後々のため、データを追加している。)

load table orders(order_id integer, user_id integer, total real, order_date text) from
```csv
1   100 50.36   2017-12-01
2   101 24.12   2017-12-02
3   137 50.36   2017-12-02
4   100 59.68   2017-12-03
``` delimiter E'\t';;

load table order_item(order_id integer, item_id integer, quantity integer, unit_price real) from
```csv
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
4   50  2   23.00
4   63  1   13.68
``` delimiter E'\t';;

table data = orders join order_item using (order_id);;

注文の合計金額は簡単に計算できる。

erq> orders {sum(total)};;
select sum(total) from orders
["sum(total)"]
[184.52]
1 row (0.001s)

一方で、結合済みのデータを使って合計金額を計算しようとすると、正しく計算できない。表が結合されるときに、ordersテーブルの各行が複製されているからだ。

erq> data {sum(total)};;
select sum(total) from data
["sum(total)"]
[342.8]
1 row (0.001s)

そこで、ordersテーブルのキーを使って、うまいことやる必要がある。

ひとつの方法は、合計する値にいい感じにキーの情報を埋め込み、sum distinct関数を使って集約を行うことだ。ここでは、単純に、order_idの値を10000倍したものをtotalに加えることで、キーの情報を埋め込んでいる。最後に、order_idだけのsum distinct結果を引けば、order_idで区別されたtotalの合計になるはずだ。

erq> data {sum(distinct total + order_id * 10000) - sum(distinct order_id * 10000)};;
select sum(distinct total + order_id * 10000) - sum(distinct order_id * 10000) from data
["sum(distinct total + order_id * 10000) - sum(distinct order_id * 10000)"]
[184.52000000000407]
1 row (0.000s)

結果は、惜しいことになってしまった。SQLiteでは浮動小数点数を使っているために、大きい数を使って情報を埋め込むと、計算したいtotalの合計値には大きな誤差が出てしまう。

逆に、小数点以下に埋め込むと、誤差はマシになる。(そもそも、金額の計算に浮動小数点数を使うこと自体を避けるべきだろうが。)

erq> data {sum(distinct total + order_id * 1e-10) - sum(distinct order_id * 1e-10)};;
select sum(distinct total + order_id * 1e-10) - sum(distinct order_id * 1e-10) from data
["sum(distinct total + order_id * 1e-10) - sum(distinct order_id * 1e-10)"]
[184.52]
1 row (0.000s)

idを単純に定数倍して足す代わりに、ハッシュ関数に通すなど、複雑な変換を行うこともできるが、異なるキーの値が偶然衝突しないように埋め込めるなら、方法はどうやってもいい。ただ、先ほどの例のように、誤差やオーバーフロー・アンダーフローには気を付ける必要がある。また、キーを埋め込んだ値を集約したあとで、埋め込んだキーの影響を集約結果から打ち消す計算ができる、ということも必要だ。

グループ化をした場合も、同じ方法が使える。ここでは、user_idでグループ化して、合計と平均を計算してみる。

erq> data {user_id => sum: sum(distinct total + order_id * 1e-8) - sum(distinct order_id * 1e-8), avg: avg(distinct total + order_id * 1e-8) - avg(distinct order_id * 1e-8)};;
select user_id, sum(distinct total + order_id * 1e-8) - sum(distinct order_id * 1e-8) as sum, avg(distinct total + order_id * 1e-8) - avg(distinct order_id * 1e-8) as avg from data group by (user_id)
["user_id","sum","avg"]
[100,110.04,55.02]
[101,24.12,24.12]
[137,50.36,50.36]
3 rows (0.001s)

ところで、サブクエリが使えるのなら、こんな面倒なことをしなくても、何段階かに分けて集計を行えば済む話ではある。集計したいカラムを抜き出してきて

erq> data {user_id, order_id, total};;
select user_id, order_id, total from data
["user_id","order_id","total"]
[100,1,50.36]
[100,1,50.36]
[101,2,24.12]
[101,2,24.12]
[101,2,24.12]
[137,3,50.36]
[100,4,59.68]
[100,4,59.68]
8 rows (0.001s)

重複を除外する

erq> data {user_id, order_id, total} distinct;;
select distinct user_id, order_id, total from data
["user_id","order_id","total"]
[100,1,50.36]
[101,2,24.12]
[137,3,50.36]
[100,4,59.68]
4 rows (0.001s)

あとは、それを集計すればよい。

erq> data {user_id, order_id, total} distinct {user_id => sum: sum(total), avg: avg(total)};;
select user_id, sum(total) as sum, avg(total) as avg from (select distinct user_id, order_id, total from data) group by (user_id)
["user_id","sum","avg"]
[100,110.03999999999999,55.019999999999996]
[101,24.12,24.12]
[137,50.36,50.36]
3 rows (0.000s)

(まあ、なんか誤差が出ているけど、これは浮動小数点数のせいだと思うし、気にしないことにする。)

distinctじゃなくて、group byをつかって重複除外を行なってもいい。こちらの方が、行全体ではなくキーの値だけを使って重複を除外するので、計算がより軽いかもしれない。

erq> data {order_id => user_id, total} {user_id => sum: sum(total), avg: avg(total)};;
select user_id, sum(total) as sum, avg(total) as avg from (select order_id, user_id, total from data group by (order_id)) group by (user_id)
["user_id","sum","avg"]
[100,110.03999999999999,55.019999999999996]
[101,24.12,24.12]
[137,50.36,50.36]
3 rows (0.001s)

SQLiteではgroup by句で指定されていない非集約カラムであるuser_id, totalは、order_idに対して関数従属であるので、select句に出てきても問題がないのだが、これを受け付けないSQLエンジンを使っている場合は、any_value集約関数を使うか、適当にmin集約関数を使うなどの対処をする必要がある。)

とにかく、サブクエリで group by を使って重複を除去しておく方法は素直で分かりやすいし、sumやavgのような集約関数の性質に左右されず汎用的な技法なので、特に制約がなければ、後者の多段階の集約を使った方がいいように思う。BIツールなどでは、集約のフォーマット・段階が固定であるといった制約があるために、前者の方法を使うしかないのかもしれない。