TDでクエリを作成している時に、複数行を1行に集約する方法を学んだので備忘録として残しておきます。
下記のようなテーブルがあるとします。
| ID | category |
|---|---|
| AAA | shoes |
| AAA | furniture |
| AAA | apparel |
| AAA | else |
| BBB | shoes |
このテーブルでIDが重複せず、categoryをスラッシュ区切りで1行にまとめたいとします。
なので上記のテーブルを以下のようにしたいです。
| ID | Category |
|---|---|
| AAA | shoes/furniture/apparel/else |
| BBB | shoes |
使用する関数
■array_join
→配列型のデータをdelimiterで分割して反映
array_join(array, delimiter)
array：ARRAY型のcolumn
delimiter：連結された配列要素を分離するために使用される文字列
■array_agg
→指定したカラムを配列型に変換
array_agg(column)
実際のクエリ
SELECT
ID
,array_join(array_agg(category), '/')
FROM
table
GROUP BY
ID
array_agg で配列型に変換したデータを array_join で分割するという流れです。
ちなみにarray_aggは集約関数なので、他のカラムお反映したい場合はGROUP BYが必要です！
今後hiveでも実行できるようにprestoように作成したクエリを修正する必要があるかもなんですが、hiveではarray_joinとarray_aggは実行できないので、いい方法あれば教えていただきたいです...
参考