[新機能] RedshiftのPIVOT/UNPIVOT機能を試してみた

データアナリティクス事業本部の森脇です。

RedshiftのPIVOT/UNPIVOT機能が発表されました。

https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause-pivot-unpivot-examples.html

これを利用することで、いわゆる 縦持ち、 横持ちを入れ替えることが可能です。

早速試してみました。

PIVOT

まずは元となるテーブルを作成します。

今回は売上情報をイメージしたものを作成しました。

ひな祭りが近いのでそれっぽいデータを投入しておきます。

-- 疑似 売上テーブル
CREATE TABLE test_pivot (
  order_at DATE, -- 注文日
  item_code VARCHAR(10), -- 商品コード
  category_name VARCHAR(100), -- カテゴリー名
  item_name VARCHAR(100), -- アイテム名
  quantity int, -- 個数
  price int -- 価格
);

INSERT INTO test_pivot VALUES ('2022/02/01', 'item-0001', '食品', 'ひなあられ', 2, 150);
INSERT INTO test_pivot VALUES ('2022/02/01', 'item-0002', '季節用品', '雛人形セット', 1, 30000);
INSERT INTO test_pivot VALUES ('2022/02/01', 'item-0003', '飲料', '甘酒', 3, 300);
INSERT INTO test_pivot VALUES ('2022/02/01', 'item-0004', '食品', 'ちらし寿司', 2, 1500);
INSERT INTO test_pivot VALUES ('2022/02/02', 'item-0002', '季節用品', '雛人形セット', 2, 30000);
INSERT INTO test_pivot VALUES ('2022/02/02', 'item-0003', '飲料', '甘酒', 10, 300);
INSERT INTO test_pivot VALUES ('2022/02/04', 'item-0004', '食品', 'ちらし寿司', 15, 1500);
INSERT INTO test_pivot VALUES ('2022/02/04', 'item-0005', '食品', 'ケーキ', 1, 3000);
INSERT INTO test_pivot VALUES ('2022/02/05', 'item-0001', '食品', 'ひなあられ', 10, 150);
INSERT INTO test_pivot VALUES ('2022/02/05', 'item-0002', '季節用品', '雛人形セット', 5, 30000);

現状の中身はこんな感じです。

SELECT * FROM test_pivot ORDER BY 1;

カテゴリー別の総売上金額を算出してみます。

-- カテゴリ別 総売上額
SELECT category_name, SUM(quantity * price) as total
FROM test_pivot
GROUP BY category_name
;

いまはいわゆる横持ち状態です。この結果を縦持ちにしたい場合、PIVOT機能を使うことで非常に簡単に実現することができます。

-- カテゴリ別 総売上額(縦持ち)
SELECT *
FROM (SELECT category_name, quantity, price FROM test_pivot) PIVOT (
    SUM(quantity * price) FOR category_name IN ('季節用品', '食品', '飲料')
);

複雑なクエリが必要ありません！

複数のAggregate関数に対応

上記例ではSUMを利用しました。

2022年2月24日現在、COUNT, SUM, MIN, MAX, AVGの関数に対応しているようです。

https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause-pivot-unpivot-examples.html

PIVOT currently supports the COUNT, SUM, MIN, MAX, and AVG aggregate functions.

COUNT

-- COUNT: 日別商品注文回数(縦持ち)
SELECT *
FROM (SELECT order_at FROM test_pivot) PIVOT (
    COUNT(*) FOR order_at IN ('2022/02/01', '2022/02/02', '2022/02/03', '2022/02/04', '2022/02/05')
);

MIN

-- MIN: 商品別最低注文個数(縦持ち)
SELECT *
FROM (SELECT item_name, quantity FROM test_pivot) PIVOT (
    MIN(quantity) FOR item_name IN ('ひなあられ', '雛人形セット', '甘酒', 'ちらし寿司', 'ケーキ')
);

MAX

-- MAX: 商品別最大注文金額(縦持ち)
SELECT *
FROM (SELECT item_name, (quantity * price) as total FROM test_pivot) PIVOT (
    MAX(total) FOR item_name IN ('ひなあられ', '雛人形セット', '甘酒', 'ちらし寿司', 'ケーキ')
);

AVG

-- AVG: 日別平均注文金額(縦持ち)
SELECT *
FROM (SELECT order_at, quantity, price FROM test_pivot) PIVOT (
    AVG(quantity * price) FOR order_at IN ('2022/02/01', '2022/02/02', '2022/02/03', '2022/02/04', '2022/02/05')
);

CTASと組み合わせ

PIVOT/UNPIVOTはFROM句の機能です。

そのため、CTASでも利用できます。

CTAS

-- CTASでも使える
CREATE TABLE test_pivot_2 AS SELECT *
FROM (SELECT category_name, quantity, price FROM test_pivot) PIVOT (
    SUM(quantity * price) FOR category_name IN ('季節用品', '食品', '飲料')
);
SELECT * FROM test_pivot_2;

マテリアライズドビュー

同様に、マテリアライズドビューとも組み合わせが可能です。

個人的にはこれがとても便利だと感じました。

マテビューを作っておけば、データの更新に複雑なSQLを用意する必要がありません。

-- マテビューでも使える
CREATE MATERIALIZED VIEW test_pivot_view AS SELECT *
FROM (SELECT category_name, quantity, price FROM test_pivot) PIVOT (
    SUM(quantity * price) FOR category_name IN ('季節用品', '食品', '飲料')
);

SELECT * FROM test_pivot_view;

-- データが追加されたらマテビューをrereshするだけで反映される
INSERT INTO test_pivot VALUES ('2022/02/06', 'item-0001', '食品', 'ひなあられ', 10, 150);

REFRESH MATERIALIZED VIEW test_pivot_view;
SELECT * FROM test_pivot_view;

UNPIVOT

縦持ちから横持ちに変換したい場合には、UNPIVOTを使います。

PIVOTで作った「test_pivot_2」を使って確認します。

select * from test_pivot_2;

-- カテゴリ別総注文金額(→横持ちへ)
SELECT * 
FROM (SELECT "季節用品", "食品", "飲料" FROM test_pivot_2) UNPIVOT (
    total FOR category_name IN ("季節用品", "食品", "飲料")
);

こちらも簡単に実現することができました。

まとめ

PIVOT/UNPIVOTを使うことでデータの縦持ち/横持ちを簡単に変更することができました。 分析用テーブル/ビューを作るのにも役立ちそうです。

参考

• リリース記事
• リファレンス