G-gen の杉村です。BigQuery の特殊な GROUP BY 構文である GROUP BY GROUPING SETS GROUP BY CUBE GROUP BY ROLLUP について解説します。

はじめに

BigQuery で以下の特殊な GROUP BY 構文が、2023年10月9日に Preview 公開、2024年2月26日に一般公開（GA）されました。

GROUP BY GROUPING SETS
GROUP BY CUBE
GROUP BY ROLLUP (以前からあったが groupable items sets を指定できるようになった)

これらの構文は他の分析用データベース製品でも一般的なものです。改めて、どのように使うのかを確認してみました。

GROUP BY GROUPING SETS

GROUP BY GROUPING SETS は GROUP BY 句で列を複数指定して、それぞれの集計結果を合わせて一つの結果として返すための構文です。

実例を見てみましょう。

WITH Products AS (
  SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
  SELECT 'shirt', 't-shirt', 8 UNION ALL
  SELECT 'shirt', 'polo', 25 UNION ALL
  SELECT 'pants', 'jeans', 6
)
  
SELECT product_type, product_name, SUM(product_count) AS product_sum
FROM Products
GROUP BY GROUPING SETS (product_type, product_name)
ORDER BY product_name;

出力結果

product_type	product_name	product_sum
shirt	(null)	36
pants	(null)	6
(null)	jeans	6
(null)	polo	25
(null)	t-shirt	11

このように、異なる列をキーとした集計結果が一つの結果テーブルとなって返ってきました。使わない列には null が入っています。つまり出力結果には

product_type ごとの集計 (product_name 列が null)
product_name ごとの集計 (product_type 列が null)

が入っています。

これは、以下のように通常の GROUP BY を使った集計クエリを2つ実行して UNION ALL した結果と全く同じです。

WITH Products AS (
  SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
  SELECT 'shirt', 't-shirt', 8 UNION ALL
  SELECT 'shirt', 'polo', 25 UNION ALL
  SELECT 'pants', 'jeans', 6
)
  
SELECT product_type, NULL AS product_name, SUM(product_count) AS product_sum
FROM Products
GROUP BY product_type
  
UNION ALL
  
SELECT NULL AS product_type, product_name, SUM(product_count) AS product_sum
FROM Products
GROUP BY product_name
  
ORDER BY product_name;

GROUP BY CUBE

GROUP BY CUBE は GROUP BY 句で複数列を指定し、指定された全ての列の全ての組み合わせの集計結果を返す構文です。

WITH Products AS (
  SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
  SELECT 'shirt', 't-shirt', 8 UNION ALL
  SELECT 'shirt', 'polo', 25 UNION ALL
  SELECT 'pants', 'jeans', 6
)
  
SELECT product_type, product_name, SUM(product_count) AS product_sum
FROM Products
GROUP BY CUBE (product_type, product_name)
ORDER BY product_type, product_name;

出力結果

product_type	product_name	product_sum
(null)	(null)	42
(null)	jeans	6
(null)	polo	25
(null)	t-shirt	11
pants	(null)	6
pants	jeans	6
shirt	(null)	36
shirt	polo	25
shirt	t-shirt	11

出力結果には

全ての行の集計 (product_name 列も product_type 列も null)
product_name ごとの集計 (product_type 列が null)
product_type ごとの集計 (product_name 列が null)
product_type + product_name の集計 (null が無い)

が含まれています。つまり GROUP BY CUBE (product_type, product_name) の括弧内で指定した列 (+ 列なし) の全ての組み合わせで集計をしています。

GROUP BY ROLLUP

GROUP BY ROLLUP は集計に使う列を複数指定して、その列で合計結果を積み上げて一つの結果として返す構文です。

実例を見てみましょう。

WITH Products AS (
  SELECT 'shirt' AS product_type, 't-shirt' AS product_name, 3 AS product_count UNION ALL
  SELECT 'shirt', 't-shirt', 8 UNION ALL
  SELECT 'shirt', 'polo', 25 UNION ALL
  SELECT 'pants', 'jeans', 6
)
  
SELECT product_type, product_name, SUM(product_count) AS product_sum
FROM Products
GROUP BY ROLLUP (product_type, product_name)
ORDER BY product_type, product_name;

出力結果

product_type	product_name	product_sum
(null)	(null)	42
pants	(null)	6
pants	jeans	6
shirt	(null)	36
shirt	polo	25
shirt	t-shirt	11

出力結果には

全ての行の合計 (product_name 列も product_type 列も null)
product_type ごとの集計 (product_name 列が null)
product_type + product_name の集計 (null が無い)

が含まれています。考え方としては「積み上げ」で、細かい集計から次々により大きい項目へ集計している、と考えることができます。

次の例のほうが分かりやすいかもしれません (数字はでたらめです)。

WITH cities AS (
  SELECT '日本' AS nation, '東京' AS prefecture, '新宿区' AS city, 100 AS population UNION ALL
  SELECT '日本', '東京', '豊島区', 80 UNION ALL
  SELECT '日本','東京', '品川区', 50 UNION ALL
  SELECT '日本','埼玉県', 'ふじみ野市', 60 UNION ALL
  SELECT '日本','埼玉県', 'さいたま市', 70 UNION ALL
  SELECT 'アメリカ合衆国','カリフォルニア州', 'サンディエゴ市', 50
)
  
SELECT nation, prefecture, city, SUM(population) AS population_sum
FROM cities
GROUP BY ROLLUP (nation, prefecture, city)
ORDER BY prefecture, city;

出力結果

nation	prefecture	city	population_sum
(null)	(null)	(null)	410
日本	(null)	(null)	360
アメリカ合衆国	(null)	(null)	50
アメリカ合衆国	カリフォルニア州	(null)	50
アメリカ合衆国	カリフォルニア州	サンディエゴ市	50
日本	埼玉県	(null)	130
日本	埼玉県	さいたま市	70
日本	埼玉県	ふじみ野市	60
日本	東京	(null)	230
日本	東京	品川区	50
日本	東京	新宿区	100
日本	東京	豊島区	80