BigQueryではSTRUCT
(構造体)型やARRAY
(配列)型が使えます
データ型 | BigQuery | Google Cloud
STRUCT
型のカラムでGROUP BY
しようとするとGrouping by expressions of type STRUCT is not allowed at
のようなエラーが出てきて実行することができません
同様にARRAY
型のカラムだとGrouping by expressions of type ARRAY is not allowed at
のようになります
これをどうにかしてGROUP BY
することを考えます
方法1: 構造体の中身を一度展開してGROUP BY
する
構造体の場合、展開して新しいSTRUCT
に入れ直して、展開したそれぞれのカラムでGROUP BY
すると実行できます
SELECT STRUCT(s.x, s.y) AS s, COUNT(*) AS c FROM ( SELECT STRUCT(1 AS x, 1 AS y) AS s UNION ALL SELECT STRUCT(2 AS x, 2 AS y) AS s UNION ALL SELECT STRUCT(1 AS x, 1 AS y) AS s ) AS src GROUP BY src.s.x, src.s.y
もしくはANY_VALUE()
を使っても書けます
SELECT ANY_VALUE(s) AS s, COUNT(*) AS c FROM ( SELECT STRUCT(1 AS x, 1 AS y) AS s UNION ALL SELECT STRUCT(2 AS x, 2 AS y) AS s UNION ALL SELECT STRUCT(1 AS x, 1 AS y) AS s ) AS src GROUP BY src.s.x, src.s.y
配列の場合も上の構造体の場合と同様に書けるはずですが、要素数が多いと大変なので次に紹介する方法をおすすめします
方法2: 構造体や配列を文字列に変換してGROUP BY
する
構造体をFORMAT()
関数で文字列に変換して、この文字列でGROUP BY
するという手もあります
'%T'
へのフォーマットはBigQueryのリテラルの形の文字列になるので(ほとんどの場合)元の構造体でGROUP BY
するのと同じような結果が得られるはずです
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#t_and_t_behavior
というわけで以下のように書くことで、だいたいいい感じにSTRUCT
型のカラムで集計できるはずです
SELECT ANY_VALUE(s) AS s, COUNT(*) AS c FROM ( SELECT STRUCT(1 AS x, 1 AS y) AS s UNION ALL SELECT STRUCT(2 AS x, 2 AS y) AS s UNION ALL SELECT STRUCT(1 AS x, 1 AS y) AS s ) GROUP BY FORMAT('%T', s)
この方法はそのままARRAY
型のカラムにも適用できます
以下のように書くことで配列をキーとした集計ができます
SELECT ANY_VALUE(a) AS a, COUNT(*) AS c FROM ( SELECT [1, 2] AS a UNION ALL SELECT [1, 2, 3] AS a UNION ALL SELECT [1, 2] AS a ) GROUP BY FORMAT('%T', a)