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をぺたぺた貼って行っただけですが、このぐらいで。

【schoo連動】PostgreSQLで学ぶデータベース技術

※ 本記事はポエムかつ炎上するような物でも無いのでまったく読む必要ないやつです ※


schooのオンライン授業でPostgreSQLの話をすることになりました。

PostgreSQLで学ぶデータベース技術という全五回の授業で、9/25(日)と9/28(水)に生放送されます。(有料会員の方はアーカイブも見れるそうなので、そちらでも。)

■データベース技術とは

タイトルに使った「データベース技術」という言葉について。

授業の内容を考えながら、SQLの解説では言葉だけの説明では限界があるし、全体で10話したとしても、PostgreSQLならではのことって3以下ぐらいにしかならないよなーとか思っていました。schooさんではこの夏、数回にわたりSQLやデータベース理論に関する授業が公開されていたそうなので、PostgreSQLに絞っていっても相当に重複するであろうという懸念もありました。
それでも、SQLは学んだはずなのに、(むしろ普段から書いてるのに)得意とは言えない、思うようにパフォーマンスがでない、という相談は私も日々いただいているのです。特に、schooさんで対象としている学生の多くは「webアプリ開発者を目指す(または駆け出し)」とか「社会人数年目」だったりするので、ここにまだ学びの余地があると考えました。
データベースを使う人の立場で、
  • 最初にこれを勉強しておけば苦労が減るだろうな
  • 何かあったときにググるために、脳内のインデックスを作れてるといいのにな
というゴールを設定して、広く浅く易しく、「RDBのことを勉強した気になれる」講座にしたいと思いました。こういうテーマでは、RDBが本来満たすべき領域を質実剛健に、しっかりとカバーしているPostgreSQLは最適です。

つまり、「RDBが本来満たすべき領域」のことをひっくるめて「データベース技術」としました。


■授業をつくってみて

「データベース技術」の中には、トランザクションのこともあるし、SQL言語のこともあるし、セキュリティのこと、バックアップのこと、いろいろあるでしょう。そしてそれらは、きちんと実装されているPostgreSQLにとっては当たり前のことだし、おそらく意識しなくてもある程度使うことはできてしまっていることでもあります。これらに光をあて、でも、難しさよりも、勉強した感を大事に、易しく扱う内容を目指しました。

さて、そんな感じで準備をしてきたのですが、 PostgreSQLを使う人(=アプリ開発者)にとって、RDBを知っているとどんなところが有利なのか、PostgreSQLならではのどういうところが便利なのか、そういう点をちゃんと紹介するのは、私にとっても普段から(DB管理者よりで、移行、障害対応やDBA教育をやっている)身近に扱う話題ではないので、とても勉強になりました。

作っていると、 細かい言葉の意味だったり、他のDBのことが気になってきたり、図の細かいところが気になったりw それでいて、説明に最適な実行例を考えるのはめんどくさくなってみたり。いろいろ雑念が生まれてくるのですが、そういうのもだんだん楽しくなってきて、最終的には手抜きしない内容にできたと思います。
仕事ではOpen Office、Libre Office慣れして数年やってきたのですが、Power Point on Windows10 対応(自分がw)したことも良かった点です。

■と、いうわけで。

そんなシルバーウィークを過ごして、今は資料を提出した喜びでこんなポエムを詠っているわけですが、せっかく作ったのでいろんな人に見てもらいたいし、ツッコミどころがあったら指摘してもらってより良くしていきたいし、良くなったものを今後もどこかで話したりして、PostgreSQLの普及、製品問わずRDBスキルの向上、Webアプリだけじゃなくていろんなシステムへの応用など、いろんなところに少しでもお役立ちできたらなーと思っています。

それでは、本番まであと数日、私も楽しみにしています!

2016年9月10日土曜日

第8回 PostgreSQLアンカンファレンスに参加しました

PostgreSQLアンカンファレンスに参加してきました。
参加っていうか運営?これに関しては参加者気分もとてもありつつ、運営も楽しくやらせていただいているのです。何より日程が会場優先で、俺得になっていてもう幸せ☆


※ところでこのゾウの看板を連日つくっていたら、急激に可愛く見えてきたんですがどっかの病気でしょうか?目つきが悪いとか良く言われているが、前から肯定的な気持ちでは見ていました。でもこんな気持ちになるなんて。


■PostgreSQLアンカンファレンスについて


PostgreSQLアンカンファレンスは、何が出るかは当日来てのお楽しみで、いろんな興味の範囲をそれぞれに持った人たちがPostgreSQLという共通点に導かれて集まり(大げさ?)、発表し、聞きあう場所です。
「PostgreSQLについてしゃべりたいことがある、聞いてほしいことがある!」
「PostgreSQLについて聞いてみたい、相談したい!」
「PostgreSQLに興味があるのでとりあえず参加してみたい」
そんな、色んな人たちを受け入れてくれるイベントです。
今回はそのPostgreSQLアンカンファレンスの第8回でした。
毎年秋はPostgreSQLの新バージョンがリリース目前で期待が高まっている時期であり、更に次のバージョンの機能デザインが決まり始めていたりで、かつもう少し後ろになるとITイベント目白押しになってしまうので、だいたいこの時期にやっている気がします。

1~2月

次バージョンで取り込む機能を決める数回のCommit Festを経て、PostgreSQL本体開発しているみなさまが燃えている時期。一番ホットな話が聞けたり

5~7月

新バージョンのα?βがでるか?ぐらいの時期で、つまりあらかた固まってきた新バージョンをいち早く試した人たちの発表が聞ける。仕事にしている身としてはとても勉強になる回

9~10月

今回のように、新バージョンのリリースが迫ってきて、そろそろユーザー目線の「新機能はこうやって使うといいよ」みたいな話が出てきたり。

あとは一年を通して、実際にサービスで使っている方がシステム開発/運用/アップグレード/性能などの話題で経験談を話してくれたりしています。


■今日のセッション


どーん!



・PostgreSQLはグラフデータベースの夢を見るか(ぬこ@横浜さん)
 Neo4jをPostgreSQLから触る話。更新はロジデコ→JSON→Cypher→RESTで。参照はNeo4j_fdwで。

・俺のキュー(っぽいもの) (@aoyagikouheiさん)
PostgreSQL以外のミドルをいれてメンテしたくないので、キューっぽいデータ構造を自作した話。

・ポスグレとスクワットと私(永安さん)
話題の健康法「30日スクワットチャレンジ」をPythonとかBlumixとかSoftlayerとかPostgreSQLでWebシステム化している話。そして懇親会2次会ではそこに秘められたアツい想い、黒い想いをたくさん聞けたのであった笑

・PostgreSQLを用いた動画検索エンジンMoovieおよびMoovie TEOのシステム概要(内山さん)
※情報求む

・PostgreSQLのエコシステム/Windowsでのチューニング/メモリ暴発 防止のヒント(綱川さん)
諸事情あってmaumau名義で活動していたが表にでれる事になったとの自己紹介。開発者としてもだけど、自分的にはMLの救世主maumauさんにお目にかかれて感無量です。
Windows環境では無意味な"update_process_title"パラメータをoffると性能がとても良くなる。
ODBCドライバで全件フェッチする問題があるのでドライバの設定を見直すと良い。
PostgreSQLが対応できるソフトウェアを調査し開発本家のMLに掲載している(→JPUGでも是非その活動を後押ししていきたい!と申し出たところ快く受け入れていただいた!)

・自動フェイルオーバー(澤田さん)
PostgreSQL自身はレプリ構成にしても相手ノードの監視やフェイルオーバーの判断をしないが、それを作っている話?
※情報求む

・CacooのPostgreSQLを9.3から9.5にアップグレードした話
Webサービスで使っている3TBのデータベースクラスタを9.3から9.5にアップグレードした話。停止時間を短く、失敗時のリスクを軽くするオペレーションをちゃんと考えつつ、PostgreSQL的な手順に関して言えばpg_upgradeのマニュアル通りで上手く行きました!という報告。
※情報求む

・PostgreSQLと他のDB(MySQL、Oracle)との違い※非技術的 (北山さん)
 いくつかのコミュニティに参加してきて、とか、PGEConsの活動について、など思うトコロをいろいろ。(懇親会でいろいろ話しすぎてどこまでがセッションの内容だったか忘れてしまったっっ・・不覚)

・pg_stats_reporterで読影会をやってみた(@masudakzさん)
※情報求む

・とあるサービスでのパフォーマンス障害とその対策を赤裸々に報告(桑田さん)
※情報求む

・9.6パラレルやってみたよ(喜田)
集計系クエリ(DWHや夜間バッチ)で効果を期待される9.6の新機能「パラレルクエリ」を実際に近いベンチマークで試してみました、と言う話。
PostgreSQLが昔から注目されるイチ要素であった「テーブル・パーティショニング」との組み合わせで、元々10分かかっていたクエリが7秒に、1時間かかっていたクエリが5分になった。やっぱOracleすごいね、という要素も多々見つかったが、使い方をそれなりにPostgreSQL向けに工夫すると同等の結果まで頑張れる!

・私のストレージ チョットカシコイ(海外さん)
 ※情報求む

・PostgreSQLでデータ分析 10の理由(永安さん)
ユーザーの目線を考えると「データ分析したい!今ならどの技術が適してるんだろう?」という動機で言語なり製品を選ぶのであって、目的に合う事がわかればどのDBでもいいのだが、いろんな要素から今ならPostgreSQLを推すよ、と言う話。
パラレルできるようになって分析クエリが早いだけじゃなくて、FDWでデータを取ってくるところができるとか、「データ分析」に必要とされるいろんな要素を満たせる。

・FDW使える?(山田さん)
 ※情報求む

・PostGIS Bundleで循環セールスマン問題(TSP)(国府田さん)
※情報求む

・SSDハック(海外さん)
※情報求む

あれ、最後の方なんだか聞けてないな。。。
それぞれ見つかり次第、資料をリンクしていくつもり。


■懇親会


上海ブギにて。ブギ様のマーボー丼はうますぎる。
PostgreSQL界の人はtwitterしない問題
若者をとりこむ策(若者とは?)
今日のアンカンファレンスを思い出しながら、次の勉強会のネタ考案とか

二次会は魚八にて。
永安さんのスクワットアプリ開発秘話や、シンガポールでのイベント開催秘話をブラック永安が出てきていろいろ語っていただいた。がんばりたいと思った。

2016年9月9日金曜日

Database Lounge Tokyo #2 に参加しました

Database Lounge Tokyo #2 に参加しました。
今回はバックアップ回で、きっとメインのパネルディスカッションは壮絶な感じなんだろうなーと誰もが予感していたと思いますが、ネタとしては基本中の基本の範囲でカバーしておくべきでもあり、データベースに関わる人すべてが(少なくとも自分が触っているデータベースに関して)きちんと理解しているべき内容です。
というわけで、弊社の新人ちゃんも誘って参加してみました。

イベントの流れは、Lighting Talksがババッとあって、その後、バックアップ・リカバリ観点でのOracle DB、PostgreSQL、MySQLの比較の話と、それを肴にパネルディスカッション。
イベントページで録画や発表資料が公開されると思うので、現時点では記憶を頼りにザザっと書いています。ゆるっと追記・修正していく予定。

■Lighting Talks


・ 「差分、増分、デルタ(って何?) バックアップあれやこれや」 by @meijik


firebirdやMySQLでのバックアップ手法について。
特に差分や増分というデータベース製品によって意味するところが異なるキーワードを解説されていました。今日の一発目として、基本のおさらいだったり、知らない人的には「ほー」っと言う感じ。アーカイブ(トランザクションログ)を適用する話がそもそも増分と呼ばれていたり、文化の違いを感じる。

・「Postgresはグラフデータベースの夢を見るか?」 by @nuko_yokohama


グラフデータベース neo4j を PostgreSQL のインターフェース で扱っちゃう話。
PostgreSQLではロジカルデコーディングという、論理WALを吐くことができるので、PostgreSQLに対して実行したクエリを別のデータベースに伝えて実行させることが可能である。それを受信する側の機構を自作する必要があるが。今回は論理WALをjson型に変換→Cypherクエリ→REST APIでneo4jに突っ込む。
データを参照する方はneo4j_fdwで。PostgreSQLはForeign Data Wrapperという、外部データソースからデータを取ってくる(実装によっては更新も可)基盤があるが、データソース、今回で言うとNeo4jに対応したものを作らないといけない。と思ったら、以前にぬこさん自身が作ったものが公式Wikiにまで掲載されいている!

・「An intelligent storage?」 by @kkaiga


実は運営などバタバタしていて全然聞けなかった。。。
GPUの超メニーコアを使った高速化をやり続けている海外さんの取り組み。フラッシュ上に配置したデータを、CPUがちょろちょろGPUに渡しているとCPUがボトルネックになってしまうので、直接渡せると嬉しいよね!という話(だったと思う。資料が公開されたらちゃんと読む。)

・「ポスグレのバックアップでやらかした話 (˚இ˚) 」 by @kkkida_twtr


僭越ながら発表させていただきました。弊社では、取り扱う製品を自分たちで使ってみて、酸いも甘いも経験して、ちゃんとノウハウを得てからサービス提供してるんですよ!という美しいお話でした。(ウソ)
バックアップ取得先の容量管理とか、シェルのロジックとかの問題で、消し込みがうまく回らずPostgreSQLのアーカイブ領域が埋まる→新規のWALが書けずにDB停止、というのを実際にやっちゃうと運用担当者(俺)がパニくるんですよ。とか。VACUUM FULLの後にVACUUMやってるから意味無くね?って言ってコメントアウトしてみたらANALYZE止めちゃって本来5分のクエリが2時間かかったとか。そんな情けない話でも共有してみんなを救いたい!っていう健気な俺ちゃんのゆるふわな発表でした。

■メインテーマ


・ 「バックアップと障害復旧から考えるOracle Database, MySQL, PostgreSQLの違い」 @wrcsus4


実はお仕事などバタバタしていてあんまり聞けなかった。。。
各データベースのオンラインバンクアップ(アーカイブ運用とか、ポスグレ界でいうところのPITR前提の取り方)について比較し、酔った勢いでMySQLを変わり者と評する感じの流れw。と思いきや、早々にマサカリが飛びかっていることをtwitterで知りあわてて会場に戻りましたw
途中をいろいろ端折って抱いたイメージは、MySQLはOra、Posの当たり前が通用しないけど、論理バックアップにログを適用して最新に追いつけるよ?的な?それはそれで便利そうではある。(取る人は楽ちん、戻す人は大変そうだけど。)
これも資料公開されたらちゃんと勉強する。と思ったけど↑は本題ではないようだwマサカリおそろしいwww

・ディスカッション@wrcsus4 @yoku0825 @kasa_zip @kumagi


テーマが5個ぐらいあったけどメモれず。こういう用途でどんな方法でバックアップ取るか、どう戻すか、みたいなパターンがいくつか。
印象的だったのは、MySQLはスレーブから戻すケースが多くて、「死んだノードで整合性を保った状態で復旧せよ」みたいな問題を考えるほうが珍しいということ。
あとは、PostgeSQLで長年疑問に思っていた、「トランザクション位置を指定したPITRって需要どんだけあるんだろうか?」に対して「普通xid記録してないよね。」ということが分かった。収穫。

全体的に、新たに知ったことや、へーっと思ったことは記憶に残ってるのでMySQLのメモが多いが、PostgreSQLやOracleについてももちろんたくさん話がでていました。総じて両者は似ている。フラッシュバック憧れる。(どれだけ使うかは別として。)
そして@kumagiさんの容赦ない切り口。いろんな経験の人がしゃべってるの面白いなーと思いました。


■その他

今回は、弊社で会場提供して、その活動報告を弊社の広報ブログで取り上げる予定です。
そのために皆さんにも写真撮影にご協力いただきました。きっとキャッキャウフフした感じが撮れていると思いますので、記事の公開を楽しみにしておいてください。

次のテーマは「トランザクション」とのこと。
学術的な方面の人たちがフィーバーしてそうで今から楽しみです。

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

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