[Redshift] テーブルの縦持ちと横持ちを入れ換える方法

はじめに

既に存在するデータの縦持ちと横持ちを入れ替えて見たい場合の方法を書いていきます。

環境

Mac OSX 10.10.5 Yosemite
Redshift 1.0.1125

縦持ち ⇨ 横持ち

Pivot（ピボット）と言います。

縦持ちテーブルの準備

商品別の月別売上の表です。

CREATE SCHEMA IF NOT EXISTS blog;

CREATE TABLE blog.vertical (
  id INTEGER
  , item_name VARCHAR(1)
  , month SMALLINT
  , amount INTEGER
);

INSERT INTO blog.vertical VALUES
 (1,'A',1,150)
,(2,'A',2,120)
,(3,'A',3,100)
,(4,'B',1,20)
,(5,'B',2,60)
,(6,'B',3,80)
,(7,'C',1,100)
,(8,'C',2,200)
,(9,'C',3,300);

postgres=# SELECT * FROM blog.vertical;
 id | item_name | month | amount 
----+-----------+-------+--------
  1 | A         |     1 |    150
  2 | A         |     2 |    120
  3 | A         |     3 |    100
  4 | B         |     1 |     20
  5 | B         |     2 |     60
  6 | B         |     3 |     80
  7 | C         |     1 |    100
  8 | C         |     2 |    200
  9 | C         |     3 |    300
(9 rows)

Pivotしてみる

SELECT
  item_name
  , MAX("1") AS "1"
  , MAX("2") AS "2"
  , MAX("3") AS "3"
FROM
 (
  SELECT
    item_name
    , CASE month WHEN 1 THEN amount END "1"
    , CASE month WHEN 2 THEN amount END "2"
    , CASE month WHEN 3 THEN amount END "3"
  FROM
    blog.vertical
  GROUP BY
    item_name, month, amount
  ORDER BY
    item_name, amount
 ) A
GROUP BY 
  item_name;

まずは服問い合わせの実行結果が下記になります。
見やすくするためにSQLには記述のないmonthを追加しています。

.
 item_name | month |  1  |  2  |  3  
-----------+-------+-----+-----+-----
 A         |     1 | 150 |     |    
 A         |     2 |     | 120 |    
 A         |     3 |     |     | 100
 B         |     1 |  20 |     |    
 B         |     2 |     |  60 |    
 B         |     3 |     |     |  80
 C         |     1 | 100 |     |    
 C         |     2 |     | 200 |    
 C         |     3 |     |     | 300
(9 rows)

それをitem_name別にで最大値を取得すると下記になり、横持ちに移行完了です。

実行結果

 item_name |  1  |  2  |  3  
-----------+-----+-----+-----
 A         | 150 | 120 | 100
 B         |  20 |  60 |  80
 C         | 100 | 200 | 300
(3 rows)

横持ち　⇨ 縦持ち

Unpivot（アンピボット）と言います。

横持ちテーブル準備

Pivotの結果をSELECT INTOで作成しても良いですね。

CREATE TABLE blog.horizontal (
  item_name CHAR(1)
  , "1" INTEGER
  , "2" INTEGER
  , "3" INTEGER
);
INSERT INTO blog.horizontal
VALUES
 ('A',150,120,100)
,('B',20,60,80)
,('C',100,200,300);

postgres=# SELECT * FROM blog.horizontal;
 item_name |  1  |  2  |  3  
-----------+-----+-----+-----
 A         | 150 | 120 | 100
 B         |  20 |  60 |  80
 C         | 100 | 200 | 300
(3 rows)

Unpivotするための月だけのテーブルを作成

CREATE TABLE blog.month (month INTEGER);
INSERT INTO blog.month VALUES(1),(2),(3);

postgres=# SELECT * FROM blog.month;
 month 
-------
     1
     2
     3
(3 rows)

Unpivotしてみる

SELECT
  item_name
  , month
  , CASE month 
         WHEN 1 THEN "1"
         WHEN 2 THEN "2"
         WHEN 3 THEN "3"
         END amount
FROM
  blog.horizontal
    cross join blog.month
ORDER BY
  item_name, month, amount;

クロスジョインして下記の状態を作ります。

postgres=# SELECT *　FROM blog.horizontal, blog.month ORDER BY　item_name, month;
 item_name |  1  |  2  |  3  | month 
-----------+-----+-----+-----+-------
 A         | 150 | 120 | 100 |     1
 A         | 150 | 120 | 100 |     2
 A         | 150 | 120 | 100 |     3
 B         |  20 |  60 |  80 |     1
 B         |  20 |  60 |  80 |     2
 B         |  20 |  60 |  80 |     3
 C         | 100 | 200 | 300 |     1
 C         | 100 | 200 | 300 |     2
 C         | 100 | 200 | 300 |     3
(9 rows)

その結果から、縦持ちにしたいamountをmonthの値で条件分岐させ、どのカラム「1〜3」のどこを参照するか決めています。

実行結果

.
 item_name | month | amount 
-----------+-------+--------
 A         |     1 |    150
 A         |     2 |    120
 A         |     3 |    100
 B         |     1 |     20
 B         |     2 |     60
 B         |     3 |     80
 C         |     1 |    100
 C         |     2 |    200
 C         |     3 |    300
(9 rows)

最初に作った縦持ちテーブルとほぼ同じになりました。

さいごに

頻繁には使わないかもしれませんが、必要な事もあるので頭の片隅で覚えておくと良いと思います。