![[Redshift] テーブルの縦持ちと横持ちを入れ換える方法](https://devio2023-media.developers.io/wp-content/uploads/2014/05/Amazon_Redshift.png)
[Redshift] テーブルの縦持ちと横持ちを入れ換える方法
2016.12.18
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
既に存在するデータの縦持ちと横持ちを入れ替えて見たい場合の方法を書いていきます。
環境
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)
最初に作った縦持ちテーブルとほぼ同じになりました。
さいごに
頻繁には使わないかもしれませんが、必要な事もあるので頭の片隅で覚えておくと良いと思います。
