schooのオンライン授業でPostgreSQLの話をすることになりました。
PostgreSQLで学ぶデータベース技術という全五回の授業で、9/25(日)と9/28(水)に生放送されます。（有料会員の方はアーカイブも見れるそうなので、そちらでも。）
■本記事について
授業の第3回では、「SQL開発入門 -PostgreSQLを選ぶ理由-」として、SQLの中でもPostgreSQLならではの便利なところ、拡張が豊富でアプリ開発をより高度に行えるところ、運用管理性の向上や、SQL開発のレベルでは無く、データ連携のようなDBサーバーとしての機能まで拡張できるところを取り上げました。
その中には、PostgreSQLらしいクエリがたくさん登場し、SQLの実習講座ではないが、試せるものなら是非ためして欲しいものがたくさん登場しますので、ここに実行例を細かく書くことにします。
ここでは、テーブルを作ってデータを入れて集計してみるという一連の流れを紹介します。
■データ生成まで
・テーブルの作成
授業のデモ用なので、正規化してなかったり構造はイケてないのですが。
メインのテーブル。
アパレルの店舗（=ここではブランド名) いくらのどの商品が売れたかを格納してるテーブルだと思ってください。
テーブル作成
CREATE TABLE sales_result (
sales_id int,
sales_date timestamp,
shop text,
kind text,
price numeric,
color int);
外部キー制約のために関連する表も作成しておきますが、JOINの練習ではないので、この表は以降は登場しません。
関連オブジェクト作成
CREATE TABLE color (color_code int,color_name text);
INSERT INTO color VALUES (1,'BLACK'),(2,'RED'),(3,'WHITE');
ALTER TABLE color ADD PRIMARY KEY (color_code);
・制約の定義
PostgreSQLでは、入力されるデータを正しく保つ制約が作れます。
制約の定義
ALTER TABLE sales_result ADD PRIMARY KEY (sales_id);
ALTER TABLE sales_result ADD CHECK (shop in ('dior','Paul Smith','DIESEL'));
ALTER TABLE sales_result ADD FOREIGN KEY (color) REFERENCES color(color_code);
テーブル定義を確認するとこんな感じです。
demo=# \d sales_result
テーブル "public.sales_result"
列 | 型 | 修飾語
------------+-----------------------------+----------
sales_id | integer | not null
sales_date | timestamp without time zone |
shop | text |
kind | text |
item | numeric |
color | integer |
インデックス:
"sales_result_pkey" PRIMARY KEY, btree (sales_id)
検査制約:
"sales_result_shop_check" CHECK (shop = ANY (ARRAY['dior'::text, 'Paul Smith'::text, 'DIESEL'::text]))
外部キー制約:
"sales_result_color_fkey" FOREIGN KEY (color) REFERENCES color(color_code)
・データの投入
バルクインサートと、ON CONFLICTを含めたINSERTの例です。このようなデータを一括で投入します。
demo=# INSERT INTO sales_result VALUES
demo-# (5280,2016-07-11,'DIESEL' ,'パンツ' ,22000,2)
demo-# ,(6368,2016-02-15,'DIESEL' ,'シャツ' ,21000,1)
demo-# ,(5018,2016-01-27,'Paul Smith','シャツ' ,13000,3)
demo-# ,(7022,2015-09-29,'dior' ,'ジャケット',22000,2)
demo-# ON CONFLICT DO NOTHING
demo-# ;
なのですが、この後のクエリではある程度の行数を集計したいので、INSERTの行数を増やして、かつ手動で30回ほど実行しておきます。
数値や日付はランダムな値を入れるようになっていますので、そのまま30回繰り返しコピペで良いです。それぐらいならすぐ終わります。（とは言え、手作業でいくスタイルです。がんばってくださいｗ 一応言い訳しておくと、今回はスクリプト化とかするより、今やってることを体験するコーナーです。）
データ投入
INSERT INTO sales_result VALUES
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','ジャケット',10000+1001+(random()*100)::int%2,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','ジャケット',10000+1001+(random()*100)::int%2,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','パンツ' ,10000+2001+(random()*100)::int%3,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','パンツ' ,10000+2001+(random()*100)::int%3,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'Paul Smith','シャツ' ,10000+3001+(random()*100)::int%8,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'ジャケット',20000+1001+(random()*100)::int%2,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'パンツ' ,20000+2001+(random()*100)::int%5,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'DIESEL' ,'シャツ' ,20000+3001+(random()*100)::int%3,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'dior' ,'ジャケット',30000+1001+(random()*100)::int%2,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'dior' ,'ジャケット',30000+1001+(random()*100)::int%2,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'dior' ,'ジャケット',30000+1001+(random()*100)::int%2,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'dior' ,'パンツ' ,30000+2001+(random()*100)::int%3,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'dior' ,'シャツ' ,30000+3001+(random()*100)::int%1,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'dior' ,'シャツ' ,30000+3001+(random()*100)::int%1,(random()*100)::int%3+1),
((random()*10000)::int,(now()-(random()*1000)::int%365*interval'1day'),'dior' ,'シャツ' ,30000+3001+(random()*100)::int%1,(random()*100)::int%3+1)
ON CONFLICT DO NOTHING
;
お疲れ様でした★
■クエリを実行する
・普通のSELECT文
まずは、INSERTした件数と、どんなデータが入ったか上位5件とか、テーブルの物理サイズを見ておきましょう。
demo=# SELECT count(*) FROM sales_result;
count
-------
1159
(1 行)
demo=# SELECT * FROM sales_result LIMIT 5;
sales_id | sales_date | shop | kind | price | color
----------+----------------------------+------------+------------+-------+-------
9396 | 2015-09-26 16:23:57.248001 | Paul Smith | ジャケット | 11001 | 3
5179 | 2016-05-13 16:23:57.248001 | Paul Smith | ジャケット | 11002 | 3
3925 | 2016-01-24 16:23:57.248001 | Paul Smith | パンツ | 12003 | 1
6720 | 2016-01-17 16:23:57.248001 | Paul Smith | パンツ | 12001 | 2
9054 | 2016-01-29 16:23:57.248001 | Paul Smith | シャツ | 13006 | 3
(5 行)
demo=# SELECT pg_size_pretty(pg_relation_size('sales_result'));
pg_size_pretty
----------------
88 kB
(1 行)
・集計してみる
さて集計編。まずは基本のGROUP BYです。2種類試します。
集計する項目を「売上」か「件数」かで見比べます。単価が高いものがぽつぽつとでも売れたほうが、売上額では勝るんですね。世の中って複雑ですｗ
売上で集計するクエリ
SELECT
sales_date::date as "売上日"
,shop as "ブランド"
,sum(price) as "売上"
FROM sales_result
GROUP BY "売上日","ブランド"
ORDER BY "売上" DESC
LIMIT 10;
販売点数で集計するクエリ
SELECT
sales_date::date as "売上日"
,shop as "ブランド"
,count(*) as "件数"
FROM sales_result
GROUP BY "売上日","ブランド"
ORDER BY "件数" DESC
LIMIT 10;
demo=# SELECT
demo-# sales_date::date as "売上日"
demo-# ,shop as "ブランド"
demo-# ,sum(price) as "売上" --★ここだけ異なる
demo-# FROM sales_result
demo-# GROUP BY "売上日","ブランド"
demo-# ORDER BY "売上" DESC
demo-# LIMIT 10;
売上日 | ブランド | 売上
------------+------------+--------
2016-03-12 | dior | 129007
2016-09-08 | dior | 99003
2015-10-12 | dior | 97003
2016-04-21 | DIESEL | 89015
2016-03-03 | DIESEL | 88018
2015-10-22 | DIESEL | 88011
2016-09-02 | DIESEL | 87009
2016-07-16 | Paul Smith | 78029
2016-06-20 | DIESEL | 67011
2016-06-21 | DIESEL | 67009
(10 行)
demo=# SELECT
demo-# sales_date::date as "売上日"
demo-# ,shop as "ブランド"
demo-# ,count(*) as "件数" --★ここだけ異なる
demo-# FROM sales_result
demo-# GROUP BY "売上日","ブランド"
demo-# ORDER BY "件数" DESC
demo-# LIMIT 10;
売上日 | ブランド | 件数
------------+------------+------
2016-07-16 | Paul Smith | 6
2016-06-14 | Paul Smith | 5
2015-12-28 | Paul Smith | 5
2016-02-09 | Paul Smith | 5
2016-06-05 | Paul Smith | 5
2016-09-21 | Paul Smith | 5
2016-06-16 | Paul Smith | 5
2016-04-07 | Paul Smith | 5
2016-07-31 | Paul Smith | 5
2016-09-15 | Paul Smith | 5
(10 行)
・ランキング（window関数）
次はいきなりPostgreSQL力の高い例題ですが、window関数を試します。
上記の集計結果にランキングをつけます。 あるブランドでは、何月何日が一番売れたかが見れます。結果はめちゃくちゃ長いので頑張ってスクロールしましょうｗ
ランキングを取得するクエリ
SELECT *,
rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
SELECT
sales_date::date as "売上日"
,shop as "ブランド"
,sum(price) as "売上"
FROM sales_result
GROUP BY "売上日","ブランド") AS "集計"
;
demo=# SELECT *,
demo-# rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
demo-# FROM (
demo(# SELECT
demo(# sales_date::date as "売上日"
demo(# ,shop as "ブランド"
demo(# ,sum(price) as "売上"
demo(# FROM sales_result
demo(# GROUP BY "売上日","ブランド") AS "集計"
demo-# ;
売上日 | ブランド | 売上 | 順位
------------+------------+--------+------
2016-04-21 | DIESEL | 89015 | 1
2016-03-03 | DIESEL | 88018 | 2
2015-10-22 | DIESEL | 88011 | 3
2016-09-02 | DIESEL | 87009 | 4
2016-06-20 | DIESEL | 67011 | 5
2016-06-21 | DIESEL | 67009 | 6
:
（結果は省略）
・ピボットテーブル（GROUPING集計）
もうちょっと簡単な集計を例にします。どのブランドのどの服が何個売れたかを出します。
しかし、RDBの結果はいかんせん見づらい。アプリでステキに表示するために、よく行列変換を施します。
demo=# SELECT
demo-# shop AS "ブランド"
demo-# ,kind AS "種類"
demo-# ,count(*) AS "数量"
demo-# FROM sales_result
demo-# GROUP BY "ブランド","種類";
ブランド | 種類 | 数量
------------+------------+------
Paul Smith | シャツ | 538
dior | シャツ | 86
DIESEL | シャツ | 30
dior | ジャケット | 89
dior | パンツ | 30
DIESEL | ジャケット | 29
DIESEL | パンツ | 299
Paul Smith | ジャケット | 61
Paul Smith | パンツ | 61
(9 行)
この結果をまるっとExcelに張り付け、「ピボット変換」機能を使うと、各中計と合計を出して見やすくしてくれます。
PostgreSQLでは、この中計や合計の値を一回のクエリで取ることができます。従来のGORUP BYでは、合計で一回、ブランド別の集計で一回、種類別の集計で一回のクエリを発行する必要がありました。最新のPostgreSQLでは、GROUPING SETS、ROLLUP、CUBEといくつか構文があり、取得したい集計によって使い分けます。その一番基本となるGROUPING SETSを今回は例にしています。ちなみに、CUBEやROLLUPを使うと、中計、合計だけでなく、普通にGROUP BYした結果も一緒に取れるので、Excelのピボット変換したのと同じ結果が1クエリで取れます。
中計と合計を取得するクエリ
SELECT
shop AS "ブランド"
,kind AS "種類"
,count(*) AS "数量"
FROM sales_result
GROUP BY GROUPING SETS (("ブランド"),("種類"),());
結果は以下のようになります。上記のEXCELのピボット変換して得た値（中計や合計）がSQLで取れています。（例えば「合計」の1223という値がちゃんと取れています。）
demo=# SELECT
demo-# shop AS "ブランド"
demo-# ,kind AS "種類"
demo-# ,count(*) AS "数量"
demo-# FROM sales_result
demo-# GROUP BY GROUPING SETS (("ブランド"),("種類"),());
ブランド | 種類 | 数量
------------+------------+------
DIESEL | | 358
Paul Smith | | 660
dior | | 205
| | 1223 --★この合計はExcelで計算されたものと一致
| シャツ | 654
| ジャケット | 179
| パンツ | 390
(7 行)
・行列の表示変換（crosstab）
なお、これはオマケなのですが、これをさらに見やすく整形し、本当にピボット変換したのと同じ表をつくる整形用の拡張もあります。
拡張を入手できるかはインストールした環境次第なので、ここではそのまま実行例を貼っておきます。EXCELの例とおなじ結果SQLを整形だけしたものです。（この時点で中計や合計は計算していない）
postgres=# CREATE EXTENSION tablefunc;
CREATE EXTENSION
demo=# SELECT * FROM crosstab(
demo(# 'SELECT
demo'# shop
demo'# ,kind
demo'# ,count(*)
demo'# FROM sales_result
demo'# GROUP BY 1,2
demo'# ORDER BY 1,2'
demo(# ,
demo(# 'SELECT
demo'# DISTINCT kind FROM sales_result
demo'# ORDER BY kind'
demo(# )
demo-# AS results(
demo(# "ブランド" text,
demo(# "シャツ" numeric,
demo(# "ジャケット" numeric,
demo(# "パンツ" numeric
demo(# );
ブランド | シャツ | ジャケット | パンツ
------------+--------+------------+--------
DIESEL | 30 | 29 | 299
Paul Smith | 538 | 61 | 61
dior | 86 | 89 | 30
(3 行)
すげー複雑！と思った方が多いと思いますが、SQLが複雑ということは、その裏でデータベースはもっと頑張って、取得する行数の見積もり、実行計画の策定、スキャン、集計、などをこなしているのです。複雑なSQLを書けるということは、賢いデーターベースであることの証でもあります。
PostgreSQL 9.6では、\crosstabviewという整形用メタコマンドが追加されました。直前に実行されたSQLを整形してくれます。
メタコマンドなのでpsql大好きっ子的には嬉しいこの機能。一般ウケはし無さそう。
schoo=#
SELECT
shop AS "ブランド"
,kind AS "種類"
,count(*) AS "数量"
FROM sales_result
GROUP BY CUBE( "ブランド", "種類");
ブランド | 種類 | 数量
------------+------------+--------
DIESEL | シャツ | 9856
DIESEL | ジャケット | 9856
DIESEL | パンツ | 93568
DIESEL | | 113280
Paul Smith | シャツ | 180352
Paul Smith | ジャケット | 20096
Paul Smith | パンツ | 20096
Paul Smith | | 220544
dior | シャツ | 29696
dior | ジャケット | 30592
dior | パンツ | 10496
dior | | 70784
| | 404608
| シャツ | 219904
| ジャケット | 60544
| パンツ | 124160
(16 行)
schoo=# \crosstabview "ブランド"
ブランド | シャツ | ジャケット | パンツ |
------------+--------+------------+--------+--------
DIESEL | 9856 | 9856 | 93568 | 113280
Paul Smith | 180352 | 20096 | 20096 | 220544
dior | 29696 | 30592 | 10496 | 70784
| 219904 | 60544 | 124160 | 404608
(4 行)
あと、こういういかにも重いクエリが、パラレルクエリでチョッパヤになります。夢が膨らむ！
schoo=# explain analyze
SELECT /*+ PARALLEL(sales_result 8) */
shop AS "ブランド"
,kind AS "種類"
,count(*) AS "数量"
FROM sales_result
GROUP BY CUBE( "ブランド", "種類");
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
----------
GroupAggregate (cost=83465.04..128227.47 rows=32 width=26) (actual time=447.751..883.691 rows=16 loops=1)
Group Key: shop, kind
Group Key: shop
Group Key: ()
Sort Key: kind
Group Key: kind
-> Sort (cost=83465.04..84476.56 rows=404608 width=18) (actual time=441.059..503.051 rows=404608 loops=1)
Sort Key: shop, kind
Sort Method: quicksort Memory: 42643kB
-> Gather (cost=1000.00..45783.56 rows=404608 width=18) (actual time=1.582..192.417 rows=404608 loops=1)
Workers Planned: 8
Workers Launched: 7
-> Parallel Seq Scan on sales_result (cost=0.00..4322.76 rows=50576 width=18) (actual time=0.017..11.969 rows=50576
loops=8)
Planning time: 0.245 ms
Execution time: 891.296 ms
(15 行)
ということで、単にSQLをぺたぺた貼って行っただけですが、このぐらいで。
