Rollupちゃんと理解してる？

##はじめに

SQLには、単純なGroup byによる集計計算に加え、Rollup, Cube, Grouping Setsなどの指定カラムに対して追加集計計算を行う便利な機能があります。特にRollupは小計や総計を取得するのに便利で身近な存在ですが、動きをしっかり把握していないと集計対象が複雑になった場合にピンポイントで必要な集計を得るのが難しくなります。

例えば、以下の例では一つのカラムだけを対象にRollupを使用して総計を取得していますが、Group Byの対象が複数カラムになった場合に総計だけを取得するにはどう記述すればよいでしょうか？ また、4つの複合カラムで集計する場合に総計と特定のひとつのカラムの小計だけ取りたい場合はどうでしょう？

```sql:
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 )**|
                   

###テストデータ

```sql:
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で３つのカラムでの集計に加えて多くの集計行が追加されているのがわかります。

```sql:単純Group-By
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
```

```sql:複合カラムRollup
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」集計は同じものです）

```sql:
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		総計
```

```sql:
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集計」   |

```sql:カッコつきカラムリストの例
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集計」**


```sql:複合Rollup例
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集計」**

```sql:
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 )**

```sql:
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関数を使って上手に清書すれば完璧です。

以上です。