Athenaでカンマ区切りの複数カラムのデータを縦持ちにする
2023.02.17
この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部のueharaです。
今回はAmazon Athenaで、カンマ区切りの複数カラムのデータを縦持ちにするという処理をしてみたいと思います。
何がしたいのか？
以下のようなテーブルを
| items | prices | nums |
|---|---|---|
| aaa,bbb,ccc | 100,200,300 | 1,2,3 |
| ddd,eee,fff | 400,500,600 | 4,5,6 |
次のような形にしたいです。
| item | price | num |
|---|---|---|
| aaa | 100 | 1 |
| bbb | 200 | 2 |
| (中略) | ||
| eee | 500 | 5 |
| fff | 600 | 6 |
実現方法
結論から言うと、 CROSS JOIN UNNEST と SPLIT の組み合わせで実現できます。
まずは事前準備として、冒頭の変換前のテーブルを一時テーブルとして作成します。
WITH tmp AS ( SELECT 'aaa,bbb,ccc' AS items, '100,200,300' AS prices, '1,2,3' AS nums UNION ALL SELECT 'ddd,eee,fff' AS items, '400,500,600' AS prices, '4,5,6' AS nums ) -- データ確認 SELECT * FROM tmp
上記クエリを実行すると、以下のように表示されるかと思います。
データが準備できたので、実際にCROSS JOIN UNNEST と SPLIT を利用してこのデータを縦持ちにしてみます。
WITH tmp AS ( SELECT 'aaa,bbb,ccc' AS items, '100,200,300' AS prices, '1,2,3' AS nums UNION ALL SELECT 'ddd,eee,fff' AS items, '400,500,600' AS prices, '4,5,6' AS nums ) -- 縦持ちに変換 SELECT item, price, num FROM tmp CROSS JOIN UNNEST ( SPLIT(items, ','), SPLIT(prices, ','), SPLIT(nums, ',') ) t(item, price, num)
結果は次のようになり、縦持ちに変換できていることが分かります。
ちなみに、 UNNESTはFROM句においてCROSS JOINを省略して使用することができます。
補足
カラム間でSPLITされた配列の要素数が異なる場合は、エラーにはなりませんが足りない要素数分はNULLとなります。
WITH tmp AS ( SELECT 'aaa,bbb,ccc' AS items, '100,200,300' AS prices, '1,2' AS nums UNION ALL SELECT 'ddd,eee,fff' AS items, '400,500' AS prices, '4,5,6' AS nums ) -- 縦持ちに変換 SELECT item, price, num FROM tmp CROSS JOIN UNNEST ( SPLIT(items, ','), SPLIT(prices, ','), SPLIT(nums, ',') ) t(item, price, num)
最後に
今回はAmazon Athenaで、カンマ区切りの複数カラムのデータを縦持ちにするという処理をしてみました。
参考になりましたら幸いです。
