tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

MySQL 8.0 GROUP BY関連の新機能

MySQL8.0の機能を調べてまとめている。
タイトルどおりGROUP BY ... WITH ROLLUPについてドキュメントを読んだまとめ。

GROUP BYで指定したカラム自体の全体の集約(超集約: super-aggregate)結果を返すWITH ROLLUP句で大きく2点の改良と機能追加があった.

  1. ORDER BY及びDISTINCTWITH ROLLUPと同時に指定することができるようになった
  2. 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と同時にDISTINCTORDER 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のカラムは型が統一できてないけど、、、)

reference