MySQL - GROUP BY .. WITH ROLLUPの勉強

2025-04-04
MySQL
training

適当に勉強してるログです。GROUP BY WITH ROLLUP っていう構文知らなかったので試す。

雑にデータベース起動。

1
docker run --rm -p 13306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql --secure-file-priv=''

データベース作成して、テーブル作成して、テストデータ投入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE
DATABASE IF NOT EXISTS sales_db;
USE sales_db;

CREATE TABLE sales
(
    id      INT AUTO_INCREMENT PRIMARY KEY,
    region  VARCHAR(50), -- 地域
    product VARCHAR(50), -- 商品
    amount  INT          -- 売上金額
);

INSERT INTO sales (region, product, amount)
VALUES ('East', 'Apple', 100),
       ('East', 'Apple', 150),
       ('East', 'Banana', 80),
       ('West', 'Apple', 120),
       ('West', 'Banana', 90),
       ('West', 'Banana', 60),
       ('North', 'Apple', 200),
       ('North', 'Banana', 50);

1. 地域ごとの合計を表示

1
2
3
SELECT region, SUM(amount) AS total_amount
FROM sales
GROUP BY region WITH ROLLUP;

なるほど、合計がでてる。 region が NULL ってのが若干気になるけど便利。

1
2
3
4
5
6
7
8
9
+--------+--------------+
| region | total_amount |
+--------+--------------+
| East   |          330 |
| North  |          250 |
| West   |          270 |
| NULL   |          850 |
+--------+--------------+
4 rows in set (0.00 sec)

2. 地域・商品ごとの合計＋小計・総計を表示

1
2
3
4
SELECT region, product, SUM(amount) AS total_amount
FROM sales
GROUP BY region, product
WITH ROLLUP;

NULL〜〜〜ってのは置いといて、小計と総計もでる。しかもクエリ一発。なるほど

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+--------+---------+--------------+
| region | product | total_amount |
+--------+---------+--------------+
| East   | Apple   |          250 |
| East   | Banana  |           80 |
| East   | NULL    |          330 |
| North  | Apple   |          200 |
| North  | Banana  |           50 |
| North  | NULL    |          250 |
| West   | Apple   |          120 |
| West   | Banana  |          150 |
| West   | NULL    |          270 |
| NULL   | NULL    |          850 |
+--------+---------+--------------+
10 rows in set (0.00 sec)

結果の見方のヒント

NULLが特別な意味を持つようになるが、クエリの数は減ってる。この辺り、現場によって判断が分かれそう。

  • product が NULL → その region の小計
  • region と product が NULL → 全体の総計

NULLを見やすくする工夫

こんな感じでNULLを処理してあげると見やすくなる。

1
2
3
4
5
6
SELECT
  IFNULL(region, '合計') AS region_label,
  IFNULL(product, '小計') AS product_label,
  SUM(amount) AS total_amount
FROM sales
GROUP BY region, product WITH ROLLUP;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+--------------+---------------+--------------+
| region_label | product_label | total_amount |
+--------------+---------------+--------------+
| East         | Apple         |          250 |
| East         | Banana        |           80 |
| East         | 小計          |          330 |
| North        | Apple         |          200 |
| North        | Banana        |           50 |
| North        | 小計          |          250 |
| West         | Apple         |          120 |
| West         | Banana        |          150 |
| West         | 小計          |          270 |
| 合計         | 小計          |          850 |
+--------------+---------------+--------------+
10 rows in set (0.00 sec)

まとめ

一回のクエリで総計がでるのは便利。とはいえ、NULLに特別な意味をもたせるので、判断が発生する余地はあるので若干気になる。きっと、帳票出力とかCSV出力とかBIツールのデータ出力だとマジで役立つのだろうと思った。

MySQL8で、これが使えるようするためにGRUOP BY周りは書き直しが発生していたらしく、GROUP BY周りの互換性のない変更なんかは、こういうのの影響らしい

https://dev.mysql.com/doc/refman/8.0/ja/upgrading-from-previous-series.html#upgrade-sql-changes

@soudai1025 が教えてくれた。あの人、MySQL詳しいな…