概要
TreasureDataでデータ加工をしている際に、「GROUP BY」したSELECT結果に、
GROUP BY対象外のカラムを配列で追加したかったので調べた結果を作業メモとして残す。
GROUP BY対象外のカラムをARRAYにする
COLLECT_SET関数で配列にする
SELECT
td_global_id,
COLLECT_SET(user_id) AS user_ids
FROM
dev.hoge_access
WHERE
td_global_id IS NOT NULL AND td_global_id <> ''
AND user_id IS NOT NULL AND user_id <> ''
GROUP BY
td_global_id
;
GROUP BY対象外のカラムをカンマ区切りで結合する
CONCAT_WS関数を使って結合する
SELECT
td_global_id,
-- カンマ区切りの文字列に結合して文字列に...
CONCAT_WS(',', COLLECT_SET(user_id)) AS user_ids
FROM
dev.hoge_access
WHERE
td_global_id IS NOT NULL AND td_global_id <> ''
AND user_id IS NOT NULL AND user_id <> ''
GROUP BY
td_global_id
;
結合したカンマ区切りの文字列を分割する
SPLIT関数を使って分割して配列型にする。
SELECT
td_global_id,
-- カンマ区切りの文字列を分割して配列化
SPLIT(CONCAT_WS(',',COLLECT_SET(user_id)), ',') AS user_ids
FROM
dev.hoge_access
WHERE
td_global_id IS NOT NULL AND td_global_id <> ''
AND user_id IS NOT NULL AND user_id <> ''
GROUP BY
td_global_id
;