2016年9月21日水曜日

【schoo連動】PostgreSQLらしい集計クエリ

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回繰り返しコピペで良いです。それぐらいならすぐ終わります。(とは言え、手作業でいくスタイルです。がんばってくださいw 一応言い訳しておくと、今回はスクリプト化とかするより、今やってることを体験するコーナーです。)

データ投入
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種類試します。
集計する項目を「売上」か「件数」かで見比べます。単価が高いものがぽつぽつとでも売れたほうが、売上額では勝るんですね。世の中って複雑ですw

売上で集計するクエリ
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関数を試します。
上記の集計結果にランキングをつけます。 あるブランドでは、何月何日が一番売れたかが見れます。結果はめちゃくちゃ長いので頑張ってスクロールしましょうw

ランキングを取得するクエリ
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をぺたぺた貼って行っただけですが、このぐらいで。

0 件のコメント:

コメントを投稿

PostgreSQL11のJITコンパイリングを試す

llvm-postgres 開発中のPostgreSQL11でJIT(Just In Time=実行時)コンパイリングを行い、クエリ性能の高速化を期待する新機能が登場した。 本記事では 構築方法を確認したので紹介。JITコンパイ...