PostgreSQLで集計をとる時にキク技（縦横変換）

PostgreSQL database
2020.07.06

SQLで集計を行うとき、実行結果（縦持ち）を横持ちにしたいことがあります。今日は「時々必要に迫られるけどいつも思い出せない」的なtips第2弾です！

サンプルで使う表 testtable を作成する

sandambara=> -- 既存テーブルがあれば削除
sandambara=> DROP TABLE IF EXISTS testtable;
DROP TABLE
sandambara=>
sandambara=> -- テストデータ格納用テーブル作成
sandambara=> -- id           主キー
sandambara=> -- salesdate    売上日
sandambara=> -- item         品目
sandambara=> -- amount       販売額
sandambara=> CREATE TABLE testtable(
sandambara(>         id int
sandambara(> ,       salesdate date
sandambara(> ,       item text
sandambara(> ,       amount int
sandambara(> ,       CONSTRAINT pk_testtable PRIMARY KEY(id)
sandambara(> );
CREATE TABLE

こんな感じの表を作ってデータを投入します。

sandambara=> -- テストデータ投入
sandambara=> INSERT INTO testtable VALUES(1, '2020-01-01', 'りんご', 1000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(2, '2020-01-01', 'みかん', 2000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(3, '2020-01-01', 'バナナ', 3000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(4, '2020-02-02', 'りんご', 1000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(5, '2020-03-03', 'りんご', 5000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(6, '2020-03-03', 'みかん', 1000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(7, '2020-03-03', 'バナナ', 3000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(8, '2020-04-03', 'いちご', 4000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(9, '2020-04-04', 'りんご', 1000);
INSERT 0 1
sandambara=> INSERT INTO testtable VALUES(10,'2020-04-01', 'バナナ', 1000);
INSERT 0 1

testtable表をSELECTしてみる

一先ずそのままSELECTしてみます。「月ごとの販売金額集計」を想定しているので、販売日をyy-mmに変換しています。

sandambara=>
sandambara=> -- 全件普通にSELECT（id昇順）
sandambara=> SELECT
sandambara->         id
sandambara-> ,       to_char(salesdate, 'yy-mm') as salesdate
sandambara-> ,       item
sandambara-> ,       amount
sandambara-> FROM
sandambara->         testtable
sandambara-> ORDER BY
sandambara->         id;

釈迦に説法ではありますが、SELECT順は保証されませんので必ずORDER BYをつけるようにしましょう（見る度に結果が変わっていたら大変ですよね）。実行結果はこちらです。

 id | salesdate |  item  | amount
----+-----------+--------+--------
  1 | 20-01     | りんご |   1000
  2 | 20-01     | みかん |   2000
  3 | 20-01     | バナナ |   3000
  4 | 20-02     | りんご |   1000
  5 | 20-03     | りんご |   5000
  6 | 20-03     | みかん |   1000
  7 | 20-03     | バナナ |   3000
  8 | 20-04     | いちご |   4000
  9 | 20-04     | りんご |   1000
 10 | 20-04     | バナナ |   1000
(10 rows)

集計を期待されているとき、このまま提出すると間違いなく突き返されます（笑）

月毎に売上を集計する

これをGROUP BYしてみます。

sandambara=> -- （★）月・item毎に売上を集計
sandambara=> SELECT
sandambara->         to_char(salesdate, 'yy-mm') as salesdate
sandambara-> ,       item
sandambara-> ,       sum(amount) amount
sandambara-> FROM
sandambara->         testtable
sandambara-> GROUP BY
sandambara->         salesdate
sandambara-> ,       item
sandambara-> ORDER BY
sandambara->         salesdate
sandambara-> ,       item;

結果はこちら。

 salesdate |  item  | amount
-----------+--------+--------
 20-01     | みかん |   2000
 20-01     | りんご |   1000
 20-01     | バナナ |   3000
 20-02     | りんご |   1000
 20-03     | みかん |   1000
 20-03     | りんご |   5000
 20-03     | バナナ |   3000
 20-04     | いちご |   4000
 20-04     | りんご |   1000
 20-04     | バナナ |   1000
(10 rows)

集計の目的は果たしているのですが、ちょっと見辛くありませんか？

なぜ見辛いのか

salesdate列とitem列に同じ値が複数出力されているからです。GROUP BYされてはいるのですが、期待している結果ではありませんよね。

縦持ちのデータを横持ちに変換する

先ほどの出力結果は集計後の状態ではなく、どちらかと言えば集計前の状態です。よく見る集計結果はこのようなものではないでしょうか？

  item  | 20-01 | 20-02 | 20-03 | 20-04
--------+-------+-------+-------+-------
 いちご |     0 |     0 |     0 |  4000
 みかん |  2000 |     0 |  1000 |     0
 りんご |  1000 |  1000 |  5000 |  1000
 バナナ |  3000 |     0 |  3000 |  1000
(4 rows)

縦持ちのデータを横持ちにすることで、販売月と品名の重複出力が無くなりスッキリと見やすくなりました。ではSQLを見てみましょう！

sandambara=> -- （★）を横持ちに変換
sandambara=> SELECT
sandambara->         item
sandambara-> ,       sum(CASE WHEN to_char(salesdate, 'yy-mm') = '20-01' THEN amount ELSE 0 END) as "20-01"
sandambara-> ,       sum(CASE WHEN to_char(salesdate, 'yy-mm') = '20-02' THEN amount ELSE 0 END) as "20-02"
sandambara-> ,       sum(CASE WHEN to_char(salesdate, 'yy-mm') = '20-03' THEN amount ELSE 0 END) as "20-03"
sandambara-> ,       sum(CASE WHEN to_char(salesdate, 'yy-mm') = '20-04' THEN amount ELSE 0 END) as "20-04"
sandambara-> FROM
sandambara->         testtable
sandambara-> GROUP BY
sandambara->         item
sandambara-> ORDER BY
sandambara->         item;

ゼロ値が不要であればELSE 0を削除すればOKです。

初めて見たときはなぜこれで縦横変換を行えるのか？？？でした。よく見るとsalesdate列はSELECTされずGROUP BY句とORDER BY句からも消えました。結果としてitem列単独でGROUP BYを行っています。

肝になるのはSUMにyy-mmを付与することでsalesdate列の代用としていることです。これは非常に便利なテクニックですので是非活用してみてください！

コメント