読んで欲しい人
- 「GROUP BY」クエリを指定した時に、何が起きているのかイマイチイメージが掴めない...
- 「GROUP BY」で取得する時に
ERROR 1055 (42000): Expression xx of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byみたいなエラーが出るんですけど。
っていう人はぜひ読んでみてください！
※ この記事では MySQL を使用することを想定しています。
GROUP BY を使用するユースケース
例えば、以下のような DDL で定義されるテーブルがあるとします。
-- 売上テーブル
CREATE TABLE sales (
id INT PRIMARY KEY,
product_name VARCHAR(50),
category VARCHAR(20),
price INT,
quantity INT,
region VARCHAR(10)
);
そして、このテーブルには以下のようなレコードが挿入されているとします。
| id | product_name | category | price | quantity | region |
|---|---|---|---|---|---|
| 1 | ノート PC | 電子機器 | 80000 | 2 | 東京 |
| 2 | マウス | 電子機器 | 3000 | 5 | 大阪 |
| 3 | 小説 A | 書籍 | 1500 | 3 | 東京 |
| 4 | キーボード | 電子機器 | 8000 | 1 | 名古屋 |
| 5 | 参考書 B | 書籍 | 2800 | 2 | 大阪 |
| 6 | デスク | 家具 | 25000 | 1 | 東京 |
| 7 | チェア | 家具 | 15000 | 2 | 福岡 |
| 8 | タブレット | 電子機器 | 45000 | 1 | 大阪 |
| 9 | 雑誌 C | 書籍 | 800 | 4 | 名古屋 |
| 10 | ソファ | 家具 | 60000 | 1 | 福岡 |
ここで、あなたは「各カテゴリごとの売上合計を教えて欲しい」と注文を受けました。ここでGROUP BYクエリの出番です。
GROUP BYクエリを使用すると、指定した列ごと（今回の場合はcategoryごと）にレコードのグループを編成し、そのレコードグループに対して集約関数を適用させて値を計算できます。
例えば、
SELECT xxx FROM sales GROUP BY category;
のようにcategory列でGROUP BYクエリを実行した結果、以下のようにレコードグループを編成することができます。（あくまでイメージ）
category = "電子機器"のグループ
| id | product_name | category | price | quantity | region |
|---|---|---|---|---|---|
| 1 | ノート PC | 電子機器 | 80000 | 2 | 東京 |
| 2 | マウス | 電子機器 | 3000 | 5 | 大阪 |
| 4 | キーボード | 電子機器 | 8000 | 1 | 名古屋 |
| 8 | タブレット | 電子機器 | 45000 | 1 | 大阪 |
category = "書籍"のグループ
| id | product_name | category | price | quantity | region |
|---|---|---|---|---|---|
| 3 | 小説 A | 書籍 | 1500 | 3 | 東京 |
| 5 | 参考書 B | 書籍 | 2800 | 2 | 大阪 |
| 9 | 雑誌 C | 書籍 | 800 | 4 | 名古屋 |
category = "家具"のグループ
| id | product_name | category | price | quantity | region |
|---|---|---|---|---|---|
| 6 | デスク | 家具 | 25000 | 1 | 東京 |
| 7 | チェア | 家具 | 15000 | 2 | 福岡 |
| 10 | ソファ | 家具 | 60000 | 1 | 福岡 |
そしてSELECT句の中で、各レコードグループに対して集約関数を適用させることができます。
例えば、
SELECT category, SUM(price * quantity) as total_sales FROM sales GROUP BY category;
とした場合、各レコードグループごとに以下の値を引っ張ってきてくれます。
- 各レコードグループの
category - 各レコードグループの中の全レコードの
price * quantitiy合計値
なので結果は以下のようになります。
| category | total_sales |
|---|---|
| 電子機器 | 228000 |
| 書籍 | 13300 |
| 家具 | 115000 |
GROUP BYのだいたいのイメージは掴めましたか？
GROUP BY を使用すると気に注意すべき点
GROUP BYを使用する時に注意しないといけない点の一つとして、GROUP BYを使用する場合、SELECT句で指定できるものは、以下のものに限られます。
-
GROUP BYで指定される列 - 集約関数の計算結果
これは冒頭のERROR 1055 (42000): Expression xx of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byのエラーに関わってきます。
例えば以下のようなsqlを書いた場合、このエラーが返ってきます。
SELECT product_name, category, SUM(price * quantity) as total_sales FROM sales GROUP BY category;
返ってくるエラー
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sandbox.sales.product_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
これは一体何が起こっているのでしょうか？
categoryでGROUP BYをした場合に編成されるレコードグループの一つをもう一度みてみましょう。
category = "書籍"のグループ
| id | product_name | category | price | quantity | region |
|---|---|---|---|---|---|
| 3 | 小説 A | 書籍 | 1500 | 3 | 東京 |
| 5 | 参考書 B | 書籍 | 2800 | 2 | 大阪 |
| 9 | 雑誌 C | 書籍 | 800 | 4 | 名古屋 |
このグループでは、categoryはGROUP BYで指定された列であり、各レコードグループごとに一意であることが保証されています。
一方で、product_nameに関しては、小説 A、参考書 B、雑誌 Cの3つが存在します。
このproduct_nameに対して SELECTを当てたとしても結局どのレコード(id = 3, 5, 9)のproduct_nameをとってこれば良いかMySQLはわからないため、エラーになってしまいます。
これはidやregionについても同様と言えます。また、今回はSUM(price * quantity) as total_salesという形で集約関数を使用して取得できていますが、price, quantityも集約関数を使わずにその列だけで取得しようとする場合、同様のエラー発生がします。
GROUP BYを使用する際は、この点に注意しましょう！