SQL練習室
← SQLガイド

SQLのROLLUP・CUBE・GROUPING SETSとは?小計と総計を1回で集計する方法

ROLLUP・CUBE・GROUPING SETSは、GROUP BYの中で複数の集計単位をまとめて指定し、小計や総計の行を1回のSELECTで出す構文です。地域別・カテゴリ別の売上を例に、3つの違いと、小計行のNULLと元データのNULLを見分ける方法を確認します。

例で使う表

salesは1行が売上1件で、regionは地域、categoryは商品カテゴリ、amountは金額(円)です。東は食品2件・雑貨1件、西は食品1件・雑貨2件に加えて、カテゴリが未入力(NULL)の売上が1件あります。全体の合計は6,400円です。

sales:売上7件
idregioncategoryamount
1東食品1000
2東食品2000
3東雑貨500
4西食品1500
5西雑貨700
6西雑貨300
7西NULL400

ROLLUP・CUBE・GROUPING SETSの基本構文

sql
SELECT region, SUM(amount)
FROM sales
GROUP BY ROLLUP (region)
ORDER BY region;

ROLLUPで地域別の合計と総計を1回で出す

GROUP BY ROLLUP (region)は、地域ごとの合計に加えて、全体を1つにまとめた総計の行も返します。東が3,500円、西が2,900円(カテゴリNULLの400円を含む)、総計が6,400円です。総計の行では、まとめたことでregionがNULLになります。ORDER BY regionのNULLは既定で最後に並ぶため、総計が最後の行です。通常のGROUP BY regionの結果に、総計の1行が足されたものと考えてください。

sql
SELECT region, SUM(amount)
FROM sales
GROUP BY ROLLUP (region)
ORDER BY region;
実行結果
regionsum
東3500
西2900
NULL6400

列を2つ並べると、右の列から順に小計になる

ROLLUP (region, category)は、左から右へ階層的に集計します。regionとcategoryの組ごとの明細、regionごとの小計(categoryがNULL)、全体の総計(両方NULL)の3種類が出ます。東の小計は3,500円、西の小計は2,900円、総計は6,400円です。categoryだけの合計(食品の全地域合計など)は出ません。列の並び順が小計の単位を決めるので、ROLLUP (category, region)にすると、categoryごとの小計になります。行の並びは保証されないので、結果の行順は一例です。

sql
SELECT region, category, SUM(amount)
FROM sales
GROUP BY ROLLUP (region, category);
実行結果
regioncategorysum
東食品3000
東雑貨500
東NULL3500
西食品1500
西雑貨1000
西NULL400
西NULL2900
NULLNULL6400

CUBEは列の全組み合わせで集計する

CUBE (region, category)は、ROLLUPの結果に加えて、categoryだけの合計(regionがNULL)も出します。食品が4,500円、雑貨が1,500円、カテゴリ未入力が400円です。2列のCUBEは「両方」「regionのみ」「categoryのみ」「なし(総計)」の4通りを集計し、この表では11行になります。列が増えるほど組み合わせが2のべき乗で増え、行数も急に増えます。使う集計単位が決まっているなら、次のGROUPING SETSで絞るほうが無駄がありません。

sql
SELECT region, category, SUM(amount)
FROM sales
GROUP BY CUBE (region, category);
実行結果
regioncategorysum
東食品3000
東雑貨500
東NULL3500
西食品1500
西雑貨1000
西NULL400
西NULL2900
NULL食品4500
NULL雑貨1500
NULLNULL400
NULLNULL6400

GROUPING SETSで必要な集計単位だけを指定する

GROUPING SETSは、集計したい単位を1つずつカッコで列挙します。((region), (category))なら、地域別の合計とカテゴリ別の合計だけが出て、明細も総計も出ません。東が3,500円、西が2,900円、食品が4,500円、雑貨が1,500円、カテゴリ未入力が400円の5行です。使われなかった列はNULLで返ります。()を並べると総計の行も加えられます。ROLLUPやCUBEは、GROUPING SETSを短く書くための省略形です。

sql
SELECT region, category, SUM(amount)
FROM sales
GROUP BY GROUPING SETS ((region), (category));
実行結果
regioncategorysum
東NULL3500
西NULL2900
NULL食品4500
NULL雑貨1500
NULLNULL400

GROUPING()で小計行のNULLと元データのNULLを見分ける

ROLLUPの結果では、小計行のcategoryがNULLですが、元データでカテゴリが未入力の行もNULLです。西には「400円のカテゴリ未入力の明細」と「2,900円の西の小計」の2行があり、categoryの見た目だけでは区別できません。GROUPING(category)は、その行でcategoryが集計でまとめられたら1、そうでなければ0を返します。未入力の明細は0、小計は1です。この列を見れば、2行を確実に区別できます。ORDER BYにも使えるので、同じ地域の中で明細を先に、小計を後ろに並べられます。

sql
SELECT region, category, GROUPING(category) AS is_subtotal, SUM(amount)
FROM sales
GROUP BY ROLLUP (region, category)
ORDER BY region, GROUPING(category), category;
実行結果
regioncategoryis_subtotalsum
東雑貨0500
東食品03000
東NULL13500
西雑貨01000
西食品01500
西NULL0400
西NULL12900
NULLNULL16400

UNION ALLで書いた場合と比べる

ROLLUP (region)と同じ結果は、地域別の集計と総計を別々のSELECTで作ってUNION ALLでつないでも得られます。UNION ALLの2つ目のSELECTでは、regionの位置にNULLを書きます。結果は同じ3行ですが、salesを2回読み、集計の条件を2か所に書く必要があります。条件を変えるときに片方を直し忘れると、小計と総計の合わせ方がずれます。ROLLUPなら、表を読むのは1回で、書く集計条件も1つで済みます。

sql
SELECT region, SUM(amount)
FROM sales
GROUP BY region
UNION ALL
SELECT NULL, SUM(amount)
FROM sales;
実行結果
regionsum
東3500
西2900
NULL6400

ROLLUP・CUBE・GROUPING SETSでよくある間違い

category IS NULLで小計行を探すと、元データのNULLも混ざる

ROLLUPの結果から小計を取り出すつもりでHAVING category IS NULLと書くと、小計行に加えて、カテゴリ未入力の明細(西の400円)も残ります。小計や総計の行だけを残すには、HAVING GROUPING(category) = 1を使います。

sql
SELECT region, category, SUM(amount)
FROM sales
GROUP BY ROLLUP (region, category)
HAVING category IS NULL;

実際に1問解いてみる

ROLLUP

売上(sales)を地域(region)ごとに合計し、全体の総計の行も加えて、regionと合計金額をこの順に取得してください。総計の行のregionはNULLのままで構いません。行順は問いません。別名は付けません。

テーブル

読み込み中…

実行結果

関連構文

ROLLUP・CUBE・GROUPING SETSについてよくある質問

ROLLUPとCUBEはどう使い分けますか?
地域→カテゴリのように上下関係のある集計では、小計と総計だけが必要なのでROLLUPを使います。どの列の組み合わせでも合計が欲しい、クロス集計のような場合にCUBEを使います。CUBEは行数が増えやすいので、必要な単位が決まっているならGROUPING SETSで指定します。
小計の行に「小計」「総計」と表示できますか?
できます。GROUPING()の結果をCASEで分岐し、1のときに「小計」などの文字を返します。COALESCEでNULLを置き換えると、元データのNULLまで「小計」になってしまうので、GROUPING()で判定してください。
ROLLUPで並び順は決まりますか?
決まりません。小計や総計の行がどこに出るかは保証されないため、見せたい順序はORDER BYで指定します。ORDER BYではNULLが既定で最後に並ぶので、列を並べればたいてい小計が各グループの後ろ、総計が最後に来ます。
ROLLUP・CUBE・GROUPING SETSは他のデータベースでも使えますか?
これらは標準SQLにある構文ですが、対応範囲や書き方はデータベースごとに異なります。たとえばMySQLでは、GROUP BY ... WITH ROLLUPの形で小計と総計を出せます。この記事の書き方はPostgreSQLのものです。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。