BigQueryの標準SQLでGROUP_CONCATしたいときはSTRING_AGG

BigQueryでGROUP BYした項目を文字列結合したいとき、レガシーSQLでは`GROUP_CONCAT`を使ってました。

標準SQLにはなく、検索しても見つけづらかったのでメモ。

結論

STRING_AGGが使えます。

STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]

標準 SQL の式、関数、演算子  |  BigQuery  |  Google Cloud

DISTINCTで重複除けるし、ORDER BYもここでできちゃうので、ID順とかで結合したいときこれだけでできてしまって超便利

例

こんなデータの場合、

WITH testData AS (
  SELECT 1 AS id, '赤身' as type,  'ブリ' AS name UNION ALL
  SELECT 2, '赤身', 'いわし' UNION ALL
  SELECT 3, '赤身', 'アジ' UNION ALL
  SELECT 4, '赤身', 'マグロ' UNION ALL
  SELECT 5, '赤身', 'カツオ' UNION ALL
  SELECT 6, '白身', 'タイ' UNION ALL
  SELECT 7, '赤身', 'サバ' UNION ALL
  SELECT 8, '白身', 'タラ' UNION ALL
  SELECT 9, '白身', 'フグ' UNION ALL
  SELECT 10, '白身', 'サケ' UNION ALL
  SELECT 11, '白身', 'サケ'
)
SELECT  * FROM testData;

1	赤身	ブリ
2	赤身	いわし
3	赤身	アジ
4	赤身	マグロ
5	赤身	カツオ
6	白身	タイ
7	赤身	サバ
8	白身	タラ
9	白身	フグ
10	白身	サケ
11	白身	サケ

SELECT  type, STRING_AGG(name)  FROM testData GROUP BY type ;

で

1	赤身	ブリ,いわし,アジ,マグロ,カツオ,サバ
2	白身	タイ,タラ,フグ,サケ,サケ

サケが重複してるのでDISTINCTつけると

SELECT  type, STRING_AGG(DISTINCT name)  FROM testData GROUP BY type ;

1	赤身	ブリ,いわし,アジ,マグロ,カツオ,サバ
2	白身	タイ,タラ,フグ,サケ

サケ消える。
DISTINCTとODER BYを同時に使うときは同じ項目でしかできない

SELECT  type, STRING_AGG(DISTINCT name ORDER BY name asc )  FROM testData GROUP BY type ;

1	赤身	いわし,アジ,カツオ,サバ,ブリ,マグロ
2	白身	サケ,タイ,タラ,フグ

区切り文字の指定はちょっと癖がある感じ

SELECT  type, STRING_AGG(DISTINCT name ,  'と'  ORDER BY name asc)  FROM testData GROUP BY type ;

1	赤身	いわしとアジとカツオとサバとブリとマグロ
2	白身	サケとタイとタラとフグ

あと今回はウィンドウ使ってないけどOVERで指定したり、LIMITも書けるので大抵のことはできそう。

Google Cloud Platform 実践 ビッグデータ分析基盤開発ストーリーで学ぶGoogle BigQuery