Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
6ヶ月前投稿 修正あり

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

MySQL 8.0.1から追加されたGROUPING関数に関するMySQL Server Blogの記事を紹介します。

原文
MySQL 8.0: GROUPING function | MySQL Server Blog (English)
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
B5aa4f809000b9147289650532e83932 taka-h
翻訳レビュアー
D98ee74ffe0fafbdc83b23907dda3665 doublemarket C17de91fbad737faaa06173533ed323d kakuka4430
原著者への翻訳報告
未報告


免責事項

この記事は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 BYROLLUPを利用する例をご紹介します。

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句を使い倒してみて、フィードバックをください。

次の記事
よりよいGitの設定
前の記事
mysqlpumpユーティリティー

Feed small 記事フィード