MySQL 8.0のGROUPING関数(MySQL Server Blogより)

免責事項

この記事はChaithra Gopalareddy氏によるMySQL Server Blogの投稿「MySQL 8.0: GROUPING function」(2017/5/2)をユーザが翻訳したものであり、Oracle公式の文書ではありません。

MySQL 8.0.1からサーバーがSQLのGROUPING関数をサポートするようになりました。GROUP関数は、(ROLLUP操作によって生成される)超集約行(super-aggregate row)のすべての値を表すNULLであるか、通常行のNULLかを区別して利用できます。

はじめに

MySQLサーバーはGROUP BY拡張のROLLUPをしばらくサポートしてきました。ここにGROUP BYでROLLUPを利用する例をご紹介します。

mysql> create table t1 (a integer, b integer, c integer);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values (111,11,11);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (222,22,22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (111,12,12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (222,23,23);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
+------+------+------+
4 rows in set (0.00 sec)

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP; 
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+------+------+------+
7 rows in set (0.00 sec)

上記の結果でお分かりいただけるように、ROLLUPによってそれぞれの超集約行に対してNULLが追加されています。

ここで、テーブルのデータにNULLを追加してみましょう。

mysql> INSERT INTO t1 values (1111,NULL,112); 
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 values (NULL,112,NULL); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
| 1111 | NULL |  112 |
| NULL |  112 | NULL |
+------+------+------+
6 rows in set (0.00 sec)

テーブルにNULLのデータを追加したあとにROLLUPを付けてクエリーを発行すると、次の結果となります。

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
| NULL |  112 | NULL |
| NULL | NULL | NULL |
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| 1111 | NULL |  112 |
| 1111 | NULL |  112 |
| NULL | NULL |  180 |
+------+------+------+
11 rows in set (0.01 sec)

この例でお分かりいただけるように、NULLが通常の集約値を表しているのか、あるいは超集約値を表しているのかを区別するのが困難です。

MySQL 8.0.1で何が変わったか

GROUPING関数により、上記のROLLUPによって生成されるNULLと集約されたデータから生成されるNULLかを区別できるようになります。列に対するGROUPING関数はROLLUP操作の結果として生成されたNULLであれば1を返し、そうでなければ0を返します。

mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a), GROUPING(b) FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+-------------+-------------+
| a    | b    | SUM  | GROUPING(a) | GROUPING(b) |
+------+------+------+-------------+-------------+
| NULL |  112 | NULL |           0 |           0 |
| NULL | NULL | NULL |           0 |           1 |
|  111 |   11 |   11 |           0 |           0 |
|  111 |   12 |   12 |           0 |           0 |
|  111 | NULL |   23 |           0 |           1 |
|  222 |   22 |   22 |           0 |           0 |
|  222 |   23 |   23 |           0 |           0 |
|  222 | NULL |   45 |           0 |           1 |
| 1111 | NULL |  112 |           0 |           0 |
| 1111 | NULL |  112 |           0 |           1 |
| NULL | NULL |  180 |           1 |           1 |
+------+------+------+-------------+-------------+
11 rows in set (0.01 sec)

上記の例で分かるように、GROUPING(b)はROLLUP操作によりNULLが生成されている行に対してだけ1を返しています。

GROUPING関数を利用する他の方法として、単一のGROUPING関数に複数の列を引数として渡す場合があります。GROUPING関数の結果はGROUPING(引数)が1である引数に対する整数値のビットマスクとなります。

例:

mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a,b) FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+---------------+
| a    | b    | SUM  | GROUPING(a,b) |
+------+------+------+---------------+
| NULL |  112 | NULL |             0 |
| NULL | NULL | NULL |             1 |
|  111 |   11 |   11 |             0 |
|  111 |   12 |   12 |             0 |
|  111 | NULL |   23 |             1 |
|  222 |   22 |   22 |             0 |
|  222 |   23 |   23 |             0 |
|  222 | NULL |   45 |             1 |
| 1111 | NULL |  112 |             0 |
| 1111 | NULL |  112 |             1 |
| NULL | NULL |  180 |             3 |
+------+------+------+---------------+
11 rows in set (0.00 sec)

上記でわかるように、GROUPING (a,b)は3であり、a列、b列にROLLUP操作で生成されたNULLがあることを示しています。結果が1であればb列だけがROLLUP操作の結果生成されたNULL値であることになります。

GROUPING関数の他の利用方法

GROUPINGは選択リストの中か、HAVING句中で利用できます。HAVING句で利用する場合、次のように超集約行か集約行のみを扱うことができます。

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP HAVING GROUPING(a) = 1 or GROUPING(b) = 1;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
| NULL | NULL | NULL |
|  111 | NULL |   23 |
|  222 | NULL |   45 |
| 1111 | NULL |  112 |
| NULL | NULL |  180 |
+------+------+------+
5 rows in set (0.00 sec)

次の例は、GROUPING関数を超集約行と集約行をうまく区別するように利用しています。

mysql> SELECT IF(GROUPING(a)=1,'All Departments', a) as Department, IF(GROUPING(b)=1, 'All Employees', b) as Employees, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+-----------------+---------------+------+
| Department      | Employees     | SUM  |
+-----------------+---------------+------+
| NULL            | 112           | NULL |
| NULL            | All Employees | NULL |
| 111             | 11            |   11 |
| 111             | 12            |   12 |
| 111             | All Employees |   23 |
| 222             | 22            |   22 |
| 222             | 23            |   23 |
| 222             | All Employees |   45 |
| 1111            | NULL          |  112 |
| 1111            | All Employees |  112 |
| All Departments | All Employees |  180 |
+-----------------+---------------+------+
11 rows in set (0.00 sec)

MySQLサーバーで新規に追加されたGROUPING関数の利用例をいくつかご紹介しました。

まとめ

この機能を追加するにあたっての基本的な箇所に関する、Zhe Dong氏のコントリビューションに感謝します。GROUPING句を使い倒してみて、フィードバックをください。