530日の第5回アンカンファレンスpgAdmin、PL/Python、JSONBなど聞きたい話が多かったので、あとスライドをやっつけで作り終えていたので発表を最初にして、お得な情報をたくさん仕入れてきました。もう出てるぬこさん、するめごはんさんのまとめと被らない点中心に、忘れないうちにメモメモ…。

PL/PythonWEBアプリ
[アシスト Y田さん]
これはすごい、「SELECT PL/Pythonのストアド関数;」でWebサーバとアプリを立ち上げる話。bottoleという超軽量なPythonWebフレームワーク(初めて知った)をPL/Pythonで使い、HTML等もデータベースに入れて、ポスグレだけでWebアプリが動いてる状態に。当然ながらストアドの実行は一つのトランザクションなので停止処理やデータ更新等で無理めな所もあるけど、静的コンテンツを返すだけなら結構行けるんじゃないかという話もあり。自分が最近関心あるPythonWebSocketについて聞けたのもよかったです。追記:スライドが公開されてました

pgAdminⅢを使おう!
[チョコレートバーさん]
「すごく便利なんだけどちょっと癖がある」pgAdminを、もっと便利に使えるというお得情報いっぱい。オブジェクトブラウザでサーバのグループを自由に設定できるとは知らなかった!明日やる!これもそうだけど、コピー時の区切り文字設定とか、セレクトボックスになっていて一見プリセットしか選べないと思いきや、入力してみると自由に変更できる所がpgAdminに結構あって、そういう癖を知るともっと便利に使えると分かりました。ファイル出力時の区切り文字選択にタブがなく\tを入れても駄目、でもタブを直接コピペするとちゃんと出来てるデモにフロア爆笑ww

ポスグレ向けマイグレーションツール
[kwatchさん]
2013年カンファレンスのLTでも話していたデータベースのバージョン管理を、Rubyでツール化してGitHubで公開されているという、これまた有益な情報。Rubypsqlとエディタがあれば使える手軽さと、いろんなケースで矛盾が起きないための細かい設計が両立されてました。バージョン番号にタイムスタンプを使わないのでDDL・DMLの適用順序が時系列にしばられない等。詳細はGitHubを見て下さい。SQliteMySQLにも対応しています。スライドのフロー図も分かりやすく、さすが始まる前にカフェド■リエで作業していただけのことはある…自分は涼んでただけ…

そのほか
発表時間が重なってカワラさんの「HOW TO <今日」を聞けなかったのが残念。意外にアンカンファレンスではSQLそのものの比較やハウツーが少ないので。ぬこさんのまとめが詳しくて有難いです。JSON(B)は、自分が使ったGoogle Analytics APIもそうだけど世間に出回ってるのが割と正規的データというか、通常のテーブルと互換可能なのが多そうで、それだとJSONのまま使う意味や実際使う場面は?って感じも。本当に非正規的で大きいデータでJSON(B)の実力が知りたいなぁ、今後調べてみようと思いました。

PostgreSQL 9.5の新しいGROUP BY
自分の発表。一応スライドはこちらですが、やっつけだったのでHTMLの方が便利なので、以下リライト。

[参考]
»PostgreSQL 9.5devel Documentation : GROUPING SETS, CUBE, and ROLLUP
»PostgreSQL Wiki : Grouping Sets(CUBE, ROLLUPの説明もあり)
»DSHL Blog : Waiting for 9.5 - Support GROUPING SETS, CUBE and ROLLUP.

[テスト環境]
»CentOS 6.6(VirbualBox上)
»PostgreSQL 9.5dev(
スナップショット526日付からビルド)

[使ったデータ]
»JSON(Google Analytics APIで取得したこのブログへのアクセス今年分、4.7MB、4レコード、UTF-8)
»TABLE TSV(上記JSONから作ったテスト用テーブルをTSV出力、約3.4万行、3.4MB)

[準備 : JSONからテスト用テーブル作成]
-- 一時テーブル作成
-- JSON をデータディレクトリに置いて pg_read_file で読み込み
-- string_to_array で改行で分けて配列化、unnest で一行ずつに変換
-- 最後に JSONB にキャスト
--
CREATE TABLE hoge AS
SELECT  unnest(
    string_to_array(pg_read_file('20150530_example.json'), E'\n')
) :: jsonb;


-- GROUP BY クエリ用テーブル作成
-- JSONB のデータ部分をキー rows で取り出す. 結果は2次元配列
-- まず jsonb_array_elements で1次元(行)に変換
-- 次に ->> 演算子で列に変換、必要に応じてキャストや不要文字削除
-- 
CREATE TABLE test.ga_tb2 AS
SELECT
    ( ary->>0 ) :: date AS ymd,  -- 添字が0から(配列型は1から)
    ( ary->>1 ) :: int AS h24,
    ary->>2 AS country,
    ary->>3 AS os,
    ary->>4 AS browser,
    ary->>5 browser_version,
    replace (ary->>6, ' : 研究に使うポスグレ', '') page_title,
    ( ary->>7 ) :: int page_views
FROM (
    SELECT jsonb_array_elements(jsonb_dat -> 'rows') AS ary
    FROM hoge
) foo;

上の2クエリで ↑ このJSONが ↓ こんなテーブルになる

ここから本題で、上のテーブルを使ってPostgreSQL 9.5の新しいGROUP BYの説明。テーブルにある列のうち、主にOSとブラウザでの集計を例にしました。まず下は、普通のGROUP BYを使ってOS別・ブラウザ別のページビューを集計するクエリとその結果。
ordinary_group_by.sqlSelectRawtextBitbucket
SELECT os, browser, sum(page_views) FROM test.ga_tb2
GROUP BY os, browser
HAVING sum(page_views) > 20 -- 見やすさのため主要なものに限定. 以下のクエリも同じ
ORDER BY os, browser;

    os     |      browser      |  sum
-----------+-------------------+-------
 Android   | Android Browser   |   254
 Android   | Chrome            |   472
 Android   | Firefox           |   232
 Android   | Opera             |    73
 iOS       | Chrome            |    41
 iOS       | Safari            |   625
 iOS       | Safari (in-app)   |    37
 Linux     | Chrome            |   301
 Linux     | Firefox           |   692
 Macintosh | Chrome            |  1392
 Macintosh | Firefox           |   409
 Macintosh | Safari            |   897
 Windows   | Chrome            | 15377
 Windows   | Firefox           |  6492
 Windows   | Internet Explorer | 12264
 Windows   | Iron              |    84
 Windows   | Opera             |   359
(17 rows)


下が、9.5で追加される予定の一つGROUP BY
GROUPING SETS (列名,列名,)という構文の簡単な例です。括孤内にOSとブラウザの列名を指定すると、先ほどの「OS別・ブラウザ別」でなく、OSとブラウザを別々に集計し、列をずらして縦にUNIONした結果に。例えば1行目「Android, , 1036」は、ブラウザを問わずAndroidからの全ページビューを意味しています。いわばGROUP BYを「列ごとの集計行に変える」感じ。
ex1_grouping_sets.sqlSelectRawtextBitbucket
SELECT os, browser, sum(page_views) FROM test.ga_tb2
GROUP BY GROUPING SETS (os, browser) -- ここ!
HAVING sum(page_views) > 20
ORDER BY os, browser;

    os     |      browser      |  sum
-----------+-------------------+-------
 Android   |                   |  1036
 iOS       |                   |   703
 Linux     |                   |  1008
 Macintosh |                   |  2730
 Windows   |                   | 34628
           | Android Browser   |   254
           | Chrome            | 17598
           | Firefox           |  7834
           | Internet Explorer | 12265
           | Iron              |   101
           | Opera             |   460
           | Safari            |  1533
           | Safari (in-app)   |    37
(13 rows)


同じ結果を普通のGROUP BYでやると、下のように結構大変です。WITH句を使ってテーブル指定、HAVING、ORDER1回で済ますこともできますが、やりたくない…。
ex1_before_95.sqlSelectRawtextBitbucket
(
SELECT os, '' :: text browser, sum(page_views) FROM test.ga_tb2
GROUP BY os
HAVING sum(page_views) > 20
ORDER BY os
)
UNION ALL 
(
SELECT '', browser, sum(page_views) FROM test.ga_tb2
GROUP BY browser
HAVING sum(page_views) > 20
ORDER BY browser
);


先ほどのGROUPING SETSの括孤内に
( )という謎の要素を追加すると、どの列でも絞り込まない「総計行」を足す結果に。下のようにOS別・ブラウザ別という小計(部分計)と総計を、一回のクエリで得られます。
ex2_grouping_sets.sqlSelectRawtextBitbucket
SELECT os, browser, sum(page_views) FROM test.ga_tb2
GROUP BY GROUPING SETS (os, browser, ()) -- ここ!
HAVING sum(page_views) > 20
ORDER BY os, browser;

    os     |      browser      |  sum
-----------+-------------------+-------
 Android   |                   |  1036
 iOS       |                   |   703
 Linux     |                   |  1008
 Macintosh |                   |  2730
 Windows   |                   | 34628
           | Android Browser   |   254
           | Chrome            | 17598
           | Firefox           |  7834
           | Internet Explorer | 12265
           | Iron              |   101
           | Opera             |   460
           | Safari            |  1533
           | Safari (in-app)   |    37
           |                   | 40145  <- これが総計行
(14 rows)


普通のGROUP BYに、総計行だけ足すことも割と簡単。GROUPING SETSの括孤内を
微妙に変えます。上では(os, browser, ( ))、下では((os, browser), ( ))。OSとブラウザを括孤でくくって一体の組み合わせだよ~と明示することで、普通のGROUP BY os, browserと同じことになり、これに( )を加えて総計行を追加。
ex3_grouping_sets.sqlSelectRawtextBitbucket
SELECT os, browser, sum(page_views) FROM test.ga_tb2
GROUP BY GROUPING SETS ((os, browser), ()) -- ここ、微妙に変えた
HAVING sum(page_views) > 20
ORDER BY os, browser;

    os     |      browser      |  sum
-----------+-------------------+-------
 Android   | Android Browser   |   254
 Android   | Chrome            |   472
 Android   | Firefox           |   232
 Android   | Opera             |    73
 iOS       | Chrome            |    41
 iOS       | Safari            |   625
 iOS       | Safari (in-app)   |    37
 Linux     | Chrome            |   301
 Linux     | Firefox           |   692
 Macintosh | Chrome            |  1392
 Macintosh | Firefox           |   409
 Macintosh | Safari            |   897
 Windows   | Chrome            | 15377
 Windows   | Firefox           |  6492
 Windows   | Internet Explorer | 12264
 Windows   | Iron              |    84
 Windows   | Opera             |   359  <- ここまで普通の GROUP BY と同じ
           |                   | 40145  <- これが総計行
(18 rows)


このようにGROUPING SETSの括孤内を
適当にいじっていると使い方が少しずつ分かると思います。下は3つの集計を一度に行う例で、①OSとブラウザ別の集計、②OS別の小計、③総計という3種類の値を一回でゲット。3種類の区別は、OSとブラウザの列がNULLかどうかを見れば分かるので、とりあえず結果をテーブル等に保存しておき、後から総計行だけ取り出すとかも簡単です。
ex4_grouping_sets.sqlSelectRawtextBitbucket
SELECT os, browser, sum(page_views) FROM test.ga_tb2
GROUP BY GROUPING SETS ((os, browser), os, ()) -- ここ、また微妙に変えた
HAVING sum(page_views) > 20
ORDER BY os, browser;

    os     |      browser      |  sum
-----------+-------------------+-------
 Android   | Android Browser   |   254  <- OSとブラウザ別
 Android   | Chrome            |   472
 Android   | Firefox           |   232
 Android   | Opera             |    73
 Android   |                   |  1036  <- OS別の小計
 iOS       | Chrome            |    41
 iOS       | Safari            |   625
 iOS       | Safari (in-app)   |    37
 iOS       |                   |   703  <- OS別の小計
 Linux     | Chrome            |   301
 Linux     | Firefox           |   692
 Linux     |                   |  1008  <- OS別の小計
 Macintosh | Chrome            |  1392
 Macintosh | Firefox           |   409
 Macintosh | Safari            |   897
 Macintosh |                   |  2730  <- OS別の小計
 Windows   | Chrome            | 15377
 Windows   | Firefox           |  6492
 Windows   | Internet Explorer | 12264
 Windows   | Iron              |    84
 Windows   | Opera             |   359
 Windows   |                   | 34628  <- OS別の小計
           |                   | 40145  <- 総計行
(23 rows)

注:HAVING句で集計値20より大きい行に絞り込んでいるため、
    OS別の小計は、出ていない「その他」のブラウザを含む値です。
    例えば Linux 小計 1008 > (Linux-Chorome 301 + Linux-Firefox 692)

ここまでGROUPING SETSだけ使ってきましたが、上の例はROLLUPを使って次のように短く書けます。
GROUP BY GROUPING  SETS ((os, browser), os, ())
 ||
GROUP BY ROLLUP (os, browser)

また、例は省略しますが「各列を絞るor絞らないの、全組み合わせによる集計」は、GROUPING SETSでも可能ながら、CUBEを使うと下のように簡単に書けます。
GROUP BY GROUPING  SETS ((os, browser), os, browser, ())
 ||
GROUP BY CUBE (os, browser)

このようにROLLUPCUBEは、
「ある種のGROUPING SETSを短く書けるエイリアス」で、それぞれ下のように対応しています。(例として3列の集計を示しますが、列数が増えても同じ)
GROUP BY ROLLUP (columnA, columnB, columnC)
 ||
GROUP BY GROUPING  SETS ((columnA, columnB, columnC),
                         (columnA, columnB),
                          columnA, ())
 ||
片方向に(ツリー状に)絞っていく組み合せによる集計

---

GROUP BY CUBE (columnA, columnB, columnC)
 ||
GROUP BY GROUPING  SETS ((columnA, columnB, columnC),
                         (columnA, columnB),
                         (columnA, columnC),
                         (columnB, columnC),
                          columnA, columnB, columnC, ())
 ||
各列で絞る or 絞らないの、全ての組み合わせによる集計

というわけで、新しい構文がGROUPING SETS、CUBE、ROLLUP3つあるといっても別々な存在ではなく、基本になるGROUP BY GROUPING SETS +その短縮形2つ。今回自分が試した中では、GROUPING SETSを適当にいじっているうち何となく動作が分かってきました。

このほか報告では、(1)少し面倒なクエリを書いてクロス表を作った後に、総計だけ足すのが簡単になる、(2)ブログアクセスを地域別・ブラウザ別に集計してみたら、東京ではIEの割合が低く、地方で意外にも?Firefoxの割合が少し高いというトピックも紹介しました。前者はそのうち別記事で、クロス表を作る例と合わせて書きます。後者は普通のGROUP BYで出来る話で、まぁ余談です。

以上、拙い報告でしたが、聞いて下さった皆さんありがとうございました。m(_)m