Treasure Dataでテーブルの縦横変換をやってみた。

こんにちは、今回はTreasure Dataでトランザクション形式で保持しているデータを加工して、
各値を一つの項目にマスターの形式に変換する処理について書いていきたいと思います。

具体的なイメージは以下のA表からB表への変換です。

id	属性名	値
001	性別	男性
001	年代	20代
001	都道府県	東京
002	性別	女性
002	年代	10代
002	都道府県	大阪

A表

id	性別	年代	都道府県
001	男性	20代	東京
002	女性	10代	大阪

B表

Treasure Dataでは、Prestoの関数でMAP_AGG()という関数があります。

これを利用して、id単位で各属性と値のマッピングを対応データを作成することができます。

MAP_AGG()の活用例

以下のイメージが対応データのイメージです。

idごとに各属性が対応していることがわかると思います。

上記のデータを作成した上で、各属性の値を列として抽出すれば、B表が出来上がります。

以下に参考SQLとイメージ画像を載せておきます。

参考SQL

WITH tab1 AS(
SELECT '001' AS id, '性別' AS attr,'男性' AS value
UNION ALL
SELECT '001' AS id, '年代' AS attr,'20代' AS value
UNION ALL
SELECT '001' AS id, '都道府県' AS attr, '東京' AS value
UNION ALL
SELECT '002' AS id, '性別' AS attr,'女性' AS value
UNION ALL
SELECT '002' AS id, '年代' AS attr,'10代' AS value
UNION ALL
SELECT '002' AS id, '都道府県' AS attr,'大阪' AS value
)
,
tab2 AS (
SELECT id,  MAP_AGG(attr, value) as attrs FROM tab1
GROUP BY id)

-- SELECT * FROM tab2

SELECT id
, attrs['性別'] AS sex
, attrs['年代'] AS age
, attrs['都道府県'] AS todofuken 
FROM tab2