BigQueryでGROUP BYに新機能が追加されました
Google Cloud データエンジニアのはんざわです。
2023年10月9日にGROUP BYの新機能がプレビューとして追加されました。
具体的には次の3つです。
では実際に触ってみましょう。
触ってみる
Group rows by GROUPING SETS
検証用に下記のようなテーブルを作成しました。
※ 見易さ向上のため、検証のクエリ内ではサブクエリは省略しています。
WITH name_by_point AS (
SELECT 'apple' AS name, 'fruit' AS types, 5 AS point UNION ALL
SELECT 'melon', 'fruit', 2 UNION ALL
SELECT 'classmethod', 'method', 1 UNION ALL
SELECT 'apple', 'fruit', 15 UNION ALL
SELECT 'classmethod', 'company', 200 UNION ALL
SELECT 'apple', 'company', 100
)
SELECT
name,
types,
point
FROM
name_by_point
-------------- 結果 -------------- | name | types | point | |-------------|---------|-------| | apple | fruit | 5 | | melon | fruit | 2 | | classmethod | method | 1 | | apple | fruit | 15 | | classmethod | company | 200 | | apple | company | 100 |
さっそく、Group rows by GROUPING SETSを試してみます。
SELECT name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY GROUPING SETS ( name, types ) ORDER BY name DESC
---------------- 結果 ---------------- | name | type | sum_point | |-------------|---------|-----------| | melon | null | 2 | | classmethod | null | 201 | | apple | null | 120 | | null | fruit | 22 | | null | method | 1 | | null | company | 300 |
結果からわかるようにGROUP BY GROUPING SETSで選択したカラムのユニークな値ごとに集計関数の結果を返します。
公式ドキュメントにも記載してある通りGROUP BY GROUPING SETS(x,y)は、GROUP BY x UNION ALL GROUP BY yで書き換えることも可能です。
具体的に上記のクエリは下記のクエリに書き換えることが可能です。
SELECT name, STRING(NULL) AS types, SUM(point) AS sum_point FROM name_by_point GROUP BY 1, 2 UNION ALL SELECT STRING(NULL) AS name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY 1, 2
---------------- 結果 ---------------- | name | types | sum_point | |-------------|---------|-----------| | melon | null | 2 | | classmethod | null | 201 | | apple | null | 120 | | null | fruit | 22 | | null | method | 1 | | null | company | 300 |
上記のようなクエリの場合、対象に含めたいカラムが増えるとその分だけUNION ALLを追加する必要があり、増えた分だけクエリが複雑になってしまいます。
そこでGROUP BY GROUPING SETSを使用すれば対象に含めたいカラムを列挙するだけで容易に集計することが可能になります。
また、対象のカラムをさらに()でまとめることでカラムをグループ化することができます。
具体的には下記のようになります。
SELECT name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY GROUPING SETS ( (name, types) ) ORDER BY name DESC
---------------- 結果 ---------------- | name | types | sum_point | |-------------|---------|-----------| | melon | fruit | 2 | | classmethod | method | 1 | | classmethod | company | 200 | | apple | fruit | 20 | | apple | company | 100 |
上記の例では(name, types)でグループ化しました。これは下記のような通常のGROUP BYと同じ結果を得ることができます。
SELECT name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY 1, 2 ORDER BY name DESC
---------------- 結果 ---------------- | name | types | sum_point | |-------------|---------|-----------| | melon | fruit | 2 | | classmethod | method | 1 | | classmethod | company | 200 | | apple | fruit | 20 | | apple | company | 100 |
また、下記のようにグループ化と一緒に使用することができます。
SELECT name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY GROUPING SETS ( (name,types), types ) ORDER BY name DESC
--------------- 結果 --------------- | name | types | sum_point | |------------|---------|-----------| | melon | fruit | 2 | | classmethod| method | 1 | | classmethod| company | 200 | | apple | fruit | 20 | | apple | company | 100 | | null | fruit | 22 | | null | method | 1 | | null | company | 300 |
上記のクエリは下記のクエリと同じです。
SELECT name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY 1, 2 UNION ALL SELECT STRING(NULL) AS name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY 1, 2 ORDER BY name DESC
Group rows by CUBE
先ほど使用した検証用のテーブルで再度検証します。
さっそくGroup rows by CUBEを試してみます。
SELECT name, types, SUM(point) AS sum_point FROM name_by_point GROUP BY GROUPING SETS ( CUBE(name, types) ) ORDER BY name DESC
---------------- 結果 ---------------- | name | types | sum_point | |-------------|---------|-----------| | melon | null | 2 | | melon | fruit | 2 | | classmethod | null | 201 | | classmethod | method | 1 | | classmethod | company | 200 | | apple | null | 120 | | apple | fruit | 20 | | apple | company | 100 | | null | null | 323 | | null | fruit | 22 | | null | method | 1 | | null | company | 300 |
結果からもわかるようにGroup rows by CUBEを使用すると選択したカラムの全ての組み合わせの集計結果を取得することができます。
Group rows by ROLLUP
Group rows by ROLLUPは、左側のカラムを親とし、右側のカラムを子とした上で親ごとの子の組み合わせを列挙します。
具体的には下記のようになります。
WITH name_by_point AS (
SELECT 'apple' AS name, 'fruit' AS types, 5 AS point UNION ALL
SELECT 'melon', 'fruit', 2 UNION ALL
SELECT 'classmethod', 'method', 1 UNION ALL
SELECT 'apple', 'fruit', 15 UNION ALL
SELECT 'classmethod', 'company', 200 UNION ALL
SELECT 'apple', 'company', 100
)
SELECT
name,
types,
SUM(point) AS sum_point
FROM
name_by_point
GROUP BY GROUPING SETS (
ROLLUP(name, types)
)
ORDER BY name DESC
---------------- 結果 ---------------- | name | types | sum_point | |-------------|---------|-----------| | melon | null | 2 | | melon | fruit | 2 | | classmethod | null | 201 | | classmethod | method | 1 | | classmethod | company | 200 | | apple | null | 120 | | apple | fruit | 20 | | apple | company | 100 | | null | null | 323 |
Group rows by CUBEと結果が類似していますが、次の3行が抜けていることがわかると思います。これは左側のnameごとにtypesの組み合わせを列挙しているため次の組み合わせは抜けています。
| null | fruit | 22 | | null | method | 1 | | null | company | 300 |
まとめ
新しくプレビューとして追加された3つのGROUP BYの新機能を紹介しました。
これにより複雑な集計を簡単に取得することができるようになったと思います。使用にはある程度慣れる必要はあると思いますが、是非有効活用してみてください。
