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
- 作者:株式会社トップゲート
- 発売日: 2019/11/29
- メディア: Kindle版
