SQLに大きなテーブルを作成した際、どのようにデータの集計をすればいいのでしょうか。そんなときに便利なのがSQLのGROUP BYです。このGROUP BYを使用することによって、たとえば「テーブル内でカテゴリー1に当てはまるモノは〇〇個」などといった集計を簡単かつ迅速におこなうことができるのです。
今回の記事ではSQLのGROUP BYとは何かについて詳しく解説をしつつ、SQLのGROUP BYを使ったハンズオンを実施していきます。
- GROUP BYはグループ化のためのSQL文
- SQLのGROUP BYで簡単に集計ができる
- GROUP BYを使ってハンズオンを体験してみる
SQLのGROUP BYとは？
先ほどSQLのGROUP BYで「テーブル内でカテゴリー1に当てはまるモノは〇〇個」といったような集計ができるということをお伝えしました。
しかし、そもそもGROUP BYは集計のために生み出された句ではありません。SQLにおけるGROUP BYの役割とは、SELECT文とセットで使用することで、指定したカラムの値をもとにグループ化をおこなうという役割を持つものなのです。
指定したカラムに同等の値が入っている場合、1つのカテゴリーとしてまとめることが可能となります。
この機能を応用することで、先ほど説明した「テーブル内でカテゴリー1に当てはまるモノは〇〇個」のような使い方をすることが可能です。
言葉だけではイメージがしづらいかと思うので、実際にSQLのGROUP BYを使用した例を見てみましょう。
前提として、以下のようなテーブルがあるとします。
mysql> SELECT * FROM fruits; +--------+-------+ | name | stock | +--------+-------+ | Banana | 10 | | Apple | 10 | | Banana | 39 | | Apple | 23 | | Banana | 50 | +--------+-------+ 5 rows in set (0.00 sec)
バナナとリンゴの在庫がそれぞれ記録されていますが、同じ果物の在庫が別々のレコードに挿入されているため、結局いくつストックがあるかわかりません。
そこでGROUP BYを使用し、同じ名前の果物のストック数を集計することができます。
mysql> SELECT name, sum(stock) from fruits GROUP BY name;
以下が出力結果です。
+--------+------------+ | name | sum(stock) | +--------+------------+ | Banana | 99 | | Apple | 33 | +--------+------------+ 2 rows in set (0.00 sec)
このようにSQLのGROUP BYを使用することによって、バナナとリンゴのストック数を集計することに成功しました。
SQLのGROUP BYを使ってみよう
では、実際にSQLのGROUP BYを使っていきましょう。こちらではMySQLというデータベースを使用していきます。MySQLはバージョンによって動作が異なる可能性もあるため、以下に今回使用したMySQLのバージョンを確認しておきます。
$ mysql --version mysql Ver 8.0.18 for osx10.15 on x86_64 (Homebrew)
準備
まずはMySQLを使用するための準備をしていきます。
MySQLのインストール・接続
まだMySQLをインストールしていない場合は、インストールしましょう。
$ brew install mysql
MySQLのインストールが終了したら、接続します。
$ sudo mysql -u root -p
データベースの作成
次にデータベースをMySQL上に作成し、使用するデータベースを指定しましょう。
mysql> CREATE DATABASE potepan; Query OK, 1 row affected (0.00 sec) mysql> USE potepan;
これでMySQLを使用する準備は完了しました。
テーブルの作成
それでは、実際にハンズオンで使用するテーブルを作成していきます。先ほどの例よりも少し複雑なテーブルを作成し、GROUP BYの効果を実感してみましょう。
mysql> CREATE TABLE importProduct (name varchar(10), price int, category int); Query OK, 0 rows affected (0.02 sec)
テーブル「 importProduct」は以下のような状態です。
mysql> DESC importProduct; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | price | int(11) | YES | | NULL | | | category | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
レコードの作成
テーブルにレコードを挿入していきましょう。
mysql> INSERT INTO importProduct VALUES("Fish", 3400, 1),("Clothes", 2000, 2),("Hobby", 400, 3),("Clothes", 400, 2),("Hobby", 21000, 3),("Fish", 500234, 1),("Hobby", 241, 3),("Clothes", 138, 2),("Fish", 2840, 1);
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
これでテーブルは以下の状態になりました。
mysql> SELECT * FROM importProduct; +---------+--------+----------+ | name | price | category | +---------+--------+----------+ | Fish | 3400 | 1 | | Clothes | 2000 | 2 | | Hobby | 400 | 3 | | Clothes | 400 | 2 | | Hobby | 21000 | 3 | | Fish | 500234 | 1 | | Hobby | 241 | 3 | | Clothes | 138 | 2 | | Fish | 2840 | 1 | +---------+--------+----------+ 9 rows in set (0.00 sec)
現在のままでは、重複した商品名がバラバラになっており、どれだけの値段になったのかがわかりません。そこでGROUP BYを使って集計してみましょう。
GROUP BYで集計に挑戦
まずはGROUP BYを使って、値段の集計に挑戦してみてましょう。
mysql> SELECT name, sum(price) FROM importProduct GROUP BY name;
以下が出力結果です。
+---------+------------+ | name | sum(price) | +---------+------------+ | Fish | 506474 | | Clothes | 2538 | | Hobby | 21641 | +---------+------------+ 3 rows in set (0.00 sec)
いかがでしょうか。このようにGROUP BYを利用することによって、それぞれの商品の合計価格が簡単にわかりました。
まとめ
今回の記事ではSQLのGROUP BYとは何かについて詳しく解説をしつつ、SQLのGROUP BYを使ったハンズオンを実施しました。
テーブル内のデータを集計したいときに便利なGROUP BY。集計することを前提にテーブル設計をするのであれば、集計用のカテゴリをカラム内に用意しておくと良いでしょう。
