MySQL データのグループ化（集計処理）について(GROUP BY句、WITH ROLLUP)

2024/02/06

MySQL だけでは無く、他のデータベースでもデータをあるキーに従ってグループ化を行って、「合計値」「平均値」「計数値」の集計処理を行うことがあります。

あるデータをその中のどれかのカラムを指定して GROUP BY の後ろにそれを指定します。データの「合計値」「平均値」「計数値」はそれぞれ SUM、AVG、COUNT の関数を使います。

グループ化は以下の様な構文で指定します。

SELECT カラム名1 [, カラム名2, ...] ,[集計関数...]  FROM テーブル名...
GROUP BY カラム名1 [, カラム名2, ...]

GROUP BY の後ろに指定されたカラム名の値が同じデータのグループ化を行います。複数のカラム名が指定された場合は、それぞれのカラム名の値が同じものでグループ化を行います。

今回テストで用いるテーブルは、以下の記事のものを少し変更しています。
⇒MySQL マスタテーブルの作成と参照に関する考察について(CREATE TABLE、AUTOINCREMENT、JOIN、LEFT JOIN)

以下の内容で、順を追って説明します。

テストテーブル（商品マスタ、売上データ）の作成について
GROUP BY 指定のカラムが1個の場合ついて
GROUP BY 指定のカラムが複数の場合ついて
グループ化した結果をさらに集計する方法ついて(WITH ROLLUP)

■テストテーブル（商品マスタ、売上データ）の作成について

テストデータとして「商品情報」と「売上データ」のテーブルを使い、テーブルの連結を行いながら、グループ化について説明したいと思います。
「商品情報」（商品マスタと言い換えます）は以下の様な項目が必要だと思います。

商品コード（商品の型番？）
商品分類
商品名
単価

「売上データ」は以下の様な項目が必要だと思います。ここでは、誰が買ったかなどの情報は割愛して考えますので、商品と売上のみの情報の項目のみとしています。（後、消費税についても割愛します。）

売上NO
商品コード
日付
数量
単価（単価の変更があるため）
金額

上記の項目でテーブル作成SQLを考えますが、商品マスタの場合は「商品コード」を主キーにしてもいいのですが、 AUTO_INCREMENT な「id」を追加してこちらを主キーとします。
「商品コード」とマスタの識別子となる「id」を分けた方が、後々なにかと便利になるといいますか、 MySQL の作法的に私は「id」を付加しています。（テーブルカラムの指定で AUTO_INCREMENT があるので気軽に連番のデータが付加できるとも言えますが...）

なお、「商品マスタ」「売上データ」ともに削除フラグ（デフォルトで「0」設定）を持たせました。それぞれのデータの削除処理の時にフラグを「1」に更新する様に考えています。こうすれば廃品種となったマスタのデータを残しておくことができます。

作成の SQL は以下の様になります。（「OR REPLACE」の指定は置き換えを行います）

-- 商品マスタ
CREATE OR REPLACE TABLE tm_prod (
	id    INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    code  VARCHAR(32),	-- 商品コード
    class VARCHAR(32),	-- 商品分類
    name  VARCHAR(32),	-- 商品名
    price INT,          -- 単価
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

-- 売上データ
CREATE OR REPLACE TABLE tt_sales (
    no        INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 売上NO
    id_prd    INT,      -- 商品マスタ.id
    salesdate DATE,	    -- 日付
    volume    INT,      -- 数量
    price     INT,      -- 単価
    amount    INT,      -- 金額
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

それでは、上記のテーブルに以下の SQL でテストデータを登録します。

-- 商品テーブル
INSERT INTO tm_prod(code, class, name, price) VALUES
    ('pen01', 'pen', '鉛筆HB', 80), 
    ('pen02', 'pen', '鉛筆B' , 100),
    ('pen03', 'pen', '鉛筆B2' , 100),
    ('cre01', 'cre', 'くれよん12色', 1000),
    ('cre02', 'cre', 'くれよん24色', 2000),
    ('pen10', 'kpen','蛍光ペン', 120),
    ('pen11', 'kpen','蛍光ペン2', 130),
    ('era01', 'era', '消しゴム', 150),
    ('era02', 'era', '消しゴム2', 200);
-- 売上件数テーブル
INSERT INTO tt_sales(id_prd, salesdate, volume, price, amount) VALUES
    (1, '2024-01-04', 10,   80,  800), 
    (4, '2024-01-04',  1, 1000, 1000), 
    (6, '2024-01-04',  2,  120,  240), 
    (7, '2024-01-05',  3,  130,  390), 
    (1, '2024-01-07', 15,   80, 1200), 
    (2, '2024-01-07', 10,  100, 1000), 
    (5, '2024-01-07',  2, 2000, 4000), 
    (1, '2024-01-05', 20,   80, 1600), 
    (3, '2024-01-05',  1,  100,  100), 
    (4, '2024-01-10',  2, 1000, 2000), 
    (2, '2024-01-15',  2,  100,  200), 
    (6, '2024-01-05',  5,  120,  600), 
    (7, '2024-01-14',  3,  130,  390), 
    (8, '2024-01-20',  1,  150,  150), 
    (5, '2024-01-20',  4, 2000, 8000), 
    (8, '2024-01-22', 10,  150, 1500), 
    (9, '2024-01-22', 10,  200, 2000);

テストデータを確認します。

MariaDB [test]> select * from tm_prod;
+----+-------+-------+--------------+-------+------+
| id | code  | class | name         | price | del  |
+----+-------+-------+--------------+-------+------+
|  1 | pen01 | pen   | 鉛筆HB       |    80 |    0 |
|  2 | pen02 | pen   | 鉛筆B        |   100 |    0 |
|  3 | pen03 | pen   | 鉛筆B2       |   100 |    0 |
|  4 | cre01 | cre   | くれよん12色 |  1000 |    0 |
|  5 | cre02 | cre   | くれよん24色 |  2000 |    0 |
|  6 | pen10 | kpen  | 蛍光ペン     |   120 |    0 |
|  7 | pen11 | kpen  | 蛍光ペン2    |   130 |    0 |
|  8 | era01 | era   | 消しゴム     |   150 |    0 |
|  9 | era02 | era   | 消しゴム2    |   200 |    0 |
+----+-------+-------+--------------+-------+------+
9 rows in set (0.00 sec)

MariaDB [test]> select * from tt_sales;
+----+--------+------------+--------+-------+--------+------+
| no | id_prd | salesdate  | volume | price | amount | del  |
+----+--------+------------+--------+-------+--------+------+
|  1 |      1 | 2024-01-04 |     10 |    80 |    800 |    0 |
|  2 |      4 | 2024-01-04 |      1 |  1000 |   1000 |    0 |
|  3 |      6 | 2024-01-04 |      2 |   120 |    240 |    0 |
|  4 |      7 | 2024-01-05 |      3 |   130 |    390 |    0 |
|  5 |      1 | 2024-01-07 |     15 |    80 |   1200 |    0 |
|  6 |      2 | 2024-01-07 |     10 |   100 |   1000 |    0 |
|  7 |      5 | 2024-01-07 |      2 |  2000 |   4000 |    0 |
|  8 |      1 | 2024-01-05 |     20 |    80 |   1600 |    0 |
|  9 |      3 | 2024-01-05 |      1 |   100 |    100 |    0 |
| 10 |      4 | 2024-01-10 |      2 |  1000 |   2000 |    0 |
| 11 |      2 | 2024-01-15 |      2 |   100 |    200 |    0 |
| 12 |      6 | 2024-01-05 |      5 |   120 |    600 |    0 |
| 13 |      7 | 2024-01-14 |      3 |   130 |    390 |    0 |
| 14 |      8 | 2024-01-20 |      1 |   150 |    150 |    0 |
| 15 |      5 | 2024-01-20 |      4 |  2000 |   8000 |    0 |
| 16 |      8 | 2024-01-22 |     10 |   150 |   1500 |    0 |
| 17 |      9 | 2024-01-22 |     10 |   200 |   2000 |    0 |
+----+--------+------------+--------+-------+--------+------+
17 rows in set (0.00 sec)

■GROUP BY 指定のカラムが1個の場合ついて

それでは売上データに対してグループ化を行う為に GROUP BY に1個のカラムとして「商品分類」を指定し、「商品分類」ごとの商品の売上数と売上金額の合計数を表示する SQL を示します。
「商品分類」は「商品マスタ」に持っているため、「売上データ」と「商品マスタ」を FROM句 で INNER JOIN で結合します。
結合によりどちらかのテーブルにあるカラムを1個のテーブルにあるカラムの様に考えて指定ができます。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY tm_prod.class
ORDER BY tm_prod.class;

この SQL の結果は以下の通りです。

+----------+------+-------+
| 商品分類 | 数量 | 金額  |
+----------+------+-------+
| cre      |    9 | 15000 |
| era      |   21 |  3650 |
| kpen     |   13 |  1620 |
| pen      |   58 |  4900 |
+----------+------+-------+
4 rows in set (0.00 sec)

「売上データ」には「商品分類」の4種類のデータが在ることが分かります。

■GROUP BY 指定のカラムが複数の場合ついて

グループ化の指定が複数の場合の例を示します。「商品マスタ」は「商品分類」ごとに分類分けして登録されていますので、「商品分類」「商品コード」の2つを GROUP BY のカラムに指定します。

先ほどの1カラム指定のSQLに追加を行います。

SELECT 
    tm_prod.class AS '商品分類',
    tm_prod.code  AS '商品コード',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY 
    tm_prod.class, 
    tm_prod.code 
ORDER BY
    tm_prod.class, 
    tm_prod.code;

SQL の実行結果は以下の通りです。

+----------+------------+------+-------+
| 商品分類 | 商品コード | 数量 | 金額  |
+----------+------------+------+-------+
| cre      | cre01      |    3 |  3000 |
| cre      | cre02      |    6 | 12000 |
| era      | era01      |   11 |  1650 |
| era      | era02      |   10 |  2000 |
| kpen     | pen10      |    7 |   840 |
| kpen     | pen11      |    6 |   780 |
| pen      | pen01      |   45 |  3600 |
| pen      | pen02      |   12 |  1200 |
| pen      | pen03      |    1 |   100 |
+----------+------------+------+-------+
9 rows in set (0.00 sec)

■グループ化した結果をさらに集計する方法ついて(WITH ROLLUP)

GROUP BY の指定であるカラムに対する集計処理は行えますが、その集計結果をさらに集計する場合には GROUP BY の最後に WITH ROLLUP の指定を行います。

上記の「■GROUP BY 指定のカラムが1個の場合ついて」での SQL について WITH ROLLUP の指定を行います。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY tm_prod.class WITH ROLLUP
ORDER BY tm_prod.class;

これを実行すると以下の様なエラーが表示されました。

MariaDB [test]> SELECT
    ->     tm_prod.class AS '商品分類',
    ->     SUM(tt_sales.volume) AS '数量',
    ->     SUM(tt_sales.amount) AS '金額'
    -> FROM tt_sales
    ->     INNER JOIN tm_prod
    ->     ON  tm_prod.id = tt_sales.id_prd
    ->     AND tm_prod.del = 0
    -> GROUP BY tm_prod.class WITH ROLLUP
    -> ORDER BY tm_prod.class WITH ROLLUP;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY
MariaDB [test]>

WITH ROLLUP の指定と ORDER BY の指定は同時にはできない様です。
ORDER BY の指定を省いて再度実行します。

MariaDB [test]> SELECT
    ->     tm_prod.class AS '商品分類',
    ->     SUM(tt_sales.volume) AS '数量',
    ->     SUM(tt_sales.amount) AS '金額'
    -> FROM tt_sales
    ->     INNER JOIN tm_prod
    ->     ON  tm_prod.id = tt_sales.id_prd
    ->     AND tm_prod.del = 0
    -> GROUP BY tm_prod.class WITH ROLLUP;
+----------+------+-------+
| 商品分類 | 数量 | 金額  |
+----------+------+-------+
| cre      |    9 | 15000 |
| era      |   21 |  3650 |
| kpen     |   13 |  1620 |
| pen      |   58 |  4900 |
| NULL     |  101 | 25170 |
+----------+------+-------+
5 rows in set (0.00 sec)

結果リストの最終行に「商品分類」が「NULL」の行が表示され、「数量」「金額」の合計値が表示されます。

さらに「■GROUP BY 指定のカラムが複数の場合ついて」での SQL について WITH ROLLUP の指定を行います。

SELECT 
    tm_prod.class AS '商品分類',
    tm_prod.code  AS '商品コード',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY 
    tm_prod.class, 
    tm_prod.code 
WITH ROLLUP;

このSQLの実行結果は以下の様になります。

+----------+------------+------+-------+
| 商品分類 | 商品コード | 数量 | 金額  |
+----------+------------+------+-------+
| cre      | cre01      |    3 |  3000 |
| cre      | cre02      |    6 | 12000 |
| cre      | NULL       |    9 | 15000 |
| era      | era01      |   11 |  1650 |
| era      | era02      |   10 |  2000 |
| era      | NULL       |   21 |  3650 |
| kpen     | pen10      |    7 |   840 |
| kpen     | pen11      |    6 |   780 |
| kpen     | NULL       |   13 |  1620 |
| pen      | pen01      |   45 |  3600 |
| pen      | pen02      |   12 |  1200 |
| pen      | pen03      |    1 |   100 |
| pen      | NULL       |   58 |  4900 |
| NULL     | NULL       |  101 | 25170 |
+----------+------------+------+-------+
14 rows in set (0.00 sec)

各「商品分類」ごとの集計値が「商品コード」が「NULL」の行に表示され、最終行に全ての合計が「商品分類」「商品コード」が共に「NULL」の行に表示されます。

MySQL
0CM

コメントを書く