Edited at

PostgreSQL 9.5 の ROLLUP

More than 3 years have passed since last update.


PostgreSQL 9.5 の新機能

Release 9.5 に一通り書いてあります。楽しみですね

この記事では、9.5 の新機能の中でも ROLLUP についてだけ書きます


ROLLUP とは

詳しくはマニュアルを見て頂ければと思いますが(GROUPING SETS, CUBE, and ROLLUP)、GROUP BY + 集計関数の組み合わせにおいて、親の軸の組み合わせでも集計関数が働いてくれるという機能です


PostgreSQL 9.5beta の起動

Docker を使うと、新しい環境が一瞬で入手できて、すぐに試すことができます

docker pull postgres:9.5

docker run --name pos_gre_95 -d postgres:9.5
docker exec -i -t pos_gre_95 bash

su postgres
psql


ROLLUP を試す

ひとまず叩いてみる

    WITH samples(id, name, live_in, birthday) AS (

VALUES (1, 'ほげ太郎', '東京都', '1901-01-01'::DATE),
(2, 'ふが二郎', '神奈川県', '1922-01-18'::DATE),
(3, 'ぴよ三郎', '東京都', '2000-12-24'::DATE),
(4, 'ほげ太郎の影武者', '東京都', '1901-01-01'::DATE))
SELECT live_in, date_part('year', age(birthday)) AS age, COUNT(*)
FROM samples
GROUP BY ROLLUP (1, 2)
;

 live_in  | age | count 

----------+-----+-------
東京都 | 14 | 1
東京都 | 114 | 2
東京都 | | 3
神奈川県 | 93 | 1
神奈川県 | | 1
| | 4

空欄(NULL)になってるところが小計となっています

単に GROUP BY を使っただけだと、1, 2, 4 行目の結果しか出てこないハズです

3, 5 行目に live_in で GROUP BY した時と同等の count、6 行目に GROUP BY しない時の count が出ています

ちなみに、ROLLUP を使わないで同等の結果を出そうとすると下記のようになります

    WITH samples(id, name, live_in, birthday) AS (

VALUES (1, 'ほげ太郎', '東京都', '1901-01-01'::DATE),
(2, 'ふが二郎', '神奈川県', '1922-01-18'::DATE),
(3, 'ぴよ三郎', '東京都', '2000-12-24'::DATE),
(4, 'ほげ太郎の影武者', '東京都', '1901-01-01'::DATE))

SELECT live_in, date_part('year', age(birthday)) AS age, COUNT(*)
FROM samples
GROUP BY 1, 2

UNION

SELECT live_in, NULL AS age, COUNT(*)
FROM samples
GROUP BY 1

UNION

SELECT NULL AS live_in, NULL AS age, COUNT(*)
FROM samples
;

 live_in  | age | count 

----------+-----+-------
東京都 | | 3
神奈川県 | | 1
東京都 | 14 | 1
東京都 | 114 | 2
| | 4
神奈川県 | 93 | 1
(6 rows)


QUERY PLAN

実行計画を見ると、別々に集計して UNION するクエリよりも、ROLLUP の方が若干最適化されているようです


ROLLUP の場合の実行計画

                                                                             QUERY PLAN                                                                              

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.23..0.50 rows=9 width=36) (actual time=0.040..0.043 rows=6 loops=1)
Group Key: samples.live_in, (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (samples.birthday)::timestamp with time zone)))
Group Key: samples.live_in
Group Key: ()
CTE samples
-> Values Scan on "*VALUES*" (cost=0.00..0.05 rows=4 width=72) (actual time=0.000..0.000 rows=4 loops=1)
-> Sort (cost=0.18..0.19 rows=4 width=36) (actual time=0.036..0.037 rows=4 loops=1)
Sort Key: samples.live_in, (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (samples.birthday)::timestamp with time zone)))
Sort Method: quicksort Memory: 25kB
-> CTE Scan on samples (cost=0.00..0.14 rows=4 width=36) (actual time=0.013..0.020 rows=4 loops=1)
Planning time: 0.115 ms
Execution time: 0.068 ms
(12 rows)


UNION の場合の実行計画

                                                                                   QUERY PLAN                                                                                    

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=0.98..1.12 rows=14 width=32) (actual time=0.062..0.063 rows=6 loops=1)
Group Key: samples.live_in, (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (samples.birthday)::timestamp with time zone))), (count(*))
CTE samples
-> Values Scan on "*VALUES*" (cost=0.00..0.05 rows=4 width=72) (actual time=0.002..0.003 rows=4 loops=1)
-> Append (cost=0.18..0.83 rows=14 width=32) (actual time=0.047..0.057 rows=9 loops=1)
-> GroupAggregate (cost=0.18..0.45 rows=9 width=36) (actual time=0.047..0.049 rows=6 loops=1)
Group Key: samples.live_in, (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (samples.birthday)::timestamp with time zone)))
Group Key: samples.live_in
Group Key: ()
-> Sort (cost=0.18..0.19 rows=4 width=36) (actual time=0.043..0.044 rows=4 loops=1)
Sort Key: samples.live_in, (date_part('year'::text, age((('now'::cstring)::date)::timestamp with time zone, (samples.birthday)::timestamp with time zone)))
Sort Method: quicksort Memory: 25kB
-> CTE Scan on samples (cost=0.00..0.14 rows=4 width=36) (actual time=0.018..0.024 rows=4 loops=1)
-> HashAggregate (cost=0.10..0.14 rows=4 width=32) (actual time=0.005..0.005 rows=2 loops=1)
Group Key: samples_1.live_in
-> CTE Scan on samples samples_1 (cost=0.00..0.08 rows=4 width=32) (actual time=0.000..0.000 rows=4 loops=1)
-> Aggregate (cost=0.09..0.10 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
-> CTE Scan on samples samples_2 (cost=0.00..0.08 rows=4 width=0) (actual time=0.000..0.000 rows=4 loops=1)
Planning time: 0.139 ms
Execution time: 0.124 ms
(20 rows)


ROLLUP の注意点

小計が NULL として出てくるという性質からくる問題があります

GROUP BY のキーの中に NULL の値がある場合の挙動です


普通に実行

    WITH samples(id, name, live_in, birthday) AS (

VALUES (1, 'ほげ太郎', '東京都', '1901-01-01'::DATE),
(2, 'ふが二郎', '神奈川県', '1922-01-18'::DATE),
(3, 'ぴよ三郎', '東京都', '2000-12-24'::DATE),
(4, 'ほげ太郎の影武者', '東京都', '1901-01-01'::DATE),
(5, 'もげ太夫', NULL, NULL))
SELECT live_in, date_part('year', age(birthday)) AS age, COUNT(*)
FROM samples
GROUP BY ROLLUP (1, 2)
;

 live_in  | age | count 

----------+-----+-------
東京都 | 14 | 1
東京都 | 114 | 2
東京都 | | 3
神奈川県 | 93 | 1
神奈川県 | | 1
| | 1
| | 1
| | 5

6行目以降の live_in と age が全部 NULL になっていて、何がなにやらですね。困った


COALESCE を使ってみる

NULL を他の何かに読み替えるメソッド COALESCE を使えば、区別できそうな気が

    WITH samples(id, name, live_in, birthday) AS (

VALUES (1, 'ほげ太郎', '東京都', '1901-01-01'::DATE),
(2, 'ふが二郎', '神奈川県', '1922-01-18'::DATE),
(3, 'ぴよ三郎', '東京都', '2000-12-24'::DATE),
(4, 'ほげ太郎の影武者', '東京都', '1901-01-01'::DATE),
(5, 'もげ太夫', NULL, NULL))
SELECT COALESCE(live_in, 'なし')
, COALESCE(date_part('year', age(birthday))::TEXT, 'なし') AS age
, COUNT(*)
FROM samples
GROUP BY ROLLUP (1, 2)
;

 coalesce | age  | count 

----------+------+-------
なし | なし | 1
なし | | 1
東京都 | 114 | 2
東京都 | 14 | 1
東京都 | | 3
神奈川県 | 93 | 1
神奈川県 | | 1
| | 5
(8 rows)

おー、いけたいけた

というわけで、NULLable なカラムをキーにして ROLLUP 集計するときには COALESCE 関数を付けておくといいでしょう

ということで、以上


12 月 18 日

今日はわしの誕生日なんですが、毎年「ウィッシュリストに入れてるからには読めよ」などと言いながら難しい本を送りつけて下さる方が続出しております。ほんと勘弁して欲しいですね