カテゴリー
SugiBlog Webエンジニアのためのお役立ちTips

MySQL GROUP BYのWITH ROLLUP修飾子とGROUPING関数

MySQLでGROUP BYを使って集計するときにWITH ROLLUPを使えば出力に集計行を追加することができます。

適当なサンプルデータを用意します。

mysql> create table t1 (a integer, b integer, c integer);
insert into t1 values (111,11,11),(222,22,22),(111,12,12),(222,23,23);

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

WITH ROLLUP

まず単純にグループ化してみましょう。

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
+------+------+------+
4 rows in set (0.001 sec)

WITH ROLLUPを付けると出力結果に集計行が追加されます。

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.000 sec)

MySQL5.7以前のバージョンでは、GROUP BYは常にソートされます。
並び順を指定すると次のようになります。
このとき集計行に影響はありません。

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

MySQL8.0ではGROUP BYでのソートは削除されています。
その代わりORDER BYが使用できます。

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

上記のようにORDER BYが効いています。
しかし、集計行にも影響が出ていますね。

GROUPING

MySQL8.0以降で5.7と同じ並び順で表示するにはORDER BYでGROUPINGを使います。

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY GROUPING(a), a, GROUPING(b), b;
+------+------+------+
| 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)
GROUPING

GROUPING関数はMySQL8.0.1で追加された新機能です。

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) |
+------+------+------+-------------+-------------+
|  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 |
| NULL | NULL |   68 |           1 |           1 |
+------+------+------+-------------+-------------+
7 rows in set (0.00 sec)

GROUPING関数は引数に渡したカラムが集計対象カラムであれば1を、そうでなければ0を返します。
複数カラムを渡した場合はそれぞれの集計では1、総計は3になります。

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) |
+------+------+------+----------------+
|  111 |   11 |   11 |              0 |
|  111 |   12 |   12 |              0 |
|  111 | NULL |   23 |              1 |
|  222 |   22 |   22 |              0 |
|  222 |   23 |   23 |              0 |
|  222 | NULL |   45 |              1 |
| NULL | NULL |   68 |              3 |
+------+------+------+----------------+
7 rows in set (0.00 sec)

集計行だけを表示する

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  |
+------+------+------+
|  111 | NULL |   23 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+------+------+------+
3 rows in set (0.00 sec)

集計行の区別を分かりやすくする

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  |
+-----------------+---------------+------+
| 111             | 11            |   11 |
| 111             | 12            |   12 |
| 111             | All Employees |   23 |
| 222             | 22            |   22 |
| 222             | 23            |   23 |
| 222             | All Employees |   45 |
| All Departments | All Employees |   68 |
+-----------------+---------------+------+
7 rows in set (0.00 sec)

参考URL:
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.20.2 GROUP BY 修飾子
MySQL :: MySQL 8.0 Reference Manual :: 14.23 Miscellaneous Functions
MySQL :: Improvements to ROLLUP in MySQL
MySQL :: MySQL 8.0: GROUPING function

この記事がお役に立ちましたらシェアお願いします
93 views

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です