Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
244
Help us understand the problem. What is going on with this article?
@k24d

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

More than 5 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
244
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
k24d

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
244
Help us understand the problem. What is going on with this article?