BigQueryでSTRINGを集約連結

はじめに

BigQueryのSTRINGを連結する際、調べるとサブクエリでやORDER BYやDISTINCTしてから連結する記事が見つかります。
しかしサブクエリは他の人が見た時にとても分かりづらく、中々メンテナンスのコストがかかってしまいます。

そのためサブクエリを使わないSTRINGを連結するやり方です。
とは言ってもいくつかBigQueryのドキュメントを見れば分かることです。

BigQueryやSQLServerではSTRING_AGGを使えばかなり楽に書けますという話です。

使い所

以下のようなuser_idに対し、何のフルーツを食べたかというテーブルデータがあったとします。

WITH fruit_log AS (
  SELECT 'XXX' AS user_id, 'Apple' AS ate_fruit
  UNION ALL SELECT 'XXX', 'Banana'
  UNION ALL SELECT 'YYY', 'Orange'
  UNION ALL SELECT 'YYY', 'Apple'
  UNION ALL SELECT 'ZZZ', 'Orange'
  UNION ALL SELECT 'ZZZ', 'Orange'
)

SELECT * FROM fruit_log

このとき以下のようにuser_idごとに食べたフルーツを羅列したい場合があります。

SQL

SELECT
  user_id,
  STRING_AGG(DISTINCT ate_fruit ORDER BY ate_fruit) AS ate_fruits
FROM
  fruit_log
GROUP BY
  user_id

終わりに

単発分析ならともかく、レポートなどで運用するクエリを書く場合は可読性を意識して書きましょう。

MySQLを使っていたので知らなかったのですがSTRING_AGGが強い。