- MySQL
-
2024-06-02
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
![](https://k-sugi.sakura.ne.jp/wp-content/themes/chic_grid3/images/icon-tag.png)