PL/Python で WEB アプリ
[アシスト Y田さん]
これはすごい、「SELECT PL/PythonpgAdmin Ⅲを使おう!
[チョコレートバーさん]
「すごく便利なんだけどちょっと癖がある」pgAdminポスグレ向けマイグレーションツール
[kwatchさん]
2013そのほか
発表時間が重なってカワラさんの「HOW TO <PostgreSQL 9.5 の新しい GROUP BY
自分の発表。一応スライドはこちらですが、やっつけだったのでHTML[参考]
»»
»
[テスト環境]
»»
[使ったデータ]
»»
[準備 : 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;
上の
ここから本題で、上のテーブルを使って
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
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)
同じ結果を普通の
( 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 );
先ほどの
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)
普通の
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)
このように
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)
ここまで
GROUP BY GROUPING SETS ((os, browser), os, ()) || GROUP BY ROLLUP (os, browser)
また、例は省略しますが「各列を絞る
GROUP BY GROUPING SETS ((os, browser), os, browser, ()) || GROUP BY CUBE (os, browser)
このように
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 絞らないの、全ての組み合わせによる集計
というわけで、新しい構文が
このほか報告では、(1)少し面倒なクエリを書いてクロス表を作った後に、総計だけ足すのが簡単になる、(2)ブログアクセスを地域別・ブラウザ別に集計してみたら、東京では
以上、拙い報告でしたが、聞いて下さった皆さんありがとうございました。m(_)m