【PostgreSQL】psqlでクエリ結果をクロス集計表示する

\crosstabviewでクエリ結果をクロス集計表示する

psqlの\crosstabviewメタコマンドは、直前に実行したクエリの結果を縦・横の2軸で組み替えたクロス集計表(ピボットテーブル)として表示する。

地域と四半期ごとの売上を持つテーブルを例にする。

testdb=# SELECT region, quarter, amount FROM sales ORDER BY region, quarter;
 region | quarter | amount
--------+---------+--------
 east   | Q1      |    100
 east   | Q2      |    150
 east   | Q3      |    120
 east   | Q4      |    200
 west   | Q1      |     80
 west   | Q2      |     90
 west   | Q3      |     95
 west   | Q4      |    110
(8 rows)

testdb=# \crosstabview region quarter amount
 region | Q1  | Q2  | Q3  | Q4
--------+-----+-----+-----+-----
 east   | 100 | 150 | 120 | 200
 west   |  80 |  90 |  95 | 110
(2 rows)

\crosstabview 縦軸の列横軸の列値の列の順で列名を指定する。regionの値が行、quarterの値が列見出しになり、対応するamountが交差するセルに入る。

列を指定しない場合のデフォルト

列名を省略すると、クエリ結果の1列目が縦軸、2列目が横軸、3列目が値として使われる。

testdb=# SELECT region, quarter, amount FROM sales ORDER BY region, quarter;
testdb=# \crosstabview
 region | Q1  | Q2  | Q3  | Q4
--------+-----+-----+-----+-----
 east   | 100 | 150 | 120 | 200
 west   |  80 |  90 |  95 | 110
(2 rows)

列を入れ替えると縦軸と横軸も入れ替わる。

testdb=# \crosstabview quarter region
 quarter | east | west
---------+------+------
 Q1      |  100 |   80
 Q2      |  150 |   90
 Q3      |  120 |   95
 Q4      |  200 |  110
(4 rows)

横見出しの並び順を指定する

横見出しの列は、指定しない場合はクエリ結果に現れた順番のまま並ぶ。文字列としてのソート順ではないため、Q1〜Q4のような自然な順序にならないことがある。

第4引数でソートに使う列を指定すると、見出しの並び順を制御できる。

testdb=# SELECT region, quarter, amount,
testdb-#        CASE quarter WHEN 'Q1' THEN 1 WHEN 'Q2' THEN 2 WHEN 'Q3' THEN 3 WHEN 'Q4' THEN 4 END AS q_order
testdb-# FROM sales;
testdb=# \crosstabview region quarter amount q_order

q_order列の値の昇順でQ1, Q2, Q3, Q4の順に列が並ぶ。q_order列自体は結果のセルには使われず、並び順の決定だけに使われる。

集計クエリと組み合わせる

\crosstabviewはクロス集計そのものを行うわけではなく、あくまで直前のクエリ結果を組み替えて表示するだけである。GROUP BYで集計したクエリを組み合わせれば、レポートらしい表になる。

testdb=# SELECT region, quarter, sum(amount) AS total
testdb-# FROM sales
testdb-# GROUP BY region, quarter
testdb-# ORDER BY region, quarter;
testdb=# \crosstabview region quarter total
 region | Q1  | Q2  | Q3  | Q4
--------+-----+-----+-----+-----
 east   | 100 | 150 | 120 | 200
 west   |  80 |  90 |  95 | 110
(2 rows)

ORDER BYを省略すると行の取得順序が保証されず、横見出しの並び順も安定しない。見出しの順序を制御したい場合は、直前のクエリにORDER BYを付けるか、前述の第4引数でソート列を指定する。

SQLのcrosstab()関数との違い

PostgreSQLにはtablefunc拡張のcrosstab()関数もあり、同様にクロス集計表を作れる。\crosstabviewはpsql側の表示機能であるのに対して、crosstab()はサーバー側でSQLの結果として行列を返す関数である。

	\crosstabview	crosstab()
実行場所	psql(クライアント)	PostgreSQLサーバー
拡張機能の有無	不要	`tablefunc`拡張が必要
出力形式	psqlでの表示専用	SQLの結果セット(アプリケーションから利用可能)
列の数	クエリ結果から動的に決まる	事前に列定義を書く必要がある

アプリケーションのコードからクロス集計結果を取得したい場合はcrosstab()を使う。psqlで手早く確認したいだけであれば\crosstabviewで十分である。

NULLと重複データの扱い

該当するデータがない交差セルは空欄になる。

testdb=# INSERT INTO sales VALUES ('north', 'Q1', 50);
testdb=# SELECT region, quarter, amount FROM sales WHERE region IN ('east', 'north') ORDER BY region, quarter;
testdb=# \crosstabview region quarter amount
 region | Q1  | Q2  | Q3  | Q4
--------+-----+-----+-----+-----
 east   | 100 | 150 | 120 | 200
 north  |  50 |     |     |
(2 rows)

同じ縦軸・横軸の組み合わせが複数行存在する場合は、最後に読み込んだ行の値で上書きされる。集計せずに生データをそのまま\crosstabviewにかけると、意図しない値を表示する場合があるため注意する。

参考

psql - PostgreSQL Documentation