こんにちは、今回はTreasure Dataでトランザクション形式で保持しているデータを加工して、
各値を一つの項目にマスターの形式に変換する処理について書いていきたいと思います。
具体的なイメージは以下のA表からB表への変換です。
id | 属性名 | 値 |
001 | 性別 | 男性 |
001 | 年代 | 20代 |
001 | 都道府県 | 東京 |
002 | 性別 | 女性 |
002 | 年代 | 10代 |
002 | 都道府県 | 大阪 |
id | 性別 | 年代 | 都道府県 |
001 | 男性 | 20代 | 東京 |
002 | 女性 | 10代 | 大阪 |
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
イメージ画像
以上が、縦(行)で保持しているデータを横(列)に分割する処理についてのまとめでした。
次回は、逆に横持ちのデータを縦に変換するようなクエリについて書いていこうと思います。
最後までご覧いただきありがとうございました。
コメント