前回は、SELECTの結果の並び替えを行うORDER BY句、表示件数の絞り込みを行うLIMIT句を解説した。
また、そこまでの処理の流れについても触れた。
処理の流れは覚えておかないと結果が意図したものにならないことがあるので、気を付けておこう。
以下がその記事だ。
【PHP講座15】SQL編2 -SELECTの結果を操作する- | Shino’s Mind Archive
さて、今回はさらに新しくグループ化というものを解説する。
ここから一気に難易度が上がるが、これも必要な内容だ。
ゆっくり、理解していこう。
グループ化
いきなりグループ化と書いたが、まずはどんなことができるかから紹介しよう。
例えば、以下のような店舗ごとの売り上げを管理しているテーブルがあったとする。
| id | area | name | sales |
|---|---|---|---|
| 1 | A | 店舗1 | 100000 |
| 2 | A | 店舗2 | 120000 |
| 3 | A | 店舗3 | 80000 |
| 4 | B | 店舗4 | 90000 |
| 5 | B | 店舗5 | 70000 |
| 6 | B | 店舗6 | 150000 |
| 7 | C | 店舗7 | 90000 |
| 8 | C | 店舗8 | 50000 |
このときに、エリアごとの合計を出したい、といったことができる。
どういうことかというと、あるカラムが同じものを一つのグループとして、それに対する合計だったり、平均だったりを求めることができるのだ。
つまり、合計を求めるとしたら、以下のような表を得ることができる。
| area | SUM(sales) |
|---|---|
| A | 300000 |
| B | 310000 |
| C | 140000 |
これも、SQLだけで可能だ。
というわけで、構文を見ていこう。
SELECT 列名 FROM テーブル名 GROUP BY 列名;今度は、GROUP BYというものがついている。
ここに、上の説明でいう、同じデータでグループ化するカラムを入れてあげる。
そして、注意点が一つ。
SELECT直後に書く列名だが、ここにそのまま書けるのはGROUP BYで指定した列名だけだ。
その他の列は、集合関数と呼ばれる関数を通さなければいけない。
集合関数
集合関数とは、グループ化したデータたちを処理して、一つの値を算出する関数のこと。
種類を挙げると…
- COUNT : データの個数を算出する
- SUM : データの合計を算出する
- AVG : データの平均値を算出する
- MAX : データの最大値を算出する
- MIN : データの最小値を算出する
などがある。
ちなみに、今回GROUP BYで使うという解説をしているが、別にGROUP BYが無くても使える。
その場合、全データを一つのグループとしてみなしている。
例えば、以下のSQL。
SELECT MAX(price) FROM product;これを実行すると、列priceの中で最も大きい値を結果として得ることができる。
GROUP BY具体例
では、GROUP BY句を使ったときの具体例を見ていこう。
今回用に、新しく以下のstoreテーブルを作成する。
| id | area | name | sales |
|---|---|---|---|
| 1 | A | 店舗1 | 100000 |
| 2 | A | 店舗2 | 120000 |
| 3 | A | 店舗3 | 80000 |
| 4 | B | 店舗4 | 90000 |
| 5 | B | 店舗5 | 70000 |
| 6 | B | 店舗6 | 150000 |
| 7 | C | 店舗7 | 90000 |
| 8 | C | 店舗8 | 50000 |
ちょっと数が多いので、サクッとSQLで作ってしまおう。
まず、いつものようにphpMyAdminでtestdbを選択しておいてほしい。
そうしたら、上部のSQLを押して、入力欄を開いておこう。
そこに、以下の内容をコピペして実行してほしい。
CREATE TABLE `testdb`.`store` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '通しID' , `area` VARCHAR(20) NOT NULL COMMENT 'エリア' , `name` VARCHAR(20) NOT NULL COMMENT '店舗名' , `sales` INT NOT NULL COMMENT '売り上げ' , PRIMARY KEY (`id`)) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_general_ci COMMENT = '店舗管理テーブル';これで、テーブルが出来上がるはずだ。
テーブルができたら、今度はそのsotreテーブルを選択した状態で再度SQLの入力画面を開こう。
今度は、以下を全部コピペして実行だ。
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'A', '店舗1', '100000');
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'A', '店舗2', '120000');
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'A', '店舗3', '80000');
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'B', '店舗4', '90000');
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'B', '店舗5', '70000');
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'B', '店舗6', '150000');
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'C', '店舗7', '90000');
INSERT INTO `store` (`id`, `area`, `name`, `sales`) VALUES (NULL, 'C', '店舗8', '50000');これで、データが入ったはず。
表示タブを押して、上の表と同じデータが入っているか確認しておこう。
これで準備ができた。
では、以下のSQLを実行してみよう。
SELECT area, SUM(sales) FROM store GROUP BY area;これを実行すると、以下の結果が返ってくる。
| area | SUM(sales) |
|---|---|
| A | 300000 |
| B | 310000 |
| C | 140000 |
これは、最初に出した例通りだ。
エリアごとの売り上げ合計値を算出して表示している。
ちょっと、処理の流れを細かく追ってみよう。
まず、GROUP BY句でarea列が指定されているので、エリアのデータが同じものがそれぞれのグループになる。
各グループと、その中のデータのうちsales列の情報を抜き出してみよう。
- エリアA : 100000, 120000, 80000
- エリアB : 90000, 70000, 150000
- エリアC : 90000, 50000
そして、表示部分でエリアの情報と、sales列をSUM関数に通している。
これで、各グループ内でsales列の情報が合計される。
- エリアA : 100000 + 120000 + 80000 = 300000
- エリアB : 90000 + 70000 + 150000 = 310000
- エリアC : 90000 + 50000 = 140000
その結果を表形式として表示している、ということになる。
ちょっと複雑だが、動きはなんとなくわかっただろうか。
GROUP BYの結果をさらに絞るHAVING句
ここで、もう一つ発展させよう。
GROUP BYでグループ化したあと、さらに表示するデータを絞り込みたいとしよう。
それに使えるのが、HAVING句だ。
これで、例えば上でやったエリアごとの合計について、さらにその合計が○○以下を表示する、といった条件をつけることができる。
構文を見てみよう。
SELECT 列名 FROM テーブル名 GROUP BY 列名 HAVING 条件;使い方はWHERE句と似ているが、条件に書けるものがちょっと変わる。
ここの時点ですでにグループ化はされており、その結果に対して条件を指定する…つまり、ここも集合関数を使わなければいけない。
例えば、店舗数が3以上のエリアについて、そのエリアの合計売上を出したい場合は…
SELECT area, SUM(sales) FROM store GROUP BY area HAVING COUNT(name) >= 3;このようになる。
COUNT(name)で各エリアの店舗数を出すことができ、それが3以上のデータのみを表示している、というわけだ。
WHERE句を使う際の注意
ここで一つ注意。
実は、WHERE句も併せて使うこと自体は可能だ。
その場合、書き方は以下のようになる。
SELECT 列名 FROM テーブル名 WHERE 条件 GROUP BY 列名 HAVING 条件;ただ、このときの動き方に注意。
実は、処理の順番でいくと…
- WHERE句でのデータ絞り込み
- GROUP BY句でのグループ化
- HAVING句でのデータ絞り込み
という順番になっている。
つまり、WHERE句はグループ化する前のデータに対する絞り込みになることに注意してほしい。
どういうことか、具体例を。
売り上げが10万以上の店舗だけに注目して、エリアごとの合計値を出したい、というような場合だ。
これについては、以下のSQLで出すことができる。
SELECT area, SUM(sales) FROM store WHERE sales >= 100000 GROUP BY area;実行した結果は、以下のようになるはずだ。
| area | SUM(sales) |
|---|---|
| A | 220000 |
| B | 150000 |
これについて、処理も順番に見ていこう。
まず、最初のstoreテーブルは以下の通りだ。
| id | area | name | sales |
|---|---|---|---|
| 1 | A | 店舗1 | 100000 |
| 2 | A | 店舗2 | 120000 |
| 3 | A | 店舗3 | 80000 |
| 4 | B | 店舗4 | 90000 |
| 5 | B | 店舗5 | 70000 |
| 6 | B | 店舗6 | 150000 |
| 7 | C | 店舗7 | 90000 |
| 8 | C | 店舗8 | 50000 |
ここで、最初にWHEREによる絞り込みが行われる。
つまり、この時点でデータが以下のようになる。
| id | area | name | sales |
|---|---|---|---|
| 1 | A | 店舗1 | 100000 |
| 2 | A | 店舗2 | 120000 |
| 6 | B | 店舗6 | 150000 |
この状態で、area列によるグループ化が行われる。
表示はSUM…合計なので、Aは220000、Bは150000という結果になった、というわけだ。
おわりに
今回は、ある列で同じデータをまとめるグループ化というものを解説した。
なかなか複雑ではあるが、これもよく使うものの一つ。
しっかり、処理を追って何をしているか理解していこう。
さて、次回はテーブルの結合を扱う。
これまではテーブル一つに対するSQLだったが、二つ以上のテーブルへ同時にアクセスし、結果を表示する。
これまたさらに複雑になっていくが、慌てずゆっくり進めていこう。
コメント