はじめに
SQLには、単純なGroup byによる集計計算に加え、Rollup, Cube, Grouping Setsなどの指定カラムに対して追加集計計算を行う便利な機能があります。特にRollupは小計や総計を取得するのに便利で身近な存在ですが、動きをしっかり把握していないと集計対象が複雑になった場合にピンポイントで必要な集計を得るのが難しくなります。
例えば、以下の例では一つのカラムだけを対象にRollupを使用して総計を取得していますが、Group Byの対象が複数カラムになった場合に総計だけを取得するにはどう記述すればよいでしょうか？ また、4つの複合カラムで集計する場合に総計と特定のひとつのカラムの小計だけ取りたい場合はどうでしょう？
select item, sum(qty), count(*) from test_rollup group by rollup(item);
ITEM SUM(QTY) COUNT(*)
--------------- ---------- ----------------
Apple 80 3
Banana 20 1
Kiwi 105 2
205 6 <-- 総計
対象カラムが増えるとなかなか思い通りの集計ができなかったりしますが、実は単純な規則があります。ここでは、以下のような少し複雑なRollupの記述によってどのカラムを対象とした集計が行われるのかを順を追って示し、その算出方法を説明しています。また、Grouping SetsやCUBEにも言及しています。
| 対象 | 記述 |
|---|---|
| 複合カラム | Group by Rollup ( A, B, C ) |
| カッコつき | Group by Rollup ( (A, B), C ) |
| 複合Rollup | Group by Rollup ( A, B ), Rollup( C ) |
テストデータ
create table test_rollup (grp number, item varchar(30), loc varchar2(30), qty number);
insert into test_rollup values (1, 'Apple', 'USA', 10);
insert into test_rollup values (1, 'Banana', 'India', 20);
insert into test_rollup values (1, 'Kiwi', 'Aussie', 5);
insert into test_rollup values (2, 'Apple', 'Japan', 50);
insert into test_rollup values (2, 'Kiwi', 'Japan', 100);
insert into test_rollup values (2, 'Apple', 'Japan', 20);
GRP ITEM LOC QTY
---------- --------------- --------------- ----------
1 Apple USA 10
1 Banana India 20
1 Kiwi Aussie 5
2 Apple Japan 50
2 Apple Japan 20
2 Kiwi Japan 100
Rollup
マルチカラム集計
まず、単純なマルチカラム集計「Group by Rollup( A, B, C )」をみてみます。
以下の例で、複合カラムで集計する単純Group-ByとRollupを見比べて見てください。Rollupでは、GRP, ITEM, LOCで３つのカラムでの集計に加えて多くの集計行が追加されているのがわかります。
select grp, item, loc, sum(qty), count(*) from test_rollup
group by grp, item, loc order by 1,2,3;
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- ---------- ---------- ---------- ----------------
1 Apple USA 10 1
1 Banana India 20 1
1 Kiwi Aussie 5 1
2 Apple Japan 70 2
2 Kiwi Japan 100 1
select grp, item, loc, sum(qty), count(*) from test_rollup
group by rollup(grp, item, loc) order by 1,2,3;
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- --------------- --------------- ---------- ----------------
1 Apple USA 10 1
1 Apple 10 1 <-これ
1 Banana India 20 1
1 Banana 20 1 <-これ
1 Kiwi Aussie 5 1
1 Kiwi 5 1 <-これ
1 35 3 <-これ
2 Apple Japan 70 2
2 Apple 70 2 <-これ
2 Kiwi Japan 100 1
2 Kiwi 100 1 <-これ
2 170 3 <-これ
205 6 <-これ
実際、どんな集計が行われているのか分類してみます。
＜GRP & ITEM & LOC＞毎の集計
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- --------------- --------------- ---------- ----------------
1 Kiwi Aussie 5 1
1 Apple USA 10 1
1 Banana India 20 1
2 Kiwi Japan 100 1
2 Apple Japan 70 2
＜GRP & ITEM＞毎の集計
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- --------------- --------------- ---------- ----------------
1 Kiwi 5 1
1 Apple 10 1
1 Banana 20 1
2 Kiwi 100 1
2 Apple 70 2
＜GRP＞毎の集計
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- --------------- --------------- ---------- ----------------
1 35 3
2 170 3
＜総集計＞
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- --------------- --------------- ---------- ----------------
205 6
上記のグループ化カラムのうちいくつかの組み合わせの集計が行われいますが、すべて組み合わせにはなっていません。たとえば、GRP毎の集計はされましたが、ITEM毎の集計はありません。おなじくGRP + ITEM毎の集計はありますが、ITEM + LOC毎の集計はありません。
では集計するカラムの組み合わせはどの様に選ばれているのか、といえばこれは並べて見ると一目瞭然です。
| Rollupによる集計カラム |
|---|
| Rollup ( GRP, ITEM, LOC ) |
| <総計>、<GRP>、<GRP & ITEM>、<GRP & ITEM & LOC> |
つまりRollupは、与えられたカラムの左側から順に集計する複合カラムを作ります。具体的に言うと「Rollup( A, B, C )」であれば、「A」,「A ＆ B」,「A & B & C」です。上記の例では、「Rollup( grp, item, loc )」であったので、集計が「GRP」, 「GRP & ITEM」, 「GRP & ITEM & LOC」になりました。そして、それに加えて全体の集計である「総計」がかならず追加されます。
まとめると以下のようになります。記述したカラムリストの左側から集計対象カラムを増やしていることを確認してください。従って、カラムの順序で集計対象が変化することにも注意してください。
| 記述 | 集計対象 |
|---|---|
| Rollup ( A ) | 「総計」、「A集計」 |
| Rollup ( A, B ) | 「総計」、「A集計」、「A & B集計」 |
| Rollup ( B, A ) | 「総計」、「B集計」、「B & A集計」 |
| Rollup ( A, B, C ) | 「総計」、「A集計」、「A & B集計」、「A & B & C集計」 |
| Rollup ( B, C, A ) | 「総計」、「B集計」、「B & C集計」、「B & C & A集計」 |
実際に、「Rollup( grp, item )」と「Rollup( item, grp )」を比較してみます。集計対象が異なっているのが確認できますね。（「GRP & ITEM」集計と「ITEM & GRP」集計は同じものです）
select grp, item, sum(qty), count(*) from test_rollup
group by rollup(grp, item) order by 1,2;
GRP ITEM SUM(QTY) COUNT(*)
---------- ---------- ---------- ----------------
1 Apple 10 1 GRP & ITEM
1 Banana 20 1 GRP & ITEM
1 Kiwi 5 1 GRP & ITEM
1 35 3 GRP
2 Apple 70 2 GRP & ITEM
2 Kiwi 100 1 GRP & ITEM
2 170 3 GRP
205 6 総計
select grp, item, sum(qty), count(*) from test_rollup
group by rollup(item, grp) order by 1,2;
GRP ITEM SUM(QTY) COUNT(*)
---------- ---------- ---------- ----------------
1 Apple 10 1 ITEM & GRP
1 Banana 20 1 ITEM & GRP
1 Kiwi 5 1 ITEM & GRP
2 Apple 70 2 ITEM & GRP
2 Kiwi 100 1 ITEM & GRP
Apple 80 3 ITEM
Banana 20 1 ITEM
Kiwi 105 2 ITEM
205 6 総計
カッコ記述集計
では、Rollup内のカッコつきカラムリストをみてみます。「Group by Rollup( (A, B), C )」のような記述ですね。
Rollup内で複数のカラムをカッコで括ると、それぞれのカラムを集計対象として分離せず、一つの塊として扱います。たとえば、「Rollup ( (A, B), C )」であれば、AとBは分離されません。従って左側から、「A & B」、「A & B & C」の組み合わせ、プラス「総計」となります。ということは、マルチカラムで集計しつつ全体の総計のみを追加したい場合は、下記の３番目のように常にRollup内の全てのカラムをカッコで括ってしまえばよいということになります。
| 記述 | 集計対象 |
|---|---|
| Rollup ( (A, B), C ) | 「総計」、「A & B集計」、「A & B & C集計」 |
| Rollup ( A, (B, C) ) | 「総計」、「A集計」、 「A & B & C集計」 |
| Rollup ( (A, B, C) ) | 「総計」、 「A & B & C集計」 |
select grp, item, loc, sum(qty), count(*) from test_rollup
group by rollup((grp, item, loc)) order by 1,2,3;
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- ---------- ---------- ---------- ----------------
1 Apple USA 10 1 GRP & ITEM & LOC
1 Banana India 20 1 GRP & ITEM & LOC
1 Kiwi Aussie 5 1 GRP & ITEM & LOC
2 Apple Japan 70 2 GRP & ITEM & LOC
2 Kiwi Japan 100 1 GRP & ITEM & LOC
205 6 総計
複合Rollup
さらに複雑な複合Rollupをみてみます。「Group by Rollup( A, B ), Rollup( C )」のような記述ですね。最終的にどのカラムの組み合わせで集計が行われるかは展開計算で求められます。
Group by Rollup( A, B ), Rollup( C )
- Rollup( A, B ) -> 総計、A集計、A & B集計
- Rollup( C ) -> 総計、C集計
=（総計、A集計、A & B集計）x （総計、C集計）
- 総計 x 総計 -> 総計
- 総計 x C集計 -> C集計
- A集計 x 総計 -> A集計
- A集計 x C集計 -> A & C集計
- A & B集計 x 総計 -> A & B集計
- A & B集計 x C集計 -> A & B & C集計
＝ 「総計」、「A集計」、「C集計」、「A & B集計」、「A & C集計」、「A & B & C集計」
select grp, item, loc, sum(qty), count(*) from test_rollup
group by rollup(grp, item), rollup(loc) order by 1,2,3;
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- ---------- ---------- ---------- ----------------
1 Apple USA 10 1
1 Apple 10 1
1 Banana India 20 1
1 Banana 20 1
1 Kiwi Aussie 5 1
1 Kiwi 5 1
1 Aussie 5 1
1 India 20 1
1 USA 10 1
1 35 3
2 Apple Japan 70 2
2 Apple 70 2
2 Kiwi Japan 100 1
2 Kiwi 100 1
2 Japan 170 3
2 170 3
Aussie 5 1
India 20 1
Japan 170 3
USA 10 1
205 6
Rollupでない単体のカラムも同様に計算できます。ただし、単体カラムは展開する必要がないのでそのまま掛け算してください。計算結果を見て分かる通り、単体カラムをつかうと総計がなくなりますので、留意しておきましょう。
Group by Rollup( A, B ), C
＝ (総計、A集計、A & B集計) x C集計
＝「C集計」、「A & C集計」、「A & B & C集計」
Grouping Sets
以上を理解したところで、こんな感想がでてくるんじゃないでしょうか。
「いちいちそんな面倒な計算したくない。集計対象を直接指定する方法はないの？」
はい、あります。それが、Grouping Setsです。
Grouping Setsでは追加したい集計対象カラムの組み合わせを一つ一つ指定していきます。総計は()で表します。さきほどのRollupのいくつかをGrouping Setsで書き換えてみます。集計対象をそのまま置き換えていくだけなので特に難しくはないと思います。Rollupに比べ冗長にはなりますが、必要な集計を直接指定するため理解はしやすいのではないでしょうか。あとは好みの問題ですね。
Rollup ( A )
= 「総計」、「A集計」
＝ Grouping Sets ( (), A )Rollup ( A, B, C)
＝ 「総計」、「A集計」、「A & B集計」、「A & B & C集計」
＝ Grouping Sets ( (), A, (A, B), (A, B, C) )Rollup ( (A, B, C) )
= 「総計」、「A & B & C集計」
= Grouping Sets ( (), (A, B, C) )
また、複数Rollupと同様に、複数Grouping Setsも可能ですし、RollupとGrouping Setsの混合も全く問題ありません。複合指定の場合にどのカラムの組み合わせの集計が行われるかは、前述の展開計算で求められます。
- Rollup ( A ), Grouping Sets ( (), B ), C
= (総計, A集計) x (総計, B集計) x C集計
= (総計, B集計, A集計, A & B集計) x C集計
= 「C集計」、「 B & C集計」、「 A & C集計」、「 A & B & C集計」
select grp, item, loc, sum(qty), count(*) from test_rollup
group by Rollup(grp), Grouping Sets((), item), loc order by 1,2,3;
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- ---------- ---------- ---------- ----------------
1 Apple USA 10 1 GRP & ITEM & LOC
1 Banana India 20 1 GRP & ITEM & LOC
1 Kiwi Aussie 5 1 GRP & ITEM & LOC
1 Aussie 5 1 GRP & LOC
1 India 20 1 GRP & LOC
1 USA 10 1 GRP & LOC
2 Apple Japan 70 2 GRP & ITEM & LOC
2 Kiwi Japan 100 1 GRP & ITEM & LOC
2 Japan 170 3 GRP & LOC
Apple Japan 70 2 ITEM & LOC
Apple USA 10 1 ITEM & LOC
Banana India 20 1 ITEM & LOC
Kiwi Aussie 5 1 ITEM & LOC
Kiwi Japan 100 1 ITEM & LOC
Aussie 5 1 LOC
India 20 1 LOC
Japan 170 3 LOC
USA 10 1 LOC
CUBE
さてついでなので最後に、CUBEも説明しておきます。CUBEは、与えられたカラムの全ての組み合わせで集計を行います。例えば、３つのカラムだと以下の2^3=8通りの組み合わせですね（それぞれのカラムにおいて集計に「含む」「含まない」の二通りの選択があるため）。冗長にはなりますが以下のように同じことをGrouping SetsやRollupで記述することもできます。
CUBE ( A, B, C )
＝「総計」、「A 集計」、「B 集計」、「C 集計」、「A & B集計」、「A & C集計」、「B & C集計」、「A & B & C集計」
＝ Grouping Sets ( (), A, B, C, (A, B), (A, C), (B, C), (A, B, C) )
＝ Rollup ( A ), Rollup ( B ), Rollup ( C )
select grp, item, loc, sum(qty), count(*) from test_rollup
group by cube(grp, item, loc) order by 1,2,3;
GRP ITEM LOC SUM(QTY) COUNT(*)
---------- ---------- ---------- ---------- ----------------
1 Apple USA 10 1
1 Apple 10 1
1 Banana India 20 1
1 Banana 20 1
1 Kiwi Aussie 5 1
1 Kiwi 5 1
1 Aussie 5 1
1 India 20 1
1 USA 10 1
1 35 3
2 Apple Japan 70 2
2 Apple 70 2
2 Kiwi Japan 100 1
2 Kiwi 100 1
2 Japan 170 3
2 170 3
Apple Japan 70 2
Apple USA 10 1
Apple 80 3
Banana India 20 1
Banana 20 1
Kiwi Aussie 5 1
Kiwi Japan 100 1
Kiwi 105 2
Aussie 5 1
India 20 1
Japan 170 3
USA 10 1
205 6
もちろん、カッコも使えます。
CUBE ( ( A, B ), C )
＝「総計」、「A & B 集計」、「C 集計」、「A & B & C 集計」
＝ Grouping Sets ( (), (A, B), C, (A, B, C) )
総計のみの追加
CUBE ( ( A, B, C ) )
＝「総計」、「A & B & C 集計」
＝ Grouping Sets ( (), (A, B, C) )
＝ Rollup ( (A, B, C) )
さいごに
というわけで、Rollup、Grouping Sets、Cubeを一通り説明しましたが、どのカラムで集計が行われるか、すぐにわかるようになったのではないでしょうか。あとはGrouping関数やGrouping_ID関数を使って上手に清書すれば完璧です。
以上です。