1. k24d

    Posted

    k24d
Changes in title
+SQL で縦横変換まとめ(pivot と unpivot)
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,159 @@
+下図のように、「縦持ち」のテーブルを「横持ち」に置き換えることをピボット(pivot)、逆に「横持ち」のテーブルを「縦持ち」に置き換えることをアンピボット(unpivot)と呼びます。これらの変換を行なう方法をまとめました。
+
+* 標準SQL
+* Presto
+* Hive
+* Pandas (Python)
+
+![vtable.png](https://qiita-image-store.s3.amazonaws.com/0/1324/89b243c4-1531-6286-fc87-5dca974a6bd8.png)
+
+![htable.png](https://qiita-image-store.s3.amazonaws.com/0/1324/4ecea281-eb8c-7fde-e905-df253e8616bd.png)
+
+# 標準SQL
+
+SQL-like なクエリ言語ならどこでも使える書き方です。
+
+**Pivot**
+
+```sql
+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**
+
+```sql
+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`` 関数でマップ型の構造を作ってから参照するやり方です。
+
+```sql
+SELECT
+ uid
+ v['c1'] AS c1,
+ v['c2'] AS c1,
+ v['c2'] AS c1,
+FROM (
+ SELECT uid, map_agg(key, value) v
+ FROM vtable
+ GROUP BY uid
+) t
+```
+```
+uid c1 c2 c3
+--- -- -- --
+101 11 12 13
+102 21 22 23
+```
+
+**Unpivot**
+カラムの配列を作ってから ``CROSS JOIN unnest`` で展開するやり方です。PostgreSQL でも使えます。
+
+```sql
+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 では、Presto の ``map_agg`` のような方法は見つけられませんでした。[Brickhouse の ``collect`` UDAF を使うと](http://engineering.klout.com/2013/01/introducing-brickhouse-major-open-source-release-from-klout/)同じようなことが出来るようです。
+
+
+**Unpivot**
+``LATERAL VIEW explode`` で展開する方法があります。
+
+```sql
+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
+
+``pivot`` や ``melt`` といった関数が使えます。
+
+**Pivot**
+
+```py3
+In [1]: vtable.pivot('uid', 'key', 'value')
+Out[1]:
+key c1 c2 c3
+uid
+101 11 12 13
+102 21 22 23
+```
+
+**Unpivot**
+
+```py3
+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
+```