BigQueryでGROUP BYに新機能が追加されました

Google Cloud データエンジニアのはんざわです。

2023年10月9日にGROUP BYの新機能がプレビューとして追加されました。

October 09, 2023

具体的には次の3つです。

• Group rows by GROUPING SETS
• Group rows by CUBE
• Group rows by ROLLUP

では実際に触ってみましょう。

触ってみる

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の新機能を紹介しました。
これにより複雑な集計を簡単に取得することができるようになったと思います。使用にはある程度慣れる必要はあると思いますが、是非有効活用してみてください。