MySQL8.0の機能を調べてまとめている。
タイトルどおりGROUP BY ... WITH ROLLUPについてドキュメントを読んだまとめ。
GROUP BYで指定したカラム自体の全体の集約(超集約: super-aggregate)結果を返すWITH ROLLUP
句で大きく2点の改良と機能追加があった.
ORDER BY
及びDISTINCT
をWITH ROLLUP
と同時に指定することができるようになった- GROUPING()の導入。超集約によって発生した
NULL
なのかもともとNULL
が入っていたものかを判別可能になった
WITH ROLLUPによるsuper-aggregate
そもそもWITH ROLLUPによるsuper-aggregateがどういったものか示す
8.0ドキュメントのサンプルと同じテーブル定義を利用し,データは独自で作成。
準備
CREATE TABLE sales ( year INT, country VARCHAR(20), product VARCHAR(32), profit INT ); INSERT INTO sales (year, country, product, profit) VALUES (2000, 'Finland', 'Computer', 1500), (2000, 'Finland', 'Computer', 1200), (2000, 'Finland', 'Phone', 100), (2000, 'Finland', 'Phone', 200), (2000, 'Finland', 'Phone', 300), (2000, 'USA', 'Calculator', 75), (2000, 'USA', 'Calculator', 75), (2000, 'USA', 'Computer' ,1500), (2000, 'USA', 'Computer' ,200), (2001, 'Finland', 'Phone', 10), (2001, 'Finland', 'Phone', 20), (2001, 'USA', 'Calculator', 50), (2001, 'USA', 'Calculator', 20), (2001, 'USA', 'Computer', 2700) ;
WITH ROLLUPをつけることで以下のようにgroup byに指定したカラム自身も集約対象となり、集約されたカラムの行はNULLが挿入される
mysql> select year, country, product, sum(profit) from sales group by year, country, product with rollup; +------+---------+------------+-------------+ | year | country | product | sum(profit) | +------+---------+------------+-------------+ | 2000 | Finland | Computer | 2700 | | 2000 | Finland | Phone | 600 | | 2000 | Finland | NULL | 3300 | | 2000 | USA | Calculator | 150 | | 2000 | USA | Computer | 1700 | | 2000 | USA | NULL | 1850 | | 2000 | NULL | NULL | 5150 | | 2001 | Finland | Phone | 30 | | 2001 | Finland | NULL | 30 | | 2001 | USA | Calculator | 70 | | 2001 | USA | Computer | 2700 | | 2001 | USA | NULL | 2770 | | 2001 | NULL | NULL | 2800 | | NULL | NULL | NULL | 7950 | +------+---------+------------+-------------+ 14 rows in set (0.00 sec)
このNULLを挿入する作業は、クエリを処理する後半のフェーズでクライアントに送信する前に起きるため、where句やJOINの条件指定でIS NOT NULL
などで除外することはできないらしい(要検証)。
例:
mysql> select year, country, product, sum(profit) from sales where year IS NOT NULL group by year, country, product with rollup; +------+---------+------------+-------------+ | year | country | product | sum(profit) | +------+---------+------------+-------------+ | 2000 | Finland | Computer | 2700 | | 2000 | Finland | Phone | 600 | | 2000 | Finland | NULL | 3300 | | 2000 | USA | Calculator | 150 | | 2000 | USA | Computer | 1700 | | 2000 | USA | NULL | 1850 | | 2000 | NULL | NULL | 5150 | | 2001 | Finland | Phone | 30 | | 2001 | Finland | NULL | 30 | | 2001 | USA | Calculator | 70 | | 2001 | USA | Computer | 2700 | | 2001 | USA | NULL | 2770 | | 2001 | NULL | NULL | 2800 | | NULL | NULL | NULL | 7950 | +------+---------+------------+-------------+ 14 rows in set (0.00 sec)
DISTINCT, ORDER BY
5.7ではWITH ROLLUP
と同時にDISTINCT
やORDER BY
を指定することはできなかった。
この制約はMySQL 8.0.12で解除された。(Bug #87450, Bug #86311, Bug #26640100, Bug #26073513
)
例1: (このクエリにSyntaxが許容されるかを試す以上の意味はない)
mysql> select distinct(year) from sales group by year, country, product with rollup; +------+ | year | +------+ | 2000 | | 2001 | | NULL | +------+ 3 rows in set (0.00 sec)
例2: (このクエリにSyntaxが許容されるかを試す以上の意味はない)
mysql> select year, country, product, sum(profit) from sales group by year, country, product with rollup order by country; +------+---------+------------+-------------+ | year | country | product | sum(profit) | +------+---------+------------+-------------+ | 2000 | NULL | NULL | 5150 | | 2001 | NULL | NULL | 2800 | | NULL | NULL | NULL | 7950 | | 2000 | Finland | Computer | 2700 | | 2000 | Finland | Phone | 600 | | 2000 | Finland | NULL | 3300 | | 2001 | Finland | Phone | 30 | | 2001 | Finland | NULL | 30 | | 2000 | USA | Calculator | 150 | | 2000 | USA | Computer | 1700 | | 2000 | USA | NULL | 1850 | | 2001 | USA | Calculator | 70 | | 2001 | USA | Computer | 2700 | | 2001 | USA | NULL | 2770 | +------+---------+------------+-------------+ 14 rows in set (0.00 sec)
GROUPING()
5.7ではsuper-aggregateされて発生したNULLとそもそもNULLが含まれていた結果を区別することができなかった。
例えば準備
のところで用意したsalesテーブルに以下の行を入れる。(NULLがこんなところにあって良いのかという話はさておき)
準備2
INSERT INTO sales (year, country, product, profit) VALUES (2000, 'Finland', NULL, 150), (2001, 'USA', NULL, 120) ;
するとGROUP BY ... WITH ROLLUP
の結果は以下のようになる。
mysql> select year, country, product, sum(profit) from sales group by year, country, product with rollup; +------+---------+------------+-------------+ | year | country | product | sum(profit) | +------+---------+------------+-------------+ | 2000 | Finland | NULL | 150 | <-- 1 | 2000 | Finland | Computer | 2700 | | 2000 | Finland | Phone | 600 | | 2000 | Finland | NULL | 3450 | <-- 1 | 2000 | USA | Calculator | 150 | | 2000 | USA | Computer | 1700 | | 2000 | USA | NULL | 1850 | | 2000 | NULL | NULL | 5300 | | 2001 | Finland | Phone | 30 | | 2001 | Finland | NULL | 30 | | 2001 | USA | NULL | 120 | <-- 2 | 2001 | USA | Calculator | 70 | | 2001 | USA | Computer | 2700 | | 2001 | USA | NULL | 2890 | <-- 2 | 2001 | NULL | NULL | 2920 | | NULL | NULL | NULL | 8220 | +------+---------+------------+-------------+ 16 rows in set (0.00 sec)
これでは<--
とつけた部分のどちらがsuper-aggregateによるものか判断しづらい
これを判断できるのがgrouping()である。
grouping()にカラムを指定することで、そのカラムのレコードがsuper-aggregateによって発生したNULLかどうかを0/1で返してくれる。
例
mysql> select year, country, product, sum(profit), grouping(year), grouping(country), grouping(product) from sales group by year, country, product with rollup; +------+---------+------------+-------------+----------------+-------------------+-------------------+ | year | country | product | sum(profit) | grouping(year) | grouping(country) | grouping(product) | +------+---------+------------+-------------+----------------+-------------------+-------------------+ | 2000 | Finland | NULL | 150 | 0 | 0 | 0 | | 2000 | Finland | Computer | 2700 | 0 | 0 | 0 | | 2000 | Finland | Phone | 600 | 0 | 0 | 0 | | 2000 | Finland | NULL | 3450 | 0 | 0 | 1 | | 2000 | USA | Calculator | 150 | 0 | 0 | 0 | | 2000 | USA | Computer | 1700 | 0 | 0 | 0 | | 2000 | USA | NULL | 1850 | 0 | 0 | 1 | | 2000 | NULL | NULL | 5300 | 0 | 1 | 1 | | 2001 | Finland | Phone | 30 | 0 | 0 | 0 | | 2001 | Finland | NULL | 30 | 0 | 0 | 1 | | 2001 | USA | NULL | 120 | 0 | 0 | 0 | | 2001 | USA | Calculator | 70 | 0 | 0 | 0 | | 2001 | USA | Computer | 2700 | 0 | 0 | 0 | | 2001 | USA | NULL | 2890 | 0 | 0 | 1 | | 2001 | NULL | NULL | 2920 | 0 | 1 | 1 | | NULL | NULL | NULL | 8220 | 1 | 1 | 1 | +------+---------+------------+-------------+----------------+-------------------+-------------------+ 16 rows in set (0.00 sec)
さらに工夫すれば、
mysql> select -> if(grouping(year), 'All year', year), -> if(grouping(country), 'All country', country), -> if(grouping(product), 'All product', product), -> sum(profit) -> from sales group by year, country, product with rollup; +--------------------------------------+-----------------------------------------------+-----------------------------------------------+-------------+ | if(grouping(year), 'All year', year) | if(grouping(country), 'All country', country) | if(grouping(product), 'All product', product) | sum(profit) | +--------------------------------------+-----------------------------------------------+-----------------------------------------------+-------------+ | 2000 | Finland | NULL | 150 | | 2000 | Finland | Computer | 2700 | | 2000 | Finland | Phone | 600 | | 2000 | Finland | All product | 3450 | | 2000 | USA | Calculator | 150 | | 2000 | USA | Computer | 1700 | | 2000 | USA | All product | 1850 | | 2000 | All country | All product | 5300 | | 2001 | Finland | Phone | 30 | | 2001 | Finland | All product | 30 | | 2001 | USA | NULL | 120 | | 2001 | USA | Calculator | 70 | | 2001 | USA | Computer | 2700 | | 2001 | USA | All product | 2890 | | 2001 | All country | All product | 2920 | | All year | All country | All product | 8220 | +--------------------------------------+-----------------------------------------------+-----------------------------------------------+-------------+
このような結果を取得することもできる(yearのカラムは型が統一できてないけど、、、)