会社の研修で初めてちゃんと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 |
シンプルに全てを表示させると下記のようになります。
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 を使用すると下記のように求めることができます。
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に複数のカラムを指定した場合は、組み合わせごとにグループ化します。
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を使用すると、下記のような結果が出力されます。
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になっています。
複数のカラムを指定した場合はこんな感じです。
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つの種類の行が追加されます。
- 特定の年における、全ての国のprofitの平均
- 全ての年、国におけるprofitの平均
WITH ROLLUP にて追加された行の見分け方
さて、WITH ROLLUPにて追加された行の値はNULLとなりますが、元のtableのレコードにNULLが存在していた場合には一見、「どれがWITH ROLLUPにて追加されたものなのか？」ということがわかりません。
これを判別するためには、GROUPING()を使用します。
GROUPING()はWITH ROLLUPに追加されたものであれば1を、それ以外であれば0を返します。
使用例は下記のような感じです。
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は併用できません。
ただ、ソートをする手段がないという訳ではありません。
例えばサブクエリを用いることでソートを行うことができます。
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では使用することができます。