Postgres: Power BI で利用しやすくする為の Unpivot SQL

背景

Power BI のビジュアルでは Unpivot してあるとスライサーで利用しやすいので
Postgre 上での Unpivot 方法を備忘録

概要

Union で繋げる道もあるけれど、重くなるので Unnest を使う

詳細

変換の前後をみることで、Unpivot の結果が分かるかな、と

変換前：元データ

PLC からの取得データだと、以下のように大量の列にデータがばらけてたりする

元データ作成用

CREATE TEMP TABLE pivotTable ("ID" int, "Interests1" text, "Interests2" text, "Interests3" text, "Knowledges1" text, "Knowledges2" text, "Knowledges3" text);
INSERT INTO pivotTable VALUES (1, 'Movie', 'Game', 'Music', 'C#', 'Yaml', 'JSON'), (2, 'Music', 'Game', 'Tennis', 'C#', 'JSON', 'PowerPlatform');

変換後：取得したい結果

こんな感じにしたい

SQL

こんな感じでグループ単位で unnest() していけば OK

Unpivot方法

SELECT 
	"ID",
	unnest(array['First', 'Second', 'Third']) AS "Order",
	unnest(array["Interests1", "Interests2", "Interests3"]) AS "Interest",
    unnest(array["Knowledges1", "Knowledges2", "Knowledges3"]) AS "Knowledge"
FROM pivotTable
ORDER BY "ID";

あとがき

半角大文字小文字を意識した列名にする場合は "ID" のように二重引用符が必要なんだと最近知ったけど・・

触ったことないもの使うと新しいことが沢山あって楽しいですね

参考

元ネタは、安定の stakoverflow。久しく投稿してないけど・・

Union版

SELECT id,
       'a' AS colname,
       a AS thing
FROM foo
UNION ALL
SELECT id,
       'b' AS colname, 
       b AS thing
FROM foo
UNION ALL
SELECT id, 
       'c' AS colname,
       c AS thing
FROM foo
ORDER BY id;

unnest版

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS thing
FROM foo
ORDER BY id;

keyword

Equivalent to unpivot() in PostgreSQL