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