Oracle の LISTAGG, MySQL の GROUP_CONCAT みたいなことを TreasureData (Presto) でやりたい。
答え
array_join と array_agg を組み合わせることで実現できる。
6.15. Array Functions and Operators — Presto 0.195 Documentation
例
- employee : department = N : N
- department_employee は中間テーブル
という前提で、素直に組み合わせをすべて選択すると以下のようになる。
SELECT e.id AS employee_id, e.name AS employee_name, d.name AS department_name FROM department_employee de LEFT JOIN employee e ON de.employee_id = e.id LEFT JOIN department d ON de.department_id = d.id ORDER BY employee_id ;
employee_id employee_name department_name ---------------- ---------------------- --------------- 1 Rick Development 1 Rick Sales 2 Chris Development 3 IOROI Sales 3 IOROI Marketing 3 IOROI HR
この結果を employee ごとにグループ化しつつ department_name も結合する形で select したい。
array_join と array_agg を組み合わせることで実現できる。
SELECT e.id AS employee_id, e.name AS employee_name, array_join(array_agg(d.name), ',') AS department_names FROM department_employee de LEFT JOIN employee e ON de.employee_id = e.id LEFT JOIN department d ON de.department_id = d.id GROUP BY employee_id, employee_name ORDER BY employee_id ;
employee_id employee_name department_names ---------------- ---------------------- --------------- 1 Rick Development,Sales 2 Chris Development 3 IOROI Sales,Marketing,HR