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ツールなどでは、集約のフォーマット・段階が固定であるといった制約があるために、前者の方法を使うしかないのかもしれない。
