SQL: GROUP BY, ROLLUP, CUBEの実行例

# サンプルデータ

```sql
SELECT *
FROM sample_data
ORDER BY key1, key2, key3, key4
```

| KEY1 | KEY2 | KEY3 | KEY4 | VAL |
|------|------|------|------|----:|
| AAAA | AAA  | AA   | A    | 100 |
| AAAA | AAA  | AA   | B    | 110 |
| AAAA | AAA  | BB   | A    | 120 |
| AAAA | AAA  | BB   | B    | 130 |
| AAAA | BBB  | AA   | A    | 140 |
| AAAA | BBB  | AA   | B    | 150 |
| AAAA | BBB  | BB   | A    | 160 |
| AAAA | BBB  | BB   | B    | 170 |
| BBBB | AAA  | AA   | A    | 180 |
| BBBB | AAA  | AA   | B    | 190 |
| BBBB | AAA  | BB   | A    | 200 |
| BBBB | AAA  | BB   | B    | 210 |
| BBBB | BBB  | AA   | A    | 220 |
| BBBB | BBB  | AA   | B    | 230 |
| BBBB | BBB  | BB   | A    | 240 |
| BBBB | BBB  | BB   | B    | 250 |

```sql
/* データ型に特に意味はない */
CREATE TABLE sample_data
(
  key1 CHAR(4),
  key2 CHAR(3),
  key3 CHAR(2),
  key4 CHAR(1),
  val INTEGER
);
```

```sql
INSERT INTO sample_data VALUES('AAAA','AAA','AA','A',100);
INSERT INTO sample_data VALUES('AAAA','AAA','AA','B',110);
INSERT INTO sample_data VALUES('AAAA','AAA','BB','A',120);
INSERT INTO sample_data VALUES('AAAA','AAA','BB','B',130);
INSERT INTO sample_data VALUES('AAAA','BBB','AA','A',140);
INSERT INTO sample_data VALUES('AAAA','BBB','AA','B',150);
INSERT INTO sample_data VALUES('AAAA','BBB','BB','A',160);
INSERT INTO sample_data VALUES('AAAA','BBB','BB','B',170);
INSERT INTO sample_data VALUES('BBBB','AAA','AA','A',180);
INSERT INTO sample_data VALUES('BBBB','AAA','AA','B',190);
INSERT INTO sample_data VALUES('BBBB','AAA','BB','A',200);
INSERT INTO sample_data VALUES('BBBB','AAA','BB','B',210);
INSERT INTO sample_data VALUES('BBBB','BBB','AA','A',220);
INSERT INTO sample_data VALUES('BBBB','BBB','AA','B',230);
INSERT INTO sample_data VALUES('BBBB','BBB','BB','A',240);
INSERT INTO sample_data VALUES('BBBB','BBB','BB','B',250);
```

---

# KEY1, KEY2 で集計する

```sql
SELECT key1, key2, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, key2
ORDER BY key1, key2
```

| KEY1 | KEY2 | COUNT(*) | SUM(VAL) |
|------|------|---------:|---------:|
| AAAA | AAA  |        4 |      460 |
| AAAA | BBB  |        4 |      620 |
| BBBB | AAA  |        4 |      780 |
| BBBB | BBB  |        4 |      940 |

---

# KEY1, KEY2 で集計しつつ KEY2 の小計をとる

小計を取りたい列を `ROLLUP()` 演算子に指定する。

```sql
SELECT key1, key2, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2)
ORDER BY key1, key2
```

KEY2がNULLの行が、KEY2の小計行である。

| KEY1 | KEY2 | COUNT(*) | SUM(VAL) |
|------|------|---------:|---------:|
| AAAA | AAA  |        4 |      460 |
| AAAA | BBB  |        4 |      620 |
| AAAA |      |        8 |     1080 |
| BBBB | AAA  |        4 |      780 |
| BBBB | BBB  |        4 |      940 |
| BBBB |      |        8 |     1720 |

---

# 複数列を組み合わせた小計（１）

KEY1ごとのKEY2, KEY3の小計をとる。KEY2, KEY3の小計については更にKEY2ごとのKEY3の小計をとる。

```sql
SELECT key1, key2, key3, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2, key3)
ORDER BY key1, key2, key3
```

| KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------|------|------|---------:|---------:|
| AAAA | AAA  | AA   |        2 |      210 |
| AAAA | AAA  | BB   |        2 |      250 |
| AAAA | AAA  |      |        4 |      460 |
| AAAA | BBB  | AA   |        2 |      290 |
| AAAA | BBB  | BB   |        2 |      330 |
| AAAA | BBB  |      |        4 |      620 |
| AAAA |      |      |        8 |     1080 |
| BBBB | AAA  | AA   |        2 |      370 |
| BBBB | AAA  | BB   |        2 |      410 |
| BBBB | AAA  |      |        4 |      780 |
| BBBB | BBB  | AA   |        2 |      450 |
| BBBB | BBB  | BB   |        2 |      490 |
| BBBB | BBB  |      |        4 |      940 |
| BBBB |      |      |        8 |     1720 |

ここで ROLLUP() に指定する順序には意味がある。

KEY2とKEY3を入れ替えると以下の通り。

```sql
SELECT key1, key2, key3, COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key3, key2)
ORDER BY key1, key3, key2
```

| KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------|------|------|---------:|---------:|
| AAAA | AAA  | AA   |        2 |      210 |
| AAAA | BBB  | AA   |        2 |      290 |
| AAAA |      | AA   |        4 |      500 |
| AAAA | AAA  | BB   |        2 |      250 |
| AAAA | BBB  | BB   |        2 |      330 |
| AAAA |      | BB   |        4 |      580 |
| AAAA |      |      |        8 |     1080 |
| BBBB | AAA  | AA   |        2 |      370 |
| BBBB | BBB  | AA   |        2 |      450 |
| BBBB |      | AA   |        4 |      820 |
| BBBB | AAA  | BB   |        2 |      410 |
| BBBB | BBB  | BB   |        2 |      490 |
| BBBB |      | BB   |        4 |      900 |
| BBBB |      |      |        8 |     1720 |

---

# 複数列を組み合わせた小計（２）

KEY1, KEY2ごとに、KEY3, KEY4の小計をとる。

```sql
SELECT key1, key2, key3, key4,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, key2, ROLLUP( (key3, key4) )
ORDER BY key1, key2, key3, key4
```

| KEY1 | KEY2 | KEY3 | KEY4 | COUNT(*) | SUM(VAL) |
|------|------|------|------|---------:|---------:|
| AAAA | AAA  | AA   | A    |        1 |      100 |
| AAAA | AAA  | AA   | B    |        1 |      110 |
| AAAA | AAA  | BB   | A    |        1 |      120 |
| AAAA | AAA  | BB   | B    |        1 |      130 |
| AAAA | AAA  |      |      |        4 |      460 |
| AAAA | BBB  | AA   | A    |        1 |      140 |
| AAAA | BBB  | AA   | B    |        1 |      150 |
| AAAA | BBB  | BB   | A    |        1 |      160 |
| AAAA | BBB  | BB   | B    |        1 |      170 |
| AAAA | BBB  |      |      |        4 |      620 |
| BBBB | AAA  | AA   | A    |        1 |      180 |
| BBBB | AAA  | AA   | B    |        1 |      190 |
| BBBB | AAA  | BB   | A    |        1 |      200 |
| BBBB | AAA  | BB   | B    |        1 |      210 |
| BBBB | AAA  |      |      |        4 |      780 |
| BBBB | BBB  | AA   | A    |        1 |      220 |
| BBBB | BBB  | AA   | B    |        1 |      230 |
| BBBB | BBB  | BB   | A    |        1 |      240 |
| BBBB | BBB  | BB   | B    |        1 |      250 |
| BBBB | BBB  |      |      |        4 |      940 |

---

# GROUPING, GROUPING_ID

カラムにNULLが格納されている場合、ROLLUPされた「超集合行」を判別するには `GROUPING` または `GROUPING_ID` 関数を使用する。

|関数|仕様|
|---|---|
|GROUPING(列)|1: 指定した列の超集合行 / 0: それ以外|
|GROUPING_ID(列1, 列2, ...)|上記の組み合わせ(2進数に見立てる)|

```sql
SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, ROLLUP(key2, key3)
ORDER BY key1, key2, key3
```

（ただし今回のサンプルデータにはNULLが含まれない。）

| GRP_1 | GRP_2 | GRP_3 | GRPID_1X2X3 | KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------:|------:|------:|------------:|------|------|------|---------:|---------:|
|     0 |     0 |     0 |           0 | AAAA | AAA  | AA   |        2 |      210 |
|     0 |     0 |     0 |           0 | AAAA | AAA  | BB   |        2 |      250 |
|     0 |     0 |     1 |           1 | AAAA | AAA  |      |        4 |      460 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | AA   |        2 |      290 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | BB   |        2 |      330 |
|     0 |     0 |     1 |           1 | AAAA | BBB  |      |        4 |      620 |
|     0 |     1 |     1 |           3 | AAAA |      |      |        8 |     1080 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | AA   |        2 |      370 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | BB   |        2 |      410 |
|     0 |     0 |     1 |           1 | BBBB | AAA  |      |        4 |      780 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | AA   |        2 |      450 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | BB   |        2 |      490 |
|     0 |     0 |     1 |           1 | BBBB | BBB  |      |        4 |      940 |
|     0 |     1 |     1 |           3 | BBBB |      |      |        8 |     1720 |

---

# クロス集計

ROLLUPと異なり、値の組み合わせで小計が計算される。

## (1) CUBE(KEY1, KEY2, KEY3)

以下の組み合わせをとる。

(KEY1), (KEY2), (KEY3), (KEY1, KEY2), (KEY1, KEY3), (KEY2, KEY3), (KEY1, KEY2, KEY3)

```sql
SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY CUBE(key1, key2, key3)
ORDER BY key1, key2, key3
```

| GRP_1 | GRP_2 | GRP_3 | GRPID_1X2X3 | KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------:|------:|------:|------------:|------|------|------|---------:|---------:|
|     0 |     0 |     0 |           0 | AAAA | AAA  | AA   |        2 |      210 |
|     0 |     0 |     0 |           0 | AAAA | AAA  | BB   |        2 |      250 |
|     0 |     0 |     1 |           1 | AAAA | AAA  |      |        4 |      460 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | AA   |        2 |      290 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | BB   |        2 |      330 |
|     0 |     0 |     1 |           1 | AAAA | BBB  |      |        4 |      620 |
|     0 |     1 |     0 |           2 | AAAA |      | AA   |        4 |      500 |
|     0 |     1 |     0 |           2 | AAAA |      | BB   |        4 |      580 |
|     0 |     1 |     1 |           3 | AAAA |      |      |        8 |     1080 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | AA   |        2 |      370 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | BB   |        2 |      410 |
|     0 |     0 |     1 |           1 | BBBB | AAA  |      |        4 |      780 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | AA   |        2 |      450 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | BB   |        2 |      490 |
|     0 |     0 |     1 |           1 | BBBB | BBB  |      |        4 |      940 |
|     0 |     1 |     0 |           2 | BBBB |      | AA   |        4 |      820 |
|     0 |     1 |     0 |           2 | BBBB |      | BB   |        4 |      900 |
|     0 |     1 |     1 |           3 | BBBB |      |      |        8 |     1720 |
|     1 |     0 |     0 |           4 |      | AAA  | AA   |        4 |      580 |
|     1 |     0 |     0 |           4 |      | AAA  | BB   |        4 |      660 |
|     1 |     0 |     1 |           5 |      | AAA  |      |        8 |     1240 |
|     1 |     0 |     0 |           4 |      | BBB  | AA   |        4 |      740 |
|     1 |     0 |     0 |           4 |      | BBB  | BB   |        4 |      820 |
|     1 |     0 |     1 |           5 |      | BBB  |      |        8 |     1560 |
|     1 |     1 |     0 |           6 |      |      | AA   |        8 |     1320 |
|     1 |     1 |     0 |           6 |      |      | BB   |        8 |     1480 |
|     1 |     1 |     1 |           7 |      |      |      |       16 |     2800 |

---

## (2) KEY1, CUBE(KEY2, KEY3)

```sql
SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, CUBE(key2, key3)
ORDER BY key1, key2, key3
```

| GRP_1 | GRP_2 | GRP_3 | GRPID_1X2X3 | KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------:|------:|------:|------------:|------|------|------|---------:|---------:|
|     0 |     0 |     0 |           0 | AAAA | AAA  | AA   |        2 |      210 |
|     0 |     0 |     0 |           0 | AAAA | AAA  | BB   |        2 |      250 |
|     0 |     0 |     1 |           1 | AAAA | AAA  |      |        4 |      460 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | AA   |        2 |      290 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | BB   |        2 |      330 |
|     0 |     0 |     1 |           1 | AAAA | BBB  |      |        4 |      620 |
|     0 |     1 |     0 |           2 | AAAA |      | AA   |        4 |      500 |
|     0 |     1 |     0 |           2 | AAAA |      | BB   |        4 |      580 |
|     0 |     1 |     1 |           3 | AAAA |      |      |        8 |     1080 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | AA   |        2 |      370 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | BB   |        2 |      410 |
|     0 |     0 |     1 |           1 | BBBB | AAA  |      |        4 |      780 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | AA   |        2 |      450 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | BB   |        2 |      490 |
|     0 |     0 |     1 |           1 | BBBB | BBB  |      |        4 |      940 |
|     0 |     1 |     0 |           2 | BBBB |      | AA   |        4 |      820 |
|     0 |     1 |     0 |           2 | BBBB |      | BB   |        4 |      900 |
|     0 |     1 |     1 |           3 | BBBB |      |      |        8 |     1720 |

---

## (3)  CUBE(KEY1,(KEY2, KEY3))

```sql
SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY CUBE(key1, (key2, key3))
ORDER BY key1, key2, key3
```

| GRP_1 | GRP_2 | GRP_3 | GRPID_1X2X3 | KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------:|------:|------:|------------:|------|------|------|---------:|---------:|
|     0 |     0 |     0 |           0 | AAAA | AAA  | AA   |        2 |      210 |
|     0 |     0 |     0 |           0 | AAAA | AAA  | BB   |        2 |      250 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | AA   |        2 |      290 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | BB   |        2 |      330 |
|     0 |     1 |     1 |           3 | AAAA |      |      |        8 |     1080 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | AA   |        2 |      370 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | BB   |        2 |      410 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | AA   |        2 |      450 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | BB   |        2 |      490 |
|     0 |     1 |     1 |           3 | BBBB |      |      |        8 |     1720 |
|     1 |     0 |     0 |           4 |      | AAA  | AA   |        4 |      580 |
|     1 |     0 |     0 |           4 |      | AAA  | BB   |        4 |      660 |
|     1 |     0 |     0 |           4 |      | BBB  | AA   |        4 |      740 |
|     1 |     0 |     0 |           4 |      | BBB  | BB   |        4 |      820 |
|     1 |     1 |     1 |           7 |      |      |      |       16 |     2800 |

## (4)  KEY1, CUBE((KEY2, KEY3))

```sql
SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_2,
       GROUPING(key3)                AS grp_3,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY key1, CUBE((key2, key3))
ORDER BY key1, key2, key3
```

| GRP_1 | GRP_2 | GRP_3 | GRPID_1X2X3 | KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------:|------:|------:|------------:|------|------|------|---------:|---------:|
|     0 |     0 |     0 |           0 | AAAA | AAA  | AA   |        2 |      210 |
|     0 |     0 |     0 |           0 | AAAA | AAA  | BB   |        2 |      250 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | AA   |        2 |      290 |
|     0 |     0 |     0 |           0 | AAAA | BBB  | BB   |        2 |      330 |
|     0 |     1 |     1 |           3 | AAAA |      |      |        8 |     1080 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | AA   |        2 |      370 |
|     0 |     0 |     0 |           0 | BBBB | AAA  | BB   |        2 |      410 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | AA   |        2 |      450 |
|     0 |     0 |     0 |           0 | BBBB | BBB  | BB   |        2 |      490 |
|     0 |     1 |     1 |           3 | BBBB |      |      |        8 |     1720 |

---

# GROUPING SETS

小計をとる列の組み合わせを直接指定する。

## key2, (key1, key3), 全体

```sql
SELECT GROUPING(key1)                AS grp_1,
       GROUPING(key2)                AS grp_3,
       GROUPING(key3)                AS grp_4,
       GROUPING_ID(key1, key2, key3) AS grpid_1x2x3,
       key1, key2, key3,
       COUNT(*), SUM(val)
FROM sample_data
GROUP BY GROUPING SETS(key2, (key1, key3), ())
ORDER BY key1, key2, key3
```

| GRP_1 | GRP_3 | GRP_4 | GRPID_1X2X3 | KEY1 | KEY2 | KEY3 | COUNT(*) | SUM(VAL) |
|------:|------:|------:|------------:|------|------|------|---------:|---------:|
|     0 |     1 |     0 |           2 | AAAA |      | AA   |        4 |      500 |
|     0 |     1 |     0 |           2 | AAAA |      | BB   |        4 |      580 |
|     0 |     1 |     0 |           2 | BBBB |      | AA   |        4 |      820 |
|     0 |     1 |     0 |           2 | BBBB |      | BB   |        4 |      900 |
|     1 |     0 |     1 |           5 |      | AAA  |      |        8 |     1240 |
|     1 |     0 |     1 |           5 |      | BBB  |      |        8 |     1560 |
|     1 |     1 |     1 |           7 |      |      |      |       16 |     2800 |

---

# 参考URL

- [図でイメージするOracle DatabaseのSQL全集 (第5回 RollUp集計など)](http://www.oracle.com/technetwork/jp/articles/otnj-sql-image5-444376-ja.html)

- [Markdown Tables Generator](http://www.tablesgenerator.com/markdown_tables)