Visual Basic データベース講座
VB2005 対応

 

Visual Basic 中学校 > データベース講座 >

第9回 SELECT文による集計

SQLを使用してデータの集合に対する集計・分析を行う手法をGROUP BY句を中心に説明します。

概要

・集計関数を使用して集計を行うことができる。代表的な集計関数は合計値を求めるSUM, 平均値を求めるAVG, 個数を求めるCOUNT, 最大値を求めるMAX, 最小値を求めるMIN。

・GROUP BY句を使用すると集計単位を指定することができる。

例:SELECT 目ID, AVG(体長) FROM T_動物マスタ GROUP BY 目ID

・集計関数はNULLを無視する。

・HAVINGを使用すると集計後のレコードに対してしぼりをかけることができる。

・DISTINCTは重複するレコードを省く効果がある。

 

1.SQLの本領

 

SQLはデータを集合としてとらえてさまざまな集計・分析を行うことができます。値と値の計算であればVBであれなんであれどのような言語でも可能なのですが、データをひとまとまりとして扱いそのまとまりに対して集計・分析を行うことができる言語は多分SQLの他にはありません。

これまではSQLを単にアプリケーションとデータベースをつなぐ言語と位置づけて使用例を紹介してきましたが、今回はSQLによる集計の一端を紹介してSQLの本領に触れていこうと思います。

今回説明するのは合計・平均・個数・最大値・最小値です。どれも集計・分析の中ではごく基本的なものですが、複雑な集計・分析であってもこのような基本を組み合わせているだけという場合が少なくありません。

できればただ説明を読むだけではなく、紹介しているSQL文を実際に実行したり、少し自分で改造して調べてみたりしながら読んでください。SQLを試すためには前回紹介したクエリデザイナを使用することができます。

メモ  -  Animals.mdbが新しくなりました

今回からは新しいバージョンのAnimals.mdbを使用します。2007年2月4日以前にAnimals.mdbをダウンロードされた方は、お手数ですが新しいバージョンのAnimals.mdbをダウンロードして上書きしてください

ここからダウンロードを行うことができます。

データベースのダウンロード    Animals.lzh    26.9KB

2007年2月5日以降にAnimals.mdbをダウンロードされた方はこの作業を行う必要はありません。

ここでダウンロードできるものはデータベース講座の第2回でダウンロードできるものと同じです。

新しいバージョンのAnimals.mdbではT_動物マスタに「生息地ID」と「体長」という2つの項目が追加されました。また、T_生息地マスタというテーブルも追加されています。T_目マスタにはデータを追加しておきました。

T_動物マスタに登録されているいくつかの動物は名前を修正しました。たとえば、旧版では「ジンベイザメ」となっていたのを「ジンベエザメ」に修正しました。

 

2.GROUP BY句

 

2−1.集計単位と集計関数

合計や平均値に限った話ではありませんが、集計を行う場合は何を単位に集計を行うかを指定する必要があります。たとえば学校を想定すると、学年別の平均値を求めるのか、クラス別の平均値を求めるのか、他にも志望校別というのもあるかもしれませんし、志望校別で男女別というのもあるかもしれません。

SQLではこのような集計の単位をGROUP BY句で指定します。また、合計値を求めるにはSUM、平均値を求めるためにはAVGなど集計関数と呼ばれる関数を使用して集計方法を指示します。

代表的な集計関数を表にまとめます。

集計関数 読み方 意味
AVG アベレージ、エーブイジー 平均値
COUNT カウント 個数
MIN ミン 最小値
MAX マックス 最大値
SUM サム 合計値

この5つの集計関数はおそらくAccess以外にもSQL Server、Oracleなどあらゆるデータベース製品で使用可能です。この他にも各データベース製品ごとに固有の集計関数が用意されている場合がありますのでリファレンスを探してみてください。標準偏差や分散、先頭値、末尾値などを集計関数で求めることができる場合があります。最頻値(モード)や中央値(メジアン)もあるかもしれません。

なお、集計単位ごとにまとめることをグループ化と呼ぶ場合が多いのでこの言葉は覚えておいた方がよいです。たとえば、「クラスごとの平均点をも求める」ということを「平均点をクラスでグループ化する」と表現したりします。

また、集計単位のことを集計キー、集約キーと呼ぶ場合があります。「クラスを集約キーとして平均点を求める」のように表現する場合があります。

 

2−2.AVGの具体例

目ごとの平均の体長を求めるSQL文は次の通りになります。


SELECT 目ID, AVG(体長) FROM T_動物マスタ GROUP BY 目ID
 

このSQL文の実行例は次の通りです。

目ID Expr1
1 73
2 22
4 25

結果は味気ないものとなっています。目マスタを見ると目ID=1は霊長目、2は翼手目、4は食虫目となっていますから、平均的な体長を見るとなんとなく納得できるものはあります。サル類は平均73cm、コウモリ類は22cm、モグラ類は25cmという感じですね。

結果を目IDではなく目名で表示させることもできます。この手法は次回説明する予定ですが、例だけここに掲載しておきます。


SELECT 目名, AVG(体長) FROM T_動物マスタ LEFT JOIN T_目マスタ ON T_動物マスタ.目ID = T_目マスタ.目ID GROUP BY 目ID, 目名
 

このSQL文の実行例は次の通りです。

目名 Expr1
霊長目 73
翼手目 22
食虫目 25

AVG(体長)は体長の平均という意味ですが、列名が指定されていないので実行すると勝手にExpr1という列名が割り当てられます。勝手に列名を割り当てるのはクエリデザイナの機能であることに注意してください。このSQL文をプログラムに埋め込んだり、ほかのツールで実行するときには体長の平均は名前のない列になるか適当な名前が割り当てられます。

明示的に列に名前を指定するには前回紹介したASを使用します。次の例では結果のデータは同じですが、体長の平均の列名が「平均体長」になります。


SELECT 目ID, AVG(体長) AS 平均体長 FROM T_動物マスタ GROUP BY 目ID
 

 

目ごとではなく、生息地ごとの平均体長を調べるには次のようにします。生息地と動物の大きさには関係があるでしょうか?


SELECT 生息地ID, AVG(体長) AS 平均体長 FROM T_動物マスタ GROUP BY 生息地ID
 

結果は次のようになります。

生息地ID 平均体長
1 115.94117647058823
2 66.25
3 66.333333333333329

基となるデータが100件しかない上に、登録されている体長も私が打ち込んだものですのでこのデータは正確とは言えません。でも、データを充実させればこのようなさまざまな分析をSQLによって行うことができるのです。

なお、T_生息地マスタを見るとわかるのですが生息地ID=1は全北区、つまり北半球全域。2は主にヨーロッパ、3は主にアジアの北半球側を指しています。細かいデータはT_生息地マスタを直接開いて確認してください。

 

さて、次は目・生息地ごとの平均体長を調べてみます。今度は集計対象の列が2つになるのでGROUP BY句にも2つの列を指定します。


SELECT 目ID, 生息地ID, AVG(体長) AS 平均体長 FROM T_動物マスタ GROUP BY 目ID, 生息地ID
 

この実行例は次の通りです。

目ID 生息地ID 平均体長
1 3 55
1 5 77.5
2 3 22
 

 

2−3.GROUP BY句とSELECT句の関係

ところで、次のSQLを見てみてください。実行する前にどのような結果になるか想像してみてください。


SELECT 目ID, 名前, AVG(体長) AS 平均体長 FROM T_動物マスタ GROUP BY 目ID
 

 

このSQL文は目IDの他に動物名も実行結果に表示することを意図したものですが、実はうまく動かないでエラーになります。このSQL文では動物名の処理方法が指定されていないからです。

どういうことかというと、仮に実行できた場合の結果を考えてみましょう。次のようになるはずです。

目ID 名前 平均体長
1 73
2 22
4 25

ここでは名前欄を?にしてみました。?には何が入るでしょうか。T_動物マスタを開いて目IDが1である動物(つまり霊長目)を見つけてみましょう。Animals.mdbの初期状態ではアイアイ、ニホンザル、ミドリザル、マンドリル、ゴリラの5レコードが該当します。ところがこの5つのうちどれを名前欄に表示すべきかSQL文では指定されていないので取得するデータを特定できずにエラーになるのです。

仮に目ID=1のレコードが1つしかなくてもエラーになります。目ID=1のデータが1つしかないというのはデータを見てはじめてわかることであって、SQL文だけ見た場合にはわからないからです。

ですから、GROUP BYを使うときにはSELECT句で指定する列は何でもよいというわけではありません。

GROUP BYを使うときにSELECT句で指定できる列はGROUP BYで指定されている列か、AVGなどの集計関数を使用している列ということになります。

 

ということで、実行結果に名前を含めたければGROUP BY句に「名前」を含めて次のようなSQL文を記述できますが、このSQLの実行結果には意味はありません。


SELECT 目ID, 名前, AVG(体長) AS 平均体長 FROM T_動物マスタ GROUP BY 目ID, 名前
 

実行例は次の通りです。

目ID 名前 平均体長
1 アイアイ 40
1 ゴリラ 150
1 ニホンザル 55

見ればわかるのですが、これでは結局動物ごとの体長を取得しているだけで事実上何の集計も行っていません。ですので意味がありません。

 

仮に目ごとの参考値としてどれでもいいので動物の名前をひとつサンプルに取得したいという場合でしたら、集計関数のMAXMINを使用するのが簡単です。MAXMINは最大値と最小値を取得する集計関数です。文字列について使用すると辞書順での一番後ろに並ぶ値と、一番前に並ぶ値を意味します。

次のSQL文は意味があります。


SELECT 目ID, Min(名前) AS 動物の例, AVG(体長) AS 平均体長 FROM T_動物マスタ GROUP BY 目ID
 

実行結果は次の通りです。

目ID 動物の例 平均体長
1 アイアイ 73
2 ウオクイコウモリ 22
4 ジャコウネズミ 25

この例のように複数の集計関数をひとつのSQLの中で使用することができます。

 

2−4.その他の集計関数

他の集計関数の使用例も簡単に紹介しておきます。

次のSQL文は目ごとに最も大きな体長を取得します。


SELECT 目ID, MAX(体長) FROM T_動物マスタ GROUP BY 目ID
 

 

次のSQL文は目ごとの体長の合計を取得します。体長の合計などには何の意味もないのですが、SUM関数の使用例として参考のために書いておきます。


SELECT 目ID, SUM(体長) FROM T_動物マスタ GROUP BY 目ID
 

 

なお、集計関数はGROUP BY句がなくても使用することができます。この場合はFROM句で指定しているもの全体を1つのグループとして集計を実行し、必然的に結果は常に1レコードになります。

次のSQL文は登録されている動物の名前の数と、平均体長を取得します。


SELECT COUNT(名前) AS 登録数, AVG(体長) AS 平均体長 FROM T_動物マスタ
 

 

3.COUNT関数

 

3−1.COUNT関数の基本

集計関数のCOUNTはデータの個数を数えます。次のSQL文は目IDごとに登録されている動物の個数を取得します。


SELECT 目ID, COUNT(1) AS 登録動物数 FROM T_動物マスタ GROUP BY 目ID
 

以下はその実行例です。

目ID 登録動物数
1 5
2 2
4 2

このSQL文をよく見てみると、COUNT関数の引数が1になっています。このことについて取り上げてみます。

 

3−2.COUNT(1)と言う書き方

先ほど説明したAVG関数の場合は引数には「体長」のような列名を指定していました。COUNT関数でも列名を指定することもできます。たとえば、次のSQL文は有効です。実行結果は上で紹介しているのと同じになります。


SELECT 目ID, COUNT(名前) AS 登録動物数 FROM T_動物マスタ GROUP BY 目ID
 

それでは、COUNT(1)とはどういうことでしょうか?このことを考えるためにGROUP BYなしで実行結果を考えてみましょう。

次のSQL文を見てください。


SELECT 目ID, 1, 名前 FROM T_動物マスタ
 

実行例は次のようになります。

目ID Expr1 名前
53 1 イモリ
53 1 サンショウウオ
52 1 トノサマガエル

この結果を見ればわかるように、「名前」の数を数えても、「1」の数を数えても個数は同じなのです。それでCOUNT関数を使用する場合には、よくCOUNT(1)と記述します。この方が特定の項目とは関係なくレコードの数を取得したいという意味がはっきりしますし、項目を取得してから数を数えるより若干実行速度の向上を期待できるからです。

メモ  -  COUNT(0), COUNT(2)では?

COUNT(1)の代わりにCOUNT(0)COUNT(2)としても同じです。合計を計算しているのではなく個数を数えているだけなので結果は変わりません。しかし、COUNT(2)などとすると何か特別な意味があるのかと迷ってしまうことがありそうなので、無難にCOUNT(1)としておくのが暗黙の了解になっています。

 

3−3.NULLと集計関数

COUNT(列名)COUNT(1)では結果が異なる場合もあります。これは重要なので覚えておく必要があります。COUNT(列名)と記述した場合は、列の値がNULLのものは無視されるのです。

NULLとは値が設定されていない状態を示します。

具体的にこのことを確認してみましょう。次のSQL文を実行してみてください。


SELECT 目ID, COUNT(1) AS 個数A, COUNT(体長) AS 個数B FROM T_動物マスタ GROUP BY 目ID
 

このSQL文では「1」の個数、つまりレコードの数と体長の数を並べて取得します。Animals.mdbの初期状態では実行結果は次のようになります。

目ID 個数A 個数B
1 5 5
2 2 1
4 2 2

みるとわかるように目ID=2のとき個数Aと個数Bの値が異なります。T_動物マスタを開いて目ID=2の動物の体長を確認してみましょう。

目ID=2の動物の体長は次のように登録されています。

動物ID 名前 目ID 体長
20 ウオクイコウモリ 2 NULL
21 エラブオオコウモリ 2 22

目ID=2の動物はウオクイコウモリとエラブオオコウモリだけですが、ウオクイコウモリの体長がNULLになっています。これは私が各動物の体長を調べたときにウオクイコウモリの体長がどうしてもわからなかったので未登録になっているためです。

ですから、目ID=2のレコードは2件ありますが、体長が入力されているレコードは1件しかありません。これがCOUNT(1)COUNT(体長)の違いです。なお、このケースではCOUNT(名前)は2になります。あくまで体長がNULLなだけで名前はNULLではないからです。

このNULLのデータを無視するという仕様はCOUNT関数に限った話ではなくAVGSUMなど他の集計関数でも同様です。ただし、他の集計関数を使用している場合にはこのことはあまり気にならないのですが、COUNTの場合は気になります。

 

4.複合的な使用方法

 

4−1.GROUP BY句での関数の使用

GROUP BYや集計関数は列名や固定値を対象にできるだけではなく、式を対象に使用することもできます。

たとえば、次の例では五十音別に登録されている動物の数を取得します。「ア」からはじまる動物は何件、「イ」からはじまる動物は何件といった具合です。


SELECT LEFT(名前, 1), COUNT(1) FROM T_動物マスタ GROUP BY LEFT(名前, 1)
 

実行結果は次のようになります。

Expr1 Expr2
9
8
2
3

実行結果が正しいかどうか確かめてみましょう。

「エ」からはじまる動物は本当に3件でしょうか?次のSQL文を使って「エ」からはじまる動物の一覧を取得してください。


SELECT * FROM T_動物マスタ WHERE LEFT(名前, 1) = 'エ'
 

結果としてエリマキトカゲ、エラブウミヘビ、エラブオオコウモリが表示されるはずです。たしかに「エ」からはじまる動物の件数は3件ですね。

 

4−2.WHERE句やORDER BY句とともにGROUP BY句を使用する

また、WHERE句やORDER BY句とともにGROUP BY句を使用することもできます。この場合WHERE GROUP BYORDER BYの順に記述する必要があります。

SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ...

 

次のSQL文は食肉目(目ID=6)の生息地別登録数を取得します。データは登録数の多い順に並びます。


SELECT 生息地ID, COUNT(1) AS 登録数 FROM T_動物マスタ WHERE 目ID = 6 GROUP BY 生息地ID ORDER BY COUNT(1) DESC
 

結果は次の通りです。

生息地ID 登録数
1 6
6 3
5 3

食肉目では全北区(北半球全体)が6レコードで最も多く、次いで東洋亜区(南・東南アジア)と旧熱帯区(サハラ以南のアフリカ)の3レコードが続いていることがわかります。

この他にもTOPなどと組み合わせることもできます。要するにGROUP BYを使用しているからと言って他のキーワードが使用できなくなるような制限はありません。

 

5.よくある間違い

 

5−1.2つの集計関数を使用する場合のよくある間違い

一通り説明も済んだところでよくある間違いをいくつか指摘しておきます。

まずは、目ごとに最も大きい動物とその名前を取得するSQL文を考えてみます。

次のSQL文ではどうでしょうか?


SELECT 目ID, 名前, MAX(体長) FROM T_動物マスタ GROUP BY 目ID, 名前
 

このSQLではうまくいきません。GROUP BY句に名前が加えられているので結局目ID単位ではなく動物ごとのデータを取得できるにすぎません。

それでは、次のSQL文はどうでしょうか?


SELECT 目ID, MAX(名前), MAX(体長) FROM T_動物マスタ GROUP BY 目ID
 

このSQL文はぱっと見た感じではうまくいきそうに思えるかもしれませんが問題があります。実行結果を見てみてください。

目ID Expr1 Expr2
1 ミドリザル 150
2 エラブオオコウモリ 22
4 ポタモガーレ 35

この実行結果によると登録されている動物の中で目ID=1、つまり霊長目で最大のものはミドリザルで体長は150cmであるということになります。T_動物マスタを開いてこのことを確認してみてください。ミドリザルの体長はいくつでしょうか?なんと、40になっています。体長が150なのはゴリラです。

どうしてこのような間違いがおこるのでしょうか?目ID=1のレコードだけ見て考えてみましょう。

動物ID 名前 目ID 体長
22 アイアイ 1 40
23 ニホンザル 1 55
24 ミドリザル 1 40
25 マンドリル 1 80
26 ゴリラ 1 150

SQL文ではGROUP BY 目IDと指定していますから、目ID=1の集計はこの5レコードを基に行われます。そして、SQL文のSELECT句には目IDとMAX(名前)MAX(体長)が指定されています。それぞれの項目を単体で考えると取得する結果は次のようになることがすぐにわかります。

目ID 1
MAX(名前) ミドリザル
MAX(体長) 150

目IDは1。名前で辞書順に並べて最後になるものは「ミドリザル」。体長で最大のものは150。間違いありません。SQLはちゃんと命令されたと通りに値を取得しているのです。しかし、結果としてそれは私たちが求めたものではありません。SQL文の方が間違っているということになります。MAX(名前)ではだめなのです。

これはある程度SQL文に慣れてきたプログラマがしばしば犯す間違いです。

この場合「ミドリザル」ではなく「ゴリラ」を取得するのは実のところ簡単ではありません。説明は別の機会にしますが結論だけ書いておくと次のSQL文になります。

SELECT T_動物マスタ.目ID, T_動物マスタ.名前, T_動物マスタ.体長
FROM T_動物マスタ
    INNER JOIN (
                SELECT 目ID, MAX(体長) AS 体長 FROM T_動物マスタ GROUP BY 目ID
               ) AS SUB_最大値
    ON T_動物マスタ.目ID = SUB_最大値.目ID AND T_動物マスタ.体長 = SUB_最大値.体長
ORDER BY T_動物マスタ.目ID

 

5−2.HAVING - GROUP BY後の条件指定

今度は一定数以上登録されている目を抽出してみましょう。目ごとに登録数を調べて3レコード以上登録されている目とその登録数の一覧を取得してみます。

慣れていない方は次のようなSQL文を考えてしまうかもしれませんね。このSQL文は間違いです。


SELECT 目ID, COUNT(1) AS 登録数 FROM T_動物マスタ WHERE COUNT(1) >= 3 GROUP BY 目ID
 

このSQL文は実行することすらできません。エラーなのです。原因はWHERE句でCOUNTを使用している点にあります。WHERE句では集計関数を使用できないのです。この理由を考えてみましょう。

WHEREGROUP BYの両方が指定されている場合、データベースエンジンはWHEREを先に解釈します。WHEREは前回も書いたように対象となるレコードを絞り込むのに使用するのですからGROUP BYより先に実行するのは当然です。まず、何を対象にするのか選別してから、対象のレコードをグループ化するというわけです。集計した後で個々のレコードの条件を考えて条件に合致しないレコードを省くなどという処理は考えられないでしょう。

一方集計関数はGROUP BYで指定された集計条件に応じて作用します。ですから、WHERE句の中で集計関数を使用することは意味的な矛盾があるのです。

ではどうすればよいでしょうか?

GROUP BYの後で必要なレコードをしぼりこむためにHAVINGというキーワードが用意されており、これを利用します。次のようになります。


SELECT 目ID, COUNT(1) AS 登録数 FROM T_動物マスタ GROUP BY 目ID HAVING COUNT(1) >= 3
 

 

6.集計関数のないGROUP BY

 

6−1.集計関数のないGROUP BY

最後に集計関数を伴わないGROUP BYについて考えてみます。GROUP BY自体はレコードをグループ化する機能があるだけで必ずしも集計関数と一緒に使用する必要はありません。

たとえば、T_生息地マスタを見ると生息地IDが1〜12まで登録されています。T_動物マスタに登録されている生息地IDには1〜12のすべてがそろっているでしょうか?SQL文で調べてみましょう。

次のSQL文を使用するとT_動物マスタに登録されている生息地IDを小さい順に取得できます。ですから、どのような生息地が登録されているか、またはされていないか調べるのに便利です。


SELECT 生息地ID FROM T_動物マスタ ORDER BY 生息地ID
 

しかし、実行してみればわかるのですが、1、1、1、1、…、2、2、2、2、…のように同じ値がたくさん並びます。この場合、何が登録されているかを見たいのであって、いくつ登録されているかはどうでもよいのですからGROUP BYを使用してグループ化してしまうともっと見やすくなります。次の通りです。


SELECT 生息地ID FROM T_動物マスタ GROUP BY 生息地ID ORDER BY 生息地ID
 

これを実行するとT_動物マスタには1〜12のすべての生息地が登録されていることが一瞬で確認できます。

 

6−2.DISTINCT

ところで、実はもっと便利な方法があります。キーワードDISTINCTを使用すると同じ値を省く効果があります。

次のSQL文を使うとGROUP BYを使用するより簡単にT_動物マスタに登録されている生息地IDの一覧を取得することができます。


SELECT DISTINCT 生息地ID FROM T_動物マスタ ORDER BY 生息地ID
 

DISTINCTはあくまでも同じ値を省くだけなので集計関数を使用するときのグループ化の条件を指定するGROUP BYのような役割はありません。でも、集計する必要がないのにGROUP BYを使っているのならばDISTINCTに置き換えた方がすっきりする場合が多いです。