この記事は公開されてから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で、カンマ区切りの複数カラムのデータを縦持ちにするという処理をしてみました。
参考になりましたら幸いです。