1. k24d

    No comment

    k24d
Changes in body
Source | HTML | Preview
@@ -1,159 +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
+ uid,
v['c1'] AS c1,
v['c2'] AS c2,
v['c3'] AS c3,
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
```