SQLについて、学習したことをアウトプットさせていただきます。
インプットは、こちらの動画を参考にさせていただきました。
今回は、GROUP BYについてアウトプットします。
GROUP BY
GROUP BYはある数値のまとまりを取得する際に使用します。
以下のテーブル構造で実際に手を動かしてGROUP BYを学習しました。
mysql> desc users;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(10) | YES | | NULL | |
| created_atc | date | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
データの中身は以下のとおり。
| id | username | created_at |
|---|---|---|
| 1 | goku | 2021-9-12 |
| 2 | gohan | 2021-9-12 |
| 3 | kuririn | 2021-9-12 |
| 4 | beji-ta | 2021-9-13 |
| 5 | trankus | 2021-9-14 |
| 6 | sell | 2021-9-15 |
| 7 | huri-za | 2021-9-15 |
例えば、上の表から「今年の9月12日〜9月15日までの日次の会員登録数を取得しなさい」という課題があった場合
SELECT count(username) FROM users where created_at='2021-9-12'; > 3
SELECT count(username) FROM users where created_at='2021-9-13'; > 1
SELECT count(username) FROM users where created_at='2021-9-14'; > 1
SELECT count(username) FROM users where created_at='2021-9-15'; > 2
と連続で入力するのはとても面倒なので、GROUP BYを使って
SELECT created_at, COUNT(username) FROM users GROUP BY created_at;
と入力することで、１行で求めている値を取得することができます。
さきほどのSQLを実行すると、以下の表のような結果が返って来ます。
| created_at | count(username) |
|---|---|
| 2021-9-12 | 3 |
| 2021-9-13 | 1 |
| 2021-9-14 | 1 |
| 2021-9-15 | 2 |
GROUP BY created_at で指定したカラム、ここではcreated_atで値をまとめてあげています。
created_atごとに、usernameがどれだけあるかカウントしてやって、その値をGROUP BYでまとめてあげています。
SELECT count(username) FROM users GROUP BY created_at;
これを実行すると、count(username)だけになってなんのことか分かりません。
| count(username) |
|---|
| 3 |
| 1 |
| 1 |
| 2 |
次は、カラムにjinnsyuを加えて、以下のようなデータ構成にして、
mysql> desc users;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| username | varchar(10) | YES | | NULL | |
| created_at | date | YES | | NULL | |
| jinsyu | varchar(10) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from users;
+------+----------+------------+----------+
| id | username | created_at | jinsyu |
+------+----------+------------+----------+
| 1 | goku | 2021-09-12 | saiyajin |
| 2 | gohan | 2021-09-12 | saiyajin |
| 3 | kuririn | 2021-09-12 | human |
| 4 | beji-ta | 2021-09-13 | saiyajin |
| 5 | trankus | 2021-09-14 | saiyajin |
| 6 | sell | 2021-09-15 | monster |
| 7 | huri-za | 2021-09-15 | monster |
+------+----------+------------+----------+
7 rows in set (0.00 sec)
「日毎の会員登録数をjinsyu別で取得する」という課題に取り組んだとしたら、
$ SELECT created_at, COUNT(username), jinsyu FROM users GROUP BY created_at;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'doragon_ball.users.jinsyu' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
これではエラーになります。
SELECTしたjinsyuがGROUP BY句に含まれておらず、システムがどの場所に値を入れたらいいのか分からない箇所があると言っています。
+------------+-----------------+----------+
| created_at | COUNT(username) | jinsyu |
+------------+-----------------+----------+
| 2021-09-12 | 3 | ? |
| 2021-09-13 | 1 | saiyajin |
| 2021-09-14 | 1 | saiyajin |
| 2021-09-15 | 2 | monster |
+------------+-----------------+----------+
2021-9-12のレコードの?の部分について、jinsyuカラムにhyumanなのか、saiyajinなのかどっちの値を入れたらいいのか分かりませんといって、エラーが起きています。
なので、さきほどのSQL文の値のまとまり（GROUP BY）にjinsyuを加えるとエラーが解消します。
$ SELECT created_at, COUNT(username), jinsyu FROM users GROUP BY created_at, jinsyu;
+------------+-----------------+----------+
| created_at | COUNT(username) | jinsyu |
+------------+-----------------+----------+
| 2021-09-12 | 1 | human |
| 2021-09-12 | 2 | saiyajin |
| 2021-09-13 | 1 | saiyajin |
| 2021-09-14 | 1 | saiyajin |
| 2021-09-15 | 2 | monster |
+------------+-----------------+----------+
5 rows in set (0.00 sec)
GROUP BYを使うときはSQLが入れる値を特定できるように軸になるカラムを指定してあげることがコツ！！