MySQL の GROUP BY とROLLUPを理解したい

会社の研修で初めてちゃんとMySQLを触ったのですが、いまいち自分が理解できない部分として ROLLUP があったので備忘録として調べた結果を記しておこうと思います。
誰かの役に立てれば幸いです。

## GROUP BY とは(おさらい)
`GROUP BY` は**指定したカラムの値を基準にグループ化をする**ことができるものです。
百聞は一見にしかずということで、例を示していきたいと思います。
下記のような`sales`テーブルがあるとします。
| year | country | product    | profit |
| -- | --- | --- | --- | 
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland |  Phone     |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |

シンプルに全てを表示させると下記のようになります。
```sql
mysql> SELECT * FROM sales;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland |  Phone     |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+
9 rows in set (0.00 sec)
```

例えば、countryごとのprofitの平均を求めたいとします。
`GROUP BY` を使用すると下記のように求めることができます。
```sql
mysql> SELECT country, AVG(profit) FROM sales GROUP BY country;
+---------+-------------+
| country | AVG(profit) |
+---------+-------------+
| Finland |    536.6667 |
| India   |    675.0000 |
| USA     |   1125.0000 |
+---------+-------------+
3 rows in set (0.02 sec)
```

`GROUP BY`に複数のカラムを指定した場合は、組み合わせごとにグループ化します。
```sql
mysql> SELECT year, country, AVG(profit) FROM sales GROUP BY year, country;
+------+---------+-------------+
| year | country | AVG(profit) |
+------+---------+-------------+
| 2000 | Finland |    800.0000 |
| 2000 | India   |    675.0000 |
| 2000 | USA     |   1500.0000 |
| 2001 | Finland |     10.0000 |
| 2001 | USA     |   1000.0000 |
+------+---------+-------------+
5 rows in set (0.01 sec)
```
## GROUP BY と WITH ROLLUP を組み合わせる
`GROUP BY` を使用すると更に`WITH ROLLUP`修飾子を使用することができます。
`WIH ROLLUP`を使用することで、**全ての値を対象とした結果を示す別の行が追加**されます。
先ほどのGROUP BYの使用例に`WITH ROLLUP`を使用すると、下記のような結果が出力されます。
```sql
mysql> SELECT country, AVG(profit) FROM sales GROUP BY country WITH ROLLUP;
+---------+-------------+
| country | AVG(profit) |
+---------+-------------+
| Finland |    536.6667 |
| India   |    675.0000 |
| USA     |   1125.0000 |
| NULL    |    828.8889 |
+---------+-------------+
4 rows in set (0.02 sec)
```
countryカラムの値がNULLとなっている行が、`WITH ROLLUP`にて集計され、追加された行にあたります。
この行のAVG(profit)は、全てのレコードのAVERAGEになっています。

複数のカラムを指定した場合はこんな感じです。
```sql
mysql> SELECT year, country, AVG(profit) FROM sales GROUP BY year, country WITH ROLLUP;
+------+---------+-------------+
| year | country | AVG(profit) |
+------+---------+-------------+
| 2000 | Finland |    800.0000 |
| 2000 | India   |    675.0000 |
| 2000 | USA     |   1500.0000 |
| 2000 | NULL    |    890.0000 |<- 1
| 2001 | Finland |     10.0000 |
| 2001 | USA     |   1000.0000 |
| 2001 | NULL    |    752.5000 |<- 1
| NULL | NULL    |    828.8889 |<- 2
+------+---------+-------------+
8 rows in set (0.01 sec)
```
2つのカラムを指定した場合では、新たに2つの種類の行が追加されます。
1. 特定の年における、全ての国のprofitの平均
2. 全ての年、国におけるprofitの平均

## WITH ROLLUP にて追加された行の見分け方
さて、`WITH ROLLUP`にて追加された行の値はNULLとなりますが、元のtableのレコードにNULLが存在していた場合には一見、「どれが`WITH ROLLUP`にて追加されたものなのか？」ということがわかりません。

これを判別するためには、`GROUPING()`を使用します。
`GROUPING()`は`WITH ROLLUP`に追加されたものであれば1を、それ以外であれば0を返します。
使用例は下記のような感じです。
```sql
mysql> SELECT 
          year, country, AVG(profit), 
          GROUPING(year) AS group_year,
          GROUPING(country) AS group_country 
       FROM sales 
       GROUP BY year, country
       WITH ROLLUP;
+------+---------+-------------+------------+---------------+
| year | country | AVG(profit) | group_year | group_country |
+------+---------+-------------+------------+---------------+
| 2000 | Finland |    800.0000 |          0 |             0 |
| 2000 | India   |    675.0000 |          0 |             0 |
| 2000 | USA     |   1500.0000 |          0 |             0 |
| 2000 | NULL    |    890.0000 |          0 |             1 |
| 2001 | Finland |     10.0000 |          0 |             0 |
| 2001 | USA     |   1000.0000 |          0 |             0 |
| 2001 | NULL    |    752.5000 |          0 |             1 |
| NULL | NULL    |    828.8889 |          1 |             1 |
+------+---------+-------------+------------+---------------+
8 rows in set (0.01 sec)
```
## WITH ROLLUP を使用する際の注意事項
`WITH ROLLUP`を使用する際の注意事項としていくつか列挙しておきます。
### NULLの生成タイミング
`WITH ROLLUP`により生成されるNULLは、**クライアントに行が渡されるタイミングで生成されます。**
よって`WHERE`を用いてNULLを検出することはできません。(そもそもGROUP BYの結果に対してもできませんが...)
`WITH ROLLUP`により生成されたNULLを検出するためには、`SELECT`や`HAVING`を使用する必要があります。

### LIMIT の使用
`LIMIT`は`WITH ROLLUP`により行が追加された後に適用されます。
よって、`LIMIT`を使用することによって、「追加された行が表示されない」ということも起こり得ます。


### ORDER BY との併用
MySQL 8.0.12 より以前のバージョンでは、`WITH ROLLUP`と`ORDER BY`は併用できません。
ただ、ソートをする手段がないという訳ではありません。
例えばサブクエリを用いることでソートを行うことができます。
```sql
mysql> SELECT * FROM 
          (
             SELECT country, AVG(profit) FROM sales
             GROUP BY country WITH ROLLUP
          ) AS db
       ORDER BY country;
+---------+-------------+
| country | AVG(profit) |
+---------+-------------+
| NULL    |    828.8889 |
| Finland |    536.6667 |
| India   |    675.0000 |
| USA     |   1125.0000 |
+---------+-------------+
4 rows in set (0.01 sec)
```
ただ、MySQL 8.0.12では使用することができます。

## 参考資料
- [データをグループ化する(GROUP BY句)](https://www.dbonline.jp/mysql/select/index9.html#section1)
- [MySQL の GROUP BY 完全に理解した](https://qiita.com/Go-Noji/items/5baeb790ade4b57126ff)
- [MySQL_Document_12.20:Aggregate_Functions](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions-and-modifiers.html)
- [MySQLのWindow関数を完全に理解する](https://qiita.com/ngyuki/items/c35dd314815939ffbeb6)