PostgreSQL Advent Calendar 2015 の19日目です。
昨日は、 PostgreSQL 9.5 からの新機能である ROLLUP について記事 でした。
私はよくクロス集計表を作ることが多いので、CUBE について書いてみようと思います。
昨日 PostgreSQL 9.5 RC1 がリリースされました が、
docker が便利すぎるので、9.5 Beta2 で試してみました。
準備
この記事 に習って Docker Tools をインストール。その後 Docker Quickstart Terminal.app を実行するだけで、すぐに docker pull, run などできるようになります。yancyaさんの記事 にある通り、pull して run して bash を実行。
docker pull postgres:9.5
docker run --name postgres_95beta -d postgres:9.5
docker exec -i -t postgres_95beta bash
su postgres
psql
postgres=# select version() ;
version
---------------------------------------------------------------------------------------------
PostgreSQL 9.5beta2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)
CUBE とは
ROLLUP が列挙された要素を左側から絞り込んだ組み合わせの集計を計算するのに対して、
CUBE は、列挙された要素の全ての組み合わせについて集計を行います。
CUBE を使ってみる
商品の種類ごと、色ごと、およびその組み合わせの在庫の合計を例にしてみます。
with samples(id, product, stock, color) as (
values (1, 'コート', 150, 'あか'),
(2, 'コート', 100, 'あお'),
(3, 'コート', 200, 'しろ'),
(4, 'シャツ', 100, 'しろ'),
(5, 'シャツ', 200, 'しろ')
)
select
product
, color
, sum(stock) as stock_sum
from samples
group by cube (1, 2)
;
この場合だと (product, color), (product, null), (null, color), (null, null) の組み合わせの
それぞれの sum(stock) が計算されます。
product | color | stock_sum
---------+-------+-----------
コート | あお | 100
コート | あか | 150
コート | しろ | 200
コート | | 450
シャツ | しろ | 300
シャツ | | 300
| あお | 100
| あか | 150
| しろ | 500
| | 750
CUBE を使わずに書いてみる
考えられる組み合わせをすべて書いて、union すれば同じ結果が得られそうです。
with samples(id, product, stock, color) as (
values (1, 'コート', 150, 'あか'),
(2, 'コート', 100, 'あお'),
(3, 'コート', 200, 'しろ'),
(4, 'シャツ', 100, 'しろ'),
(5, 'シャツ', 200, 'しろ')
)
select -- (product, color)
product
, color
, sum(stock) as stock_sum
from samples
group by 1, 2
union
select -- (product, null)
product
, null as color
, sum(stock) as stock_sum
from samples
group by 1, 2
union
select -- (null, color)
null as product
, color
, sum(stock) as stock_sum
from samples
group by 1, 2
union
select -- (null, null)
null as product
, null as color
, sum(stock) as stock_sum
from samples
group by 1, 2
;
実行計画を見てみる
explain analyze-
CUBEを使った場合、Scan on samplesは1回だけ-
UNIONの場合、組み合わせごとに走るので4回
-
- 実行時間も結構違う（けど、複数回実行した平均を見るべきですね。データ量も変えてみたい）
CUBE
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.22..0.69 rows=16 width=68) (actual time=0.064..0.086 rows=10 loops=1)
Group Key: samples.product, samples.color
Group Key: samples.product
Group Key: ()
Sort Key: samples.color
Group Key: samples.color
CTE samples
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=72) (actual time=0.002..0.007 rows=5 loops=1)
-> Sort (cost=0.16..0.17 rows=5 width=68) (actual time=0.052..0.052 rows=5 loops=1)
Sort Key: samples.product, samples.color
Sort Method: quicksort Memory: 25kB
-> CTE Scan on samples (cost=0.00..0.10 rows=5 width=68) (actual time=0.010..0.020 rows=5 loops=1)
Planning time: 0.142 ms
Execution time: 0.151 ms
(14 rows)
UNION
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.05..1.21 rows=16 width=44) (actual time=0.074..0.077 rows=10 loops=1)
Group Key: samples.product, samples.color, (sum(samples.stock))
CTE samples
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=72) (actual time=0.006..0.011 rows=5 loops=1)
-> Append (cost=0.14..0.87 rows=16 width=44) (actual time=0.031..0.063 rows=10 loops=1)
-> HashAggregate (cost=0.14..0.19 rows=5 width=68) (actual time=0.030..0.031 rows=4 loops=1)
Group Key: samples.product, samples.color
-> CTE Scan on samples (cost=0.00..0.10 rows=5 width=68) (actual time=0.011..0.017 rows=5 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.14..0.24 rows=5 width=36) (actual time=0.009..0.011 rows=2 loops=1)
-> HashAggregate (cost=0.14..0.19 rows=5 width=36) (actual time=0.008..0.010 rows=2 loops=1)
Group Key: samples_1.product, NULL::text
-> CTE Scan on samples samples_1 (cost=0.00..0.10 rows=5 width=36) (actual time=0.001..0.003 rows=5 loops=1)
-> Subquery Scan on "*SELECT* 3" (cost=0.14..0.24 rows=5 width=36) (actual time=0.007..0.009 rows=3 loops=1)
-> HashAggregate (cost=0.14..0.19 rows=5 width=36) (actual time=0.006..0.006 rows=3 loops=1)
Group Key: NULL::text, samples_2.color
-> CTE Scan on samples samples_2 (cost=0.00..0.10 rows=5 width=36) (actual time=0.001..0.001 rows=5 loops=1)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..0.16 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
-> GroupAggregate (cost=0.00..0.15 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
Group Key: NULL::text, NULL::text
-> CTE Scan on samples samples_3 (cost=0.00..0.10 rows=5 width=4) (actual time=0.001..0.002 rows=5 loops=1)
Planning time: 0.217 ms
Execution time: 0.239 ms
(22 rows)
まとめ
ROLLUP を使ってみた記事と構成がそっくりそのままで、恐縮です。
クロス集計表を作る時は、値に紐づく属性（軸）が増えれば増えるほど煩雑になるので、
CUBE を使うことで、だいぶ書かないといけないSQLを抑えられそうです。
参考
言い訳
plmruby を 9.5 RC1 で試してみたかったのですが、
環境作ったりで思いのほか時間がかかってしまい、CUBE の紹介と利用例という内容になってしまいました。