小計・総計を一括で出す - ROLLUP / CUBE / GROUPING SETS

小計・総計を 1 クエリで。ROLLUP は階層、CUBE は全組合せ、GROUPING SETS は任意指定

概念図

構文

sql

GROUP BY ROLLUP(a, b) | CUBE(a, b) | GROUPING SETS ((a,b),(a),())

サンプル

地域 > カテゴリの階層で小計と総計を一度に取得

sql

SELECT
  COALESCE(region,   '(total)') AS region,
  COALESCE(category, '(subtotal)') AS category,
  SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category);

ROLLUP — 階層の小計

ROLLUP(a, b, c) は指定した列を 右から順に外して 集計セットを作ります。ROLLUP(region, category) なら次の 3 セットが一度に得られます。

(region, category) — 明細レベル
(region) — 地域ごとの小計
() — 総計

地域 > カテゴリのような 階層構造 に合致する集計を 1 クエリで返すのに最適です。

CUBE — 全組合せ

CUBE(a, b) は指定列の 全ての部分集合でグループ化します。CUBE(region, category) なら (region, category), (region), (category), () の 4 セット。OLAP のピボット表をフラットに出したいときに便利です。

sql

SELECT region, category, SUM(amount)
FROM sales
GROUP BY CUBE(region, category);

GROUPING SETS — 任意の組合せ

GROUPING SETS は 欲しい集計軸だけを列挙する最も柔軟な書き方です。ROLLUP / CUBE では不要な集計まで出してしまうとき、GROUPING SETS でピンポイントに指定できます。

sql

SELECT region, category, product_id, SUM(amount)
FROM sales
GROUP BY GROUPING SETS (
  (region, category, product_id),  -- 明細
  (region, category),              -- 地域×カテゴリ
  (region),                        -- 地域
  ()                               -- 総計
);

GROUPING() で小計行を識別する

ROLLUP / CUBE の結果は、小計行の対象外列が NULL で埋められます。データ側にもともと NULL がある場合、「それが本当の NULL なのか、小計による NULL なのか」が区別できません。

そこで GROUPING(col) を使います。小計で発生した NULL なら 1、元データの値（NULL 含む）なら 0 を返すので、CASE で表示を切り替えられます。

sql

SELECT
  CASE WHEN GROUPING(region)   = 1 THEN '(all regions)'   ELSE region   END AS region,
  CASE WHEN GROUPING(category) = 1 THEN '(all categories)' ELSE category END AS category,
  SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category);

RDBMS 対応状況と落とし穴

PostgreSQL 9.5+ / SQL Server / Oracle / MySQL 8.0+: ROLLUP / CUBE / GROUPING SETS すべて対応
MySQL は書式が特殊: 歴史的に GROUP BY a, b WITH ROLLUP という書き方のみ対応していた。8.0.1 以降で標準の ROLLUP(...) も使える
SQLite: いずれも未対応。UNION ALL で代用する
NULL の混同: 小計 NULL と元データ NULL を GROUPING() で区別すること