Edited at

SQL で縦横変換まとめ(pivot と unpivot)

More than 3 years have passed since last update.

下図のように、「縦持ち」のテーブルを「横持ち」に置き換えることをピボット(pivot)、逆に「横持ち」のテーブルを「縦持ち」に置き換えることをアンピボット(unpivot)と呼びます。これらの変換を行なう方法をまとめました。


  • 標準SQL

  • Presto

  • Hive

  • Pandas (Python)

vtable.png

htable.png


標準SQL

SQL-like なクエリ言語ならどこでも使える書き方です。

Pivot

SELECT uid,

max(CASE WHEN key = 'c1' THEN value END) AS c1,
max(CASE WHEN key = 'c2' THEN value END) AS c2,
max(CASE WHEN key = 'c3' THEN value END) AS c3
FROM vtable
GROUP BY uid
;

uid  c1  c2  c3

--- -- -- --
101 11 12 13
102 21 22 23

Unpivot

SELECT uid, 'c1' AS key, c1 AS value FROM htable

UNION ALL
SELECT uid, 'c2' AS key, c2 AS value FROM htable
UNION ALL
SELECT uid, 'c3' AS key, c3 AS value FROM htable
;

uid  key  value

--- --- -----
101 c1 11
102 c1 21
101 c2 12
102 c2 22
101 c3 13
102 c3 23


Presto

標準SQLの方法でも構いませんが、以下のような書き方も出来ます。

Pivot

map_agg 関数でマップ型の構造を作ってから参照するやり方です。

SELECT

uid,
kv['c1'] AS c1,
kv['c2'] AS c2,
kv['c3'] AS c3
FROM (
SELECT uid, map_agg(key, value) kv
FROM vtable
GROUP BY uid
) t

uid  c1  c2  c3

--- -- -- --
101 11 12 13
102 21 22 23

Unpivot

カラムの配列を作ってから CROSS JOIN unnest で展開するやり方です。PostgreSQL でも使えます。

SELECT t1.uid, t2.key, t2.value

FROM htable t1
CROSS JOIN unnest (
array['c1', 'c2', 'c3'],
array[c1, c2, c3]
) t2 (key, value)

uid  key  value

--- --- -----
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23


Hive

Pivot

標準の Hive 関数ではありませんが、Treasure Data の Hive には to_map UDAF が組み込まれており、マップ型の構造を作ってから参照することが出来ます。

SELECT

uid,
kv['c1'] AS c1,
kv['c2'] AS c2,
kv['c3'] AS c3
FROM (
SELECT uid, to_map(key, value) kv
FROM vtable
GROUP BY uid
) t

uid  c1  c2  c3

--- -- -- --
101 11 12 13
102 21 22 23

Unpivot

LATERAL VIEW explode で展開する方法があります。

SELECT t1.uid, t2.key, t2.value

FROM htable t1
LATERAL VIEW explode (map(
'c1', c1,
'c2', c2,
'c3', c3
)) t2 as key, value

uid  key  value

--- --- -----
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23


Pandas

pivotmelt といった関数が使えます。

Pivot

In [1]: vtable.pivot('uid', 'key', 'value')

Out[1]:
key c1 c2 c3
uid
101 11 12 13
102 21 22 23

Unpivot

In [2]: pd.melt(htable, 'uid', var_name='key')

Out[2]:
uid key value
0 101 c1 11
1 101 c2 12
2 101 c3 13
3 102 c1 21
4 102 c2 22
5 102 c3 23