アプリケーションで作成したデータをGROUP BYを用いて集計する、という機会はよくあるかと思います。そのようなときにROLLUPを使うことで、小計や合計を簡単に集計することが可能です。この機能は以前からありましたが、バージョン8.
今回はROLLUPを使ったクエリの確認と、8.
テストデータの準備
今回は例として、的当てゲームのようなものを想定して色とスコアの結果を保存するデータを作成するために、以下のようなテーブルとデータを利用します。
mysql> CREATE TABLE scores (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(256) NOT NULL, color varchar(30) NOT NULL, score INT NOT NULL); mysql> INSERT INTO scores VALUES (1, 'AAA', 'RED', 10), (2, 'BBB', 'BLUE', 30), (3, 'CCC', 'RED', 12), (4, 'AAA', 'BLUE', 20), (5, 'BBB', 'RED', 15),(6, 'CCC', 'BLUE', 25),(7, 'AAA', 'YELLOW', 50), (8, 'BBB', 'BLUE', 30), (9, 'CCC', 'RED', 40);
ROLLUPを使った集計
では、さっそく作成したデータを集計していきましょう。各nameごとのスコアを表示させるには、GROUP BYを使って下記のようなクエリを実行することで、合計、最大のスコア、平均等を取得することができます。
mysql> SELECT name, SUM(score), max(score), avg(score) FROM scores GROUP BY name; +------+------------+------------+------------+ | name | SUM(score) | max(score) | avg(score) | +------+------------+------------+------------+ | AAA | 80 | 50 | 26.6667 | | BBB | 75 | 30 | 25.0000 | | CCC | 77 | 40 | 25.6667 | +------+------------+------------+------------+ 3 rows in set (0.00 sec)
この出力結果にROLLUPを使って合計を出力してみましょう。ROLLUPを利用するには、GROUP BYのあとにWITH ROLLUPを追加することで出力することができます。実行するとnameにNULLの行が現れ、3人の合計、3人の最大スコア、3人の平均スコアを出力されることが確認できました。このようにWITH ROLLUPを追加するだけで、全体に対しての集計結果を取得することができるようになります。
mysql> SELECT name, SUM(score), max(score), avg(score) FROM scores GROUP BY name WITH ROLLUP; +------+------------+------------+------------+ | name | SUM(score) | max(score) | avg(score) | +------+------------+------------+------------+ | AAA | 80 | 50 | 26.6667 | | BBB | 75 | 30 | 25.0000 | | CCC | 77 | 40 | 25.6667 | | NULL | 232 | 50 | 25.7778 | +------+------------+------------+------------+ 4 rows in set (0.01 sec)
複数のカラムにGROUP BYした場合
GROUP BYで指定するカラムが複数ある場合は、ROLLUPでどのような結果が出力されるのか？ と疑問に思った人もいるでしょう。例としてnameとcolorカラムをGROUP BYで指定して、count(color)の集計関数も追加しみます。
mysql> SELECT name, color, SUM(score), max(score), avg(score), count(color) FROM scores GROUP BY name, color; +------+--------+------------+------------+------------+--------------+ | name | color | SUM(score) | max(score) | avg(score) | count(color) | +------+--------+------------+------------+------------+--------------+ | AAA | RED | 10 | 10 | 10.0000 | 1 | | BBB | BLUE | 60 | 30 | 30.0000 | 2 | | CCC | RED | 52 | 40 | 26.0000 | 2 | | AAA | BLUE | 20 | 20 | 20.0000 | 1 | | BBB | RED | 15 | 15 | 15.0000 | 1 | | CCC | BLUE | 25 | 25 | 25.0000 | 1 | | AAA | YELLOW | 50 | 50 | 50.0000 | 1 | +------+--------+------------+------------+------------+--------------+ 7 rows in set (0.00 sec) mysql> SELECT name, color, SUM(score), max(score), avg(score), count(color) FROM scores GROUP BY name, color WITH ROLLUP; +------+--------+------------+------------+------------+--------------+ | name | color | SUM(score) | max(score) | avg(score) | count(color) | +------+--------+------------+------------+------------+--------------+ | AAA | BLUE | 20 | 20 | 20.0000 | 1 | | AAA | RED | 10 | 10 | 10.0000 | 1 | | AAA | YELLOW | 50 | 50 | 50.0000 | 1 | | AAA | NULL | 80 | 50 | 26.6667 | 3 | | BBB | BLUE | 60 | 30 | 30.0000 | 2 | | BBB | RED | 15 | 15 | 15.0000 | 1 | | BBB | NULL | 75 | 30 | 25.0000 | 3 | | CCC | BLUE | 25 | 25 | 25.0000 | 1 | | CCC | RED | 52 | 40 | 26.0000 | 2 | | CCC | NULL | 77 | 40 | 25.6667 | 3 | | NULL | NULL | 232 | 50 | 25.7778 | 9 | +------+--------+------------+------------+------------+--------------+ 11 rows in set (0.00 sec)
GROUP BY name, colorで7行の結果だったものが、今度はWITH ROLLUPを追加することで11行の結果に変わりました。追加された行のデータを確認すると、各colorがNULLのところには対応する各nameの集計結果が入っており、name, colorともにNULLの行には全体の集計結果
データにNULLがある場合
ROLLUPの挙動が確認できたところで、ではデータそのものにNULLがあった場合は、小計された行とどうやって見分けるのでしょうか。今度はcolorカラムにあるNOT NULL制約を外して、colorがNULLであるデータも追加してみましょう。
mysql> ALTER TABLE scores MODIFY COLUMN color varchar(30); mysql> INSERT INTO scores VALUES (10, 'AAA', NULL, 100), (11, 'BBB', 'RED', 12), (12, 'CCC', NULL, 200);
このデータに対して以下のようにクエリを実行すると、たしかにサマリーされたデータとcolorがNULLであったデータの集計は、区別をつけることが難しいことがわかります。
mysql> SELECT name, color, SUM(score), max(score), avg(score), count(color) FROM scores GROUP BY name, color WITH ROLLUP; +------+--------+------------+------------+------------+--------------+ | name | color | SUM(score) | max(score) | avg(score) | count(color) | +------+--------+------------+------------+------------+--------------+ | AAA | NULL | 100 | 100 | 100.0000 | 0 | | AAA | BLUE | 20 | 20 | 20.0000 | 1 | | AAA | RED | 10 | 10 | 10.0000 | 1 | | AAA | YELLOW | 50 | 50 | 50.0000 | 1 | | AAA | NULL | 180 | 100 | 45.0000 | 3 | | BBB | BLUE | 60 | 30 | 30.0000 | 2 | | BBB | RED | 27 | 15 | 13.5000 | 2 | | BBB | NULL | 87 | 30 | 21.7500 | 4 | | CCC | NULL | 200 | 200 | 200.0000 | 0 | | CCC | BLUE | 25 | 25 | 25.0000 | 1 | | CCC | RED | 52 | 40 | 26.0000 | 2 | | CCC | NULL | 277 | 200 | 69.2500 | 3 | | NULL | NULL | 544 | 200 | 45.3333 | 10 | +------+--------+------------+------------+------------+--------------+ 13 rows in set (0.00 sec)
これを区別するにはGROUPING関数を利用します。GROUPING関数は、引数が単一のカラムである場合はその行がROLLUPによって出力された結果の行である場合1を、そうでない場合は0を返すようになります。
もし、引数に複数のカラムを指定した場合は、各カラムの結果からビットの組み合わせを返すようになります。下記の例で言えば、1行のデータにはGROUPING(color)は0となっていますが、5行目は1になっていることで、5行目のデータがROLLUPによって出力された小計であることがわかります。さらにnameとcolorの合計行は、GROUPING(name, color)が3になっていることがわかります。
mysql> SELECT name, color, SUM(score), GROUPING(name), GROUPING(color),GROUPING(name, color), bin(GROUPING(name, color)) FROM scores GROUP BY name, color WITH ROLLUP; +------+--------+------------+----------------+-----------------+-----------------------+----------------------------+ | name | color | SUM(score) | GROUPING(name) | GROUPING(color) | GROUPING(name, color) | bin(GROUPING(name, color)) | +------+--------+------------+----------------+-----------------+-----------------------+----------------------------+ | AAA | NULL | 100 | 0 | 0 | 0 | 0 | | AAA | BLUE | 20 | 0 | 0 | 0 | 0 | | AAA | RED | 10 | 0 | 0 | 0 | 0 | | AAA | YELLOW | 50 | 0 | 0 | 0 | 0 | | AAA | NULL | 180 | 0 | 1 | 1 | 1 | | BBB | BLUE | 60 | 0 | 0 | 0 | 0 | | BBB | RED | 27 | 0 | 0 | 0 | 0 | | BBB | NULL | 87 | 0 | 1 | 1 | 1 | | CCC | NULL | 200 | 0 | 0 | 0 | 0 | | CCC | BLUE | 25 | 0 | 0 | 0 | 0 | | CCC | RED | 52 | 0 | 0 | 0 | 0 | | CCC | NULL | 277 | 0 | 1 | 1 | 1 | | NULL | NULL | 544 | 1 | 1 | 3 | 11 | +------+--------+------------+----------------+-----------------+-----------------------+----------------------------+ 13 rows in set (0.02 sec)
この関数をIF関数と組み合わせることで、以下のように出力されることも可能です。
> SELECT IF(GROUPING(name), 'ALL MEMBER', name) as '名前', IF(GROUPING(color),'TOTAL SCORE',color) as '色', SUM(score) FROM scores GROUP BY name, color WITH ROLLUP; +------------+-------------+------------+ | 名前 | 色 | SUM(score) | +------------+-------------+------------+ | AAA | NULL | 100 | | AAA | BLUE | 20 | | AAA | RED | 10 | | AAA | YELLOW | 50 | | AAA | TOTAL SCORE | 180 | | BBB | BLUE | 60 | | BBB | RED | 27 | | BBB | TOTAL SCORE | 87 | | CCC | NULL | 200 | | CCC | BLUE | 25 | | CCC | RED | 52 | | CCC | TOTAL SCORE | 277 | | ALL MEMBER | TOTAL SCORE | 544 | +------------+-------------+------------+ 13 rows in set (0.00 sec)
8.0.12での変更点
バージョン8.
mysql> SELECT IF(GROUPING(name), 'ALL MEMBER', name) as '名前', IF(GROUPING(color),'TOTAL SCORE',color) as '色', SUM(score) FROM scores WHERE color is not null GROUP BY scores.name, scores.color WITH ROLLUP ORDER BY 3; +------------+-------------+------------+ | 名前 | 色 | SUM(score) | +------------+-------------+------------+ | AAA | RED | 10 | | AAA | BLUE | 20 | | CCC | BLUE | 25 | | BBB | RED | 27 | | AAA | YELLOW | 50 | | CCC | RED | 52 | | BBB | BLUE | 60 | | CCC | TOTAL SCORE | 77 | | AAA | TOTAL SCORE | 80 | | BBB | TOTAL SCORE | 87 | | ALL MEMBER | TOTAL SCORE | 244 | +------------+-------------+------------+ 11 rows in set (0.00 sec)
まとめ
今回は簡単に小計、合計を出力する方法としてROLLUPを紹介しました。GROUP BYに追加するだけで簡単に出力することが可能であるため、即時で集計する必要がある場合には非常に便利です。
より詳しい説明については公式ドキュメントの12.