mysqlの集計関数とGROUP BY句の挙動を確認してみた

SHIFT Group 技術ブログ

2023年3月2日 16:48

はじめに

こんにちは、SHIFTの開発部門に所属しているmurasawaです。
中途で入社、バックエンド関連の開発を担当しています。

現在、認証基盤の開発を行っています。
開発の中でDB等、新たに学んだり得た知見をアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。

今回はmysqlに備わっている集計関数を実際に使いその結果を確認してみました。

使用環境 mysql:5.7.35

以下テーブルを用いて操作を行います。

mysql> DESC orders;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field      | Type             | Null | Key | Default           | Extra                       |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255)     | YES  |     | NULL              |                             |
| price      | int(10)          | YES  |     | NULL              |                             |
| quantity   | varchar(255)     | YES  |     | NULL              |                             |
| category   | varchar(255)     | YES  |     | NULL              |                             |
| created_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.02 sec)

mysql> select * from orders;
+----+----------+-------+----------+----------+---------------------+---------------------+
| id | name     | price | quantity | category | created_at          | updated_at          |
+----+----------+-------+----------+----------+---------------------+---------------------+
|  1 | NULL     |  2000 | 1        | A        | 2022-11-04 13:45:30 | 2022-11-04 16:13:46 |
|  2 | kato     |  3000 | 2        | A        | 2022-11-04 13:45:47 | 2022-11-04 16:13:52 |
|  3 | sato     |  2500 | 5        | A        | 2022-11-04 13:46:03 | 2022-11-04 16:13:56 |
|  4 | ito      |  1000 | 1        | B        | 2022-11-04 13:46:22 | 2022-11-04 16:14:00 |
|  5 | sato     |  4000 | 2        | B        | 2022-11-04 13:46:35 | 2022-11-04 16:14:03 |
|  6 | yamamoto |   700 | 3        | A        | 2022-11-04 13:46:56 | 2022-11-04 16:14:09 |
|  7 | kato     |  2700 | 10       | B        | 2022-11-04 13:47:17 | 2022-11-04 16:14:14 |
|  8 |          |  2700 | 5        | B        | 2022-11-04 14:17:27 | 2022-11-04 16:14:33 |
+----+----------+-------+----------+----------+---------------------+---------------------+
8 rows in set (0.03 sec)

COUNT関数

count関数を使うことで行数を出力させることができます。

試しに8行のテーブルでid列をしてcount関数を実行すると

mysql> select * from orders;
+----+----------+-------+----------+----------+---------------------+---------------------+
| id | name     | price | quantity | category | created_at          | updated_at          |
+----+----------+-------+----------+----------+---------------------+---------------------+
|  1 | NULL     |  2000 | 1        | A        | 2022-11-04 13:45:30 | 2022-11-04 16:13:46 |
|  2 | kato     |  3000 | 2        | A        | 2022-11-04 13:45:47 | 2022-11-04 16:13:52 |
|  3 | sato     |  2500 | 5        | A        | 2022-11-04 13:46:03 | 2022-11-04 16:13:56 |
|  4 | ito      |  1000 | 1        | B        | 2022-11-04 13:46:22 | 2022-11-04 16:14:00 |
|  5 | sato     |  4000 | 2        | B        | 2022-11-04 13:46:35 | 2022-11-04 16:14:03 |
|  6 | yamamoto |   700 | 3        | A        | 2022-11-04 13:46:56 | 2022-11-04 16:14:09 |
|  7 | kato     |  2700 | 10       | B        | 2022-11-04 13:47:17 | 2022-11-04 16:14:14 |
|  8 |          |  2700 | 5        | B        | 2022-11-04 14:17:27 | 2022-11-04 16:14:33 |
+----+----------+-------+----------+----------+---------------------+---------------------+
8 rows in set (0.03 sec)

mysql> select count(id) from orders;
+-----------+
| count(id) |
+-----------+
|         8 |
+-----------+
1 row in set (0.02 sec)
##

8と出力されました。

次にnullと空文字の含まれている name列を指定してみます。

mysql> select count(name) from orders;
+-------------+
| count(name) |
+-------------+
|           7 |
+-------------+
1 row in set (0.01 sec)

nullである行はカウントされず7が返ってきました。

次にすべての列を指定してカウントしてみます。

mysql> select count(*) from orders;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.01 sec)

8が返ってきました。
すべての列を指定した場合はいずれかの列に値が入っていればカウントされるようです。

DISTINCT

DISTINCTを指定することで値の重複する行を除いてカウントすることができます。

mysql> select count(distinct name) from orders;
+----------------------+
| count(distinct name) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.01 sec)

sato,katoの重複分とnullが除かれ、5が出力されました。

MAX関数,MIN関数

指定したカラムの最大値を取得することができます。

mysql> select max(price), min(price) from orders;
+------------+------------+
| max(price) | min(price) |
+------------+------------+
|       4000 |        700 |
+------------+------------+
1 row in set (0.01 sec)

price列での最高額は4000、最低額は700なので4000,700を取得できました。

count関数同様、distinctを指定し重複した値を取り除くことができますが、結果としては変わりません

SUM関数

指定したカラムの合計値を取得することができます。

mysql> select sum(price) from orders;
+------------+
| sum(price) |
+------------+
| 15900      |
+------------+
1 row in set (0.01 sec)

price列の値をすべて足した値を取得できました。

この関数も同様にdistinctを指定し重複した値を除くことができます。

価格などではなく、ユニークな数字であればdistinctを用いる場面もありそうですね。

AVG関数

指定したカラムの平均値を取得することができます。

mysql> select avg(price) from orders;
+------------+
| avg(price) |
+------------+
| 2271.4286  |
+------------+
1 row in set (0.01 sec)

priceの平均である2271.4286が取得できました。

この関数も同様にdistinctを指定し重複した値を除くことができます。

GROUP BYと集計関数の組み合わせ

上記集計関数はgroup byと組み合わせることができます。

実際の使い方としても組み合わせて使う場面が多いと考えられます。

COUNT関数とGROUP BY

categoryというカラムを用いてcategoryA,Bごとのcountを行います。

mysql> select category, count(id) from orders group by category;
+----------+-----------+
| category | count(id) |
+----------+-----------+
| A        |         4 |
| B        |         4 |
+----------+-----------+
2 rows in set (0.02 sec)

MAX,MIN関数とGROUP BY

categoryA,Bごとの最大値、最小値を取得します。

mysql> select category, max(price), min(price) from orders group by category;
+----------+------------+------------+
| category | max(price) | min(price) |
+----------+------------+------------+
| A        |       3000 |        700 |
| B        |       4000 |       1000 |
+----------+------------+------------+
2 rows in set (0.01 sec)

SUM関数とGROUP BY

categoryA,Bごとの合計値を取得します。

mysql> select category, sum(price) from orders group by category;
+----------+------------+
| category | sum(price) |
+----------+------------+
| A        | 8200       |
| B        | 10400      |
+----------+------------+
2 rows in set (0.01 sec)

AVG関数とGROUP BY

categoryA,Bごとの平均値を取得します。

mysql> select category, avg(price) from orders group by category;
+----------+------------+
| category | avg(price) |
+----------+------------+
| A        | 2050.0000  |
| B        | 2600.0000  |
+----------+------------+
2 rows in set (0.01 sec)

グルーピングをしてグループごとに集計していくことを数行で行うことができます

HAVING句

また上記集計関数とgroup by句によっていろいろな値を算出できますが、where区内では集計関数が使えないため、絞り込みが行えません

そんな時HAVING句を使用します。

mysql> select category, sum(price) from orders group by category;
+----------+------------+
| category | sum(price) |
+----------+------------+
| A        | 8200       |
| B        | 10400      |
+----------+------------+
2 rows in set (0.01 sec)

mysql> select category, sum(price) from orders group by category having sum(price) >= 10000;
+----------+------------+
| category | sum(price) |
+----------+------------+
| B        | 10400      |
+----------+------------+
1 row in set (0.02 sec)

categoryA,Bごとにsum関数で集計した結果に対して、

having sum(price) >= 10000

とすることで合計値が10000以上のものだけを取得することができます。

終わりに

集計関数に関してはcountを主に使用していたので、今回の記事でほかの関数を知ることができました。

うまく使えば、容易にデータ分析や標準偏差などの統計を行うことができるのではないかと感じました。

参考

https://www.javadrive.jp/mysql/function/

【この公式ブロガーのおすすめ記事】

執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。
DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

PHOTO：UnsplashのRezvani

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。