BigQueryのマルチレベル集計を試してみる
はじめに
データアナリティクス事業本部のkobayashiです。
BigQueryのSQLでは「集計した結果をさらに集計したい」という場面がよくあります。例えば「店舗ごとの日次売上合計の平均」のように、いったん日付単位で合計してから、その合計値を店舗単位で平均する、といった2段階の集計です。標準SQLでは集計関数の引数に別の集計関数を書くことができないため、これまではサブクエリやCTEで一段目の集計を行ってから外側で集計する、という2段階のSELECTに分ける必要がありました。
2026年7月8日のアップデートでBigQueryに マルチレベル集計（Multi-level aggregation） がPreviewとして追加され、集計関数の引数に別の集計関数を書けるようになりました。今回はこのマルチレベル集計を試してみます。
マルチレベル集計とは
マルチレベル集計は、集計関数の呼び出しに GROUP BY 修飾子を付けることで、集計関数の引数に別の集計関数を書けるようにする構文です。
外側の集計関数( 内側の集計関数(...) GROUP BY 内側のグループ化キー )
内側の集計関数は、外側のクエリの GROUP BY に加えて、集計関数呼び出しに付けた GROUP BY 修飾子でグループ化されて評価されます。その中間集計結果を外側の集計関数がさらに集計する、という2段階の集計を1つの式で表現できます。
サンプルデータの準備
店舗・日付・売上を持つ、日次の売上明細テーブルを用意します。同じ店舗・同じ日付に複数の売上レコードが存在する構成です。今回は bq CLI を使って作成します。
CREATE OR REPLACE TABLE data_set_sample.daily_sales (
store_name STRING,
sale_date DATE,
revenue INT64
);
INSERT INTO data_set_sample.daily_sales (store_name, sale_date, revenue) VALUES
('店舗A', DATE '2026-07-01', 100),
('店舗A', DATE '2026-07-01', 100),
('店舗A', DATE '2026-07-02', 150),
('店舗A', DATE '2026-07-03', 300),
('店舗A', DATE '2026-07-04', 100),
('店舗A', DATE '2026-07-05', 250),
('店舗B', DATE '2026-07-01', 30),
('店舗B', DATE '2026-07-02', 60),
('店舗B', DATE '2026-07-02', 30),
('店舗B', DATE '2026-07-03', 200),
('店舗B', DATE '2026-07-04', 100),
('店舗B', DATE '2026-07-05', 80),
('店舗C', DATE '2026-07-01', 300),
('店舗C', DATE '2026-07-02', 200),
('店舗C', DATE '2026-07-03', 400),
('店舗C', DATE '2026-07-04', 250),
('店舗C', DATE '2026-07-05', 350);
実行結果は以下のようになります。
$ bq query '<上記SQL>'
Waiting on bqjob_... (1s) Current status: DONE
Created {プロジェクト名}.data_set_sample.daily_sales
Number of affected rows: 17
このデータに対して日付ごとに合計を取ってみると、各店舗の日次売上合計は以下のようになります。
$ bq query --format=pretty '
SELECT store_name, sale_date, SUM(revenue) AS daily_revenue
FROM data_set_sample.daily_sales
GROUP BY store_name, sale_date
ORDER BY store_name, sale_date;'
+------------+------------+---------------+
| store_name | sale_date | daily_revenue |
+------------+------------+---------------+
| 店舗A | 2026-07-01 | 200 |
| 店舗A | 2026-07-02 | 150 |
| 店舗A | 2026-07-03 | 300 |
| 店舗A | 2026-07-04 | 100 |
| 店舗A | 2026-07-05 | 250 |
| 店舗B | 2026-07-01 | 30 |
| 店舗B | 2026-07-02 | 90 |
| 店舗B | 2026-07-03 | 200 |
| 店舗B | 2026-07-04 | 100 |
| 店舗B | 2026-07-05 | 80 |
| 店舗C | 2026-07-01 | 300 |
| 店舗C | 2026-07-02 | 200 |
| 店舗C | 2026-07-03 | 400 |
| 店舗C | 2026-07-04 | 250 |
| 店舗C | 2026-07-05 | 350 |
+------------+------------+---------------+
この「日次売上合計」を店舗ごとに平均した値（店舗A: (200+150+300+100+250)/5 = 200、店舗B: (30+90+200+100+80)/5 = 100、店舗C: (300+200+400+250+350)/5 = 300）を求めることをゴールにします。
これまでの書き方（サブクエリ）
マルチレベル集計を使わない場合、まずサブクエリで日付ごとの合計を求め、外側でそれを店舗ごとに平均します。
SELECT
store_name,
AVG(daily_revenue) AS avg_daily_revenue
FROM (
SELECT
store_name,
sale_date,
SUM(revenue) AS daily_revenue
FROM data_set_sample.daily_sales
GROUP BY store_name, sale_date
)
GROUP BY store_name
ORDER BY store_name;
実行すると以下の結果が得られます。
$ bq query --format=pretty '<上記SQL>'
+------------+-------------------+
| store_name | avg_daily_revenue |
+------------+-------------------+
| 店舗A | 200.0 |
| 店舗B | 100.0 |
| 店舗C | 300.0 |
+------------+-------------------+
意図通りの結果は得られますが、一段目の集計のためだけにサブクエリを1つ挟む必要があります。
マルチレベル集計で書く
マルチレベル集計を使うと、サブクエリなしで同じ結果を1つのクエリで表現できます。内側の SUM(revenue) に GROUP BY sale_date 修飾子を付け、それを外側の AVG で集計します。
SELECT
store_name,
AVG(SUM(revenue) GROUP BY sale_date) AS avg_daily_revenue
FROM data_set_sample.daily_sales
GROUP BY store_name
ORDER BY store_name;
結果はサブクエリ版と一致します。
$ bq query --format=pretty '<上記SQL>'
+------------+-------------------+
| store_name | avg_daily_revenue |
+------------+-------------------+
| 店舗A | 200.0 |
| 店舗B | 100.0 |
| 店舗C | 300.0 |
+------------+-------------------+
内側の SUM(revenue) は、外側のクエリの GROUP BY store_name に集計関数側の GROUP BY sale_date を加えた「店舗×日付」でグループ化されて日次合計を計算し、その結果を外側の AVG が店舗ごとに平均しています。サブクエリのネストが消え、「日次合計の平均」という意図がそのままSQLに表れる形で書けました。
応用: 別の外側集計や複数指標
外側の集計関数は AVG に限りません。同じ「日次売上合計」に対して、店舗ごとの最大値・平均値・件数などを1クエリでまとめて求められます。
SELECT
store_name,
AVG(SUM(revenue) GROUP BY sale_date) AS avg_daily_revenue,
MAX(SUM(revenue) GROUP BY sale_date) AS max_daily_revenue,
COUNT(SUM(revenue) GROUP BY sale_date) AS active_days
FROM data_set_sample.daily_sales
GROUP BY store_name
ORDER BY store_name;
実行すると以下のようになります。
$ bq query --format=pretty '<上記SQL>'
+------------+-------------------+-------------------+-------------+
| store_name | avg_daily_revenue | max_daily_revenue | active_days |
+------------+-------------------+-------------------+-------------+
| 店舗A | 200.0 | 300 | 5 |
| 店舗B | 100.0 | 200 | 5 |
| 店舗C | 300.0 | 400 | 5 |
+------------+-------------------+-------------------+-------------+
max_daily_revenue は日次合計の最大（店舗Aなら300）、active_days は売上があった日数（日次合計のグループ数）を表します。それぞれサブクエリを書き分けることなく、日次集計を土台にした複数の指標を並べて取得できています。
外側のクエリで GROUP BY を指定しなければ、テーブル全体を対象に日次合計の平均を求めることもできます。
SELECT
AVG(SUM(revenue) GROUP BY store_name, sale_date) AS avg_daily_revenue_all
FROM data_set_sample.daily_sales;
この場合、内側は「店舗×日付」の15グループ（店舗A: 200/150/300/100/250, 店舗B: 30/90/200/100/80, 店舗C: 300/200/400/250/350）で日次合計を求め、その平均 (合計3000)/15 = 200 が返ります。
$ bq query --format=pretty '<上記SQL>'
+-----------------------+
| avg_daily_revenue_all |
+-----------------------+
| 199.99999999999997 |
+-----------------------+
理論値200に対して199.99999999999997という浮動小数点誤差込みの値が返っていますが、これはFLOAT64でのAVG計算に伴う丸め誤差で、必要に応じてROUND(..., 2)などで整えて使ってください。
実務でよく使う応用パターン
AVG / MAX / COUNT 以外の集計関数もマルチレベル集計の外側に据えられます。ここでは実務でよく出てくる3つのパターンを紹介します。
閾値超えの日数を数える（COUNTIF）
「日次売上が200以上の日が何日あったか」といった閾値越えの日数は、集計関数の内側で日次合計を作り、その結果を外側の COUNTIF で数える形で1クエリにまとめられます。ポイントは GROUP BY sale_date 修飾子は外側の COUNTIF 側に付ける ことです。内側の SUM(revenue) はその修飾子で「店舗×日付」でグループ化され、外側の COUNTIF がその真偽値を店舗ごとにカウントします。
SELECT
store_name,
COUNTIF(SUM(revenue) >= 200 GROUP BY sale_date) AS days_over_200
FROM data_set_sample.daily_sales
GROUP BY store_name
ORDER BY store_name;
$ bq query --format=pretty '<上記SQL>'
+------------+---------------+
| store_name | days_over_200 |
+------------+---------------+
| 店舗A | 3 |
| 店舗B | 1 |
| 店舗C | 5 |
+------------+---------------+
店舗Aは日次合計 200/150/300/100/250 のうち3日、店舗Bは 30/90/200/100/80 のうち1日、店舗Cは 300/200/400/250/350 の5日すべてが200以上、というKPIをサブクエリなしで抽出できました。同じ考え方で「1000円未満の閑散日」「特定商品カテゴリで一定金額を超えた日」なども1式で書けます。
日次合計の時系列を配列で持たせる（ARRAY_AGG）
店舗ごとの日次売上を 時系列の配列 で持たせておくと、可視化やダウンストリーム連携の下準備として便利です。ARRAY_AGG にマルチレベル集計を組み合わせるには、以下の2つの制約に注意が必要です。
ARRAY_AGG側でIGNORE NULLSを明示する必要があるARRAY_AGG(... ORDER BY ...)は使えない（配列内の順序は保証されない）
順序を保証したい場合は、日付とセットで STRUCT に詰めてダウンストリームでソートするのが実用的です。
SELECT
store_name,
ARRAY_AGG(
STRUCT(sale_date, SUM(revenue) AS daily_revenue)
IGNORE NULLS
GROUP BY sale_date
) AS daily_series
FROM data_set_sample.daily_sales
GROUP BY store_name
ORDER BY store_name;
$ bq query --format=json '<上記SQL>' | jq
[
{
"store_name": "店舗A",
"daily_series": [
{ "sale_date": "2026-07-01", "daily_revenue": "200" },
{ "sale_date": "2026-07-04", "daily_revenue": "100" },
{ "sale_date": "2026-07-02", "daily_revenue": "150" },
{ "sale_date": "2026-07-05", "daily_revenue": "250" },
{ "sale_date": "2026-07-03", "daily_revenue": "300" }
]
},
{
"store_name": "店舗B",
"daily_series": [
{ "sale_date": "2026-07-01", "daily_revenue": "30" },
{ "sale_date": "2026-07-02", "daily_revenue": "90" },
{ "sale_date": "2026-07-05", "daily_revenue": "80" },
{ "sale_date": "2026-07-04", "daily_revenue": "100" },
{ "sale_date": "2026-07-03", "daily_revenue": "200" }
]
},
{
"store_name": "店舗C",
"daily_series": [
{ "sale_date": "2026-07-02", "daily_revenue": "200" },
{ "sale_date": "2026-07-04", "daily_revenue": "250" },
{ "sale_date": "2026-07-01", "daily_revenue": "300" },
{ "sale_date": "2026-07-05", "daily_revenue": "350" },
{ "sale_date": "2026-07-03", "daily_revenue": "400" }
]
}
]
配列内の要素順は保証されないため、順序が意味を持つ用途では sale_date を含めておいて後段でソートする、という設計にしておくのが安全です。
中間集計の結果でグループを絞り込む（HAVING）
「日次売上が一度でも300円以上に達した店舗」だけを抽出したい、といったケースでは、HAVING 句の中でもマルチレベル集計を使えます。ポイントは、GROUP BY 修飾子付きの集計を HAVING の条件式内でそのまま呼び出せる ことです。
SELECT
store_name,
AVG(SUM(revenue) GROUP BY sale_date) AS avg_daily_revenue,
MAX(SUM(revenue) GROUP BY sale_date) AS max_daily_revenue
FROM data_set_sample.daily_sales
GROUP BY store_name
HAVING MAX(SUM(revenue) GROUP BY sale_date) >= 300
ORDER BY store_name;
$ bq query --format=pretty '<上記SQL>'
+------------+-------------------+-------------------+
| store_name | avg_daily_revenue | max_daily_revenue |
+------------+-------------------+-------------------+
| 店舗A | 200.0 | 300 |
| 店舗C | 300.0 | 400 |
+------------+-------------------+-------------------+
店舗A（最大300）と店舗C（最大400）が残り、店舗B（最大200）は HAVING で除外されました。「一度でも大口の売上があった店舗だけの平均を見たい」のような分析でも、サブクエリを重ねずに書けます。なお、後述の注意点にあるように HAVING MIN / HAVING MAX 句（GROUP BY の後ろに書く特殊な句）とマルチレベル集計は併用できませんが、通常の HAVING <集計式> はこのように問題なく使えます。
使う上での注意点
マルチレベル集計を使う上での制約をまとめます。
| 項目 | 内容 |
|---|---|
| GROUP BY 修飾子が必須 | 集計関数の引数に集計関数を書けるのは、外側の集計関数に GROUP BY 修飾子が付いている場合のみ |
| 使える箇所 | マルチレベル集計が使えるのは、集計関数の引数・DISTINCT 句・集計関数呼び出しの GROUP BY 修飾子の中に限られる |
| HAVING MIN / MAX との併用不可 | マルチレベル集計の集計関数では、GROUP BY 修飾子と HAVING MIN / HAVING MAX 句を同時に使えない（通常の HAVING <集計式> は使える） |
| ARRAY_AGG の制約 | ARRAY_AGG にマルチレベル集計を組み合わせる場合、IGNORE NULLS が必須で、ORDER BY は使えない。順序が意味を持つ用途では STRUCT にキーを含めておいて後段でソートする |
| FLOAT64 の丸め誤差 | 外側 AVG などは FLOAT64 で計算されるため、想定値からごく僅かな丸め誤差が乗ることがある（ROUND で整えて出力するのが安全） |
| 提供状況 | 執筆時点ではPreview |
まとめ
BigQueryのマルチレベル集計を試してみました。集計関数の呼び出しに GROUP BY 修飾子を付けることで、集計関数の引数に別の集計関数を書けるようになり、「集計した結果をさらに集計する」処理をサブクエリなしで表現できます。日次合計の平均や最大といった2段階の集計を素直に書けるため、これまでサブクエリのネストで書いていたクエリを簡潔にできる便利な機能だと思います。
最後まで読んで頂いてありがとうございました。
