GROUP BY句とHAVING句

本章では特定列をグループ化し、集計できる「GROUP BY」句と、その抽出条件を指定する「HAVING」句について解説します。
本章の目標
  • グループ化の考え方を理解する
  • 「GROUP BY」句の使い方をおぼえる
  • 「GROUP BY」句と集計関数を組み合わせた使い方をおぼえる
  • 「HAVING」句の使い方をおぼえる
ページの先頭へ
ページの先頭へ

1.「GROUP BY」句の基本

データグループ化の基本的な考え方を学習します。

1. 学習の準備

まずは、「GROUP BY」句を学習するための準備として、新しいテーブルを作成し、データを登録しましょう。


* 新規テーブルの作成
下表のテーブル「orderinfo」テーブルを作成してください。

テーブル名:「orderinfo」

項目 データのタイプ 列名 データ型 属性
受注番号 数値(整数) orderno INT PRIMARY KEY
AUTO_INCREMENT
ユーザー名 文字列
(最大半角20文字/全角10文字)
user VARCHAR(20)
ISBN番号 文字列
(最大半角20文字/全角10文字)
price VARCHAR(20)
注文数 数値(整数) quantity INT
注文日 日付 date date


CREATE TABLE orderinfo (
orderno INTEGER AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(20),
isbn VARCHAR(20),
quantity INTEGER,
date date
)ENGINE=InnoDB;

* 新規テーブルへのデータ登録
次に下記のデータを登録してください。

orderno user price quantity date
1kanda0000112010-07-01
2shibuya0000122010-07-15
3akiba0000112010-08-02
4meguro0000132010-07-17
5kanda0000212010-08-22
6shibuya0000232010-09-03
7kanda0000212010-07-25
8meguro0000342010-07-30
9ueno0000312010-08-12
10shibuya0000312010-08-21
11tamachi0000422010-09-14
12meguro0000432010-07-11
13shibuya0000462010-07-19
14akiba0000412010-08-19
15kanda0000542010-09-01
16tamachi0000522010-08-22
17shibuya0000512010-07-01
18kanda0000612010-07-15
19osaki0000612010-08-20
20shibuya0000632010-07-30
21meguro0000622010-09-01
22tamachi0000662010-09-30
23kanda0000712010-07-11
24shibuya0000732010-07-21
25kanda0000722010-08-15
26meguro0000712010-07-02
27akiba0000742010-07-25
28ueno0000712010-08-15
29ueno0000112010-08-14
30ueno0000212010-07-15


INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00001',1,'2010-07-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15');

* データの確認
データの中身を確認してみましょう。

SELECT * FROM orderinfo;









2. 「GROUP BY」句の基本

「GROUP BY句」はある特定の列をキーに(グループキーといいます)、データをグループ化するための句です。
書式は下記のようになります。

SELECT グループ化キー FROM テーブル名 GROUP BY グループ化キー;

先程作成した「orderinfo」で見てみましょう。
「orderinfo」のユーザー名には、複数のユーザーが存在しますが、重複しているデータもあります。
これをユーザー毎にグループ化してみましょう。

SELECT user FROM orderinfo GROUP BY user;





user列がグループ化され重複が排除されたユーザー名の一覧が取得できました。


「GROUP BY」句と似たような働きをする句に、「DISTINCT」句があります。
「DISTINCT」句は、検索結果の重複を排除する機能があります。
書式は下記のようになります。

SELECT DISTINCT 列名 FROM テーブル名;

先程「orderinfo」をuser列でグルーピング化したように、user列の重複を排除するには

SELECT DISTINCT user FROM orderinfo;

となります。




但し「DISTINCT」句は重複を排除するためのものであり、「GROUP BY」句のように、集計関数と同時に使ってグループごとの集計を取るような使い方はできません。

ページの先頭へ

2.「GROUP BY」句と集計関数

「GROUP BY」句を単独で使うことは少なく、集計関数と同時に用いられるのが一般的です。ここでは、「GROUP BY」句と集計関数を組み合わせた使い方を解説します。

1. 「GROUP BY」句とAVG関数

AVG関数は前の章で解説したように平均値を取る関数でしたが、「GROUP BY」句と組み合わせると、グループ化キー毎の平均値を出すことができます。
書式は下記のようになります。

SELECT グループ化キー,AVG(列名) FROM テーブル名 GROUP BY グループ化キー;

例えば「orderinfo」で、ユーザー毎の平均注文数を出す場合は下記のようになります。

SELECT user,AVG(quantity) FROM orderinfo GROUP BY user;






2. 「GROUP BY」句とCOUNT関数

COUNT関数もAVG関数同様、「GROUP BY」句と組み合わせることで、グループ化キーごとの行数を算出することができます。
書式は下記のようになります。

SELECT グループ化キー,COUNT(列名) FROM テーブル名 GROUP BY グループ化キー;

「orderinfo」で、ユーザー毎の行数(この場合は注文回数)を出す場合は下記のようになります。

SELECT user,COUNT(orderno) FROM orderinfo GROUP BY user;






3. 「GROUP BY」句とSUM関数

次はSUM関数と「GROUP BY」句の組み合わせを見ていきます。
書式は下記のようになります。

SELECT グループ化キー,COUNT(列名) FROM テーブル名 GROUP BY グループ化キー;

今度は「orderinfo」で、ISBN番号ごとの注文数の合計を出して見ましょう。
DML文は下記のようになります。

SELECT isbn,SUM(quantity) FROM orderinfo GROUP BY isbn;






4. 「GROUP BY」句とMAX関数、MIN関数

今回はMAX関数、MIN関数と「GROUP BY」句の組み合わせを同時に解説します。
書式はMAX関数の場合は、

SELECT グループ化キー,MAX(列名) FROM テーブル名 GROUP BY グループ化キー;

MIN関数の場合は、

SELECT グループ化キー,MIN(列名) FROM テーブル名 GROUP BY グループ化キー;

のようになりますが、同じグループ化キーで集計する場合、集計関数は複数指定することができます。
例えば「orderinfo」で、ユーザーごとに最初の注文日と最終注文日を調べてみましょう。
最初の注文日はdate列の最小値を、最終注文日は同じくdate列の最大値を取得することで算出できます。
DML文は下記のようになります。

SELECT user,MIN(date),MAX(date) FROM orderinfo GROUP BY user;







グループ化キーは複数指定することもできます。
下記のようになります。

SELECT グループ化キー1, グループ化キー2・・・,集計関数(列名) FROM テーブル名
GROUP BY グループ化キー1, グループ化キー2・・・;

例えば「orderinfo」で、ユーザーごと、ISBN番号ごとの注文数合計をとる場合は、

SELECT user,isbn,SUM(quantity) FROM orderinfo GROUP BY user,isbn;

となります。


また、グループ化キーは「SELECT」句に必ず含めなければならないわけではありません。
例えば下記のように、ユーザーごとの注文数量合計を出す場合に、ユーザー名を出さないように書いても、SQLの構文上はエラーにはなりません。

SELECT SUM(quantity) FROM orderinfo GROUP BY user;



但し結果を見てもわかるように、どのデータの集計結果かはわからないので、特殊な使用例と言えるでしょう。


ページの先頭へ

3.「GROUP BY」句と「HAVING」句

「GROUP BY」句でグループ化したデータに対して、条件を指定してデータを絞り込む際に使用する「HAVING」句について説明します。

1. 「HAVING」句の使い方

「HAVING」句の使い方は、「WHERE」句に似ていますが、SQLでは「WHERE」句の中で集計関数を使えないルールになっていますので、 そのような場合は「HAVING」句を使用します。
「HAVING」句の基本的な書式は下記のようになります。

SELECT グループ化キー,集計関数(列名) FROM テーブル名 GROUP BY グループ化キー
HAVING 集計関数(列名) 演算子 条件;

例えば「orderinfo」で、ISBN番号ごとの注文数の合計を出し、このうち注文数が10以上のデータを抽出する場合を考えてみます。
DML文は下記のようになります。

SELECT isbn,SUM(quantity) FROM orderinfo GROUP BY isbn HAVING SUM(quantity) >=10;

「HAVING」句では、下記の図のように、一旦集計されたデータを基に抽出条件が評価されます。




ページの先頭へ

4.本章のまとめ

本章では「GROUP BY」句の使い方と集計関数との組み合わせ、「HAVING」句の使い方について解説しました。
特に「GROUP BY」句と集計関数を組み合わせて使用するケースは非常に多いので、本章を何度も見直して確実に習得しておきましょう。

ページの先頭へ