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

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

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

MySQL
2024-06-02

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

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

1mysql> create table t1 (a integer, b integer, c integer);
2insert into t1 values (111,11,11),(222,22,22),(111,12,12),(222,23,23);
3 
4mysql> SELECT * FROM t1;
5+------+------+------+
6| a    | b    | c    |
7+------+------+------+
8|  111 |   11 |   11 |
9|  222 |   22 |   22 |
10|  111 |   12 |   12 |
11|  222 |   23 |   23 |
12+------+------+------+
134 rows in set (0.000 sec)

WITH ROLLUP

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

1mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b;
2+------+------+------+
3| a    | b    | SUM  |
4+------+------+------+
5|  111 |   11 |   11 |
6|  111 |   12 |   12 |
7|  222 |   22 |   22 |
8|  222 |   23 |   23 |
9+------+------+------+
104 rows in set (0.001 sec)

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

1mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP;
2+------+------+------+
3| a    | b    | SUM  |
4+------+------+------+
5|  111 |   11 |   11 |
6|  111 |   12 |   12 |
7|  111 | NULL |   23 |
8|  222 |   22 |   22 |
9|  222 |   23 |   23 |
10|  222 | NULL |   45 |
11| NULL | NULL |   68 |
12+------+------+------+
137 rows in set (0.000 sec)

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

1mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a ASC, b DESC WITH ROLLUP;
2+------+------+------+
3| a    | b    | SUM  |
4+------+------+------+
5|  111 |   12 |   12 |
6|  111 |   11 |   11 |
7|  111 | NULL |   23 |
8|  222 |   23 |   23 |
9|  222 |   22 |   22 |
10|  222 | NULL |   45 |
11| NULL | NULL |   68 |
12+------+------+------+
137 rows in set (0.000 sec)

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

1mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY a, b;
2+------+------+------+
3| a    | b    | SUM  |
4+------+------+------+
5| NULL | NULL |   68 |
6|  111 | NULL |   23 |
7|  111 |   11 |   11 |
8|  111 |   12 |   12 |
9|  222 | NULL |   45 |
10|  222 |   22 |   22 |
11|  222 |   23 |   23 |
12+------+------+------+
137 rows in set (0.00 sec)

1mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY a, b DESC;
2+------+------+------+
3| a    | b    | SUM  |
4+------+------+------+
5| NULL | NULL |   68 |
6|  111 |   12 |   12 |
7|  111 |   11 |   11 |
8|  111 | NULL |   23 |
9|  222 |   23 |   23 |
10|  222 |   22 |   22 |
11|  222 | NULL |   45 |
12+------+------+------+
137 rows in set (0.01 sec)

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

GROUPING

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

1mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY GROUPING(a), a, GROUPING(b), b;
2+------+------+------+
3| a    | b    | SUM  |
4+------+------+------+
5|  111 |   11 |   11 |
6|  111 |   12 |   12 |
7|  111 | NULL |   23 |
8|  222 |   22 |   22 |
9|  222 |   23 |   23 |
10|  222 | NULL |   45 |
11| NULL | NULL |   68 |
12+------+------+------+
137 rows in set (0.00 sec)

GROUPING

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

1mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a), GROUPING(b) FROM t1 GROUP BY a, b WITH ROLLUP;
2+------+------+------+-------------+-------------+
3| a    | b    | SUM  | GROUPING(a) | GROUPING(b) |
4+------+------+------+-------------+-------------+
5|  111 |   11 |   11 |           0 |           0 |
6|  111 |   12 |   12 |           0 |           0 |
7|  111 | NULL |   23 |           0 |           1 |
8|  222 |   22 |   22 |           0 |           0 |
9|  222 |   23 |   23 |           0 |           0 |
10|  222 | NULL |   45 |           0 |           1 |
11| NULL | NULL |   68 |           1 |           1 |
12+------+------+------+-------------+-------------+
137 rows in set (0.00 sec)

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

1mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a, b) FROM t1 GROUP BY a, b WITH ROLLUP;
2+------+------+------+----------------+
3| a    | b    | SUM  | GROUPING(a, b) |
4+------+------+------+----------------+
5|  111 |   11 |   11 |              0 |
6|  111 |   12 |   12 |              0 |
7|  111 | NULL |   23 |              1 |
8|  222 |   22 |   22 |              0 |
9|  222 |   23 |   23 |              0 |
10|  222 | NULL |   45 |              1 |
11| NULL | NULL |   68 |              3 |
12+------+------+------+----------------+
137 rows in set (0.00 sec)

集計行だけを表示する

1mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP HAVING GROUPING(a) = 1 or GROUPING(b) = 1;
2+------+------+------+
3| a    | b    | SUM  |
4+------+------+------+
5|  111 | NULL |   23 |
6|  222 | NULL |   45 |
7| NULL | NULL |   68 |
8+------+------+------+
93 rows in set (0.00 sec)

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

1mysql> 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;
2+-----------------+---------------+------+
3| Department      | Employees     | SUM  |
4+-----------------+---------------+------+
5| 111             | 11            |   11 |
6| 111             | 12            |   12 |
7| 111             | All Employees |   23 |
8| 222             | 22            |   22 |
9| 222             | 23            |   23 |
10| 222             | All Employees |   45 |
11| All Departments | All Employees |   68 |
12+-----------------+---------------+------+
137 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

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

MySQL

316 views

≪ XAMPPのshell 文字コードを変更 XAMPPでPHPのモジュール版・CGI版切り替え ≫