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 UNNESTSPLIT の組み合わせで実現できます。

まずは事前準備として、冒頭の変換前のテーブルを一時テーブルとして作成します。

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 UNNESTSPLIT を利用してこのデータを縦持ちにしてみます。

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で、カンマ区切りの複数カラムのデータを縦持ちにするという処理をしてみました。

参考になりましたら幸いです。

参考文献