Visual Basic データベース講座

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

第９回　SELECT文による集計

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

１．SQLの本領

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

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

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

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

２．GROUP BY句

２−１．集計単位と集計関数

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

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

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

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

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

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

２−２．AVGの具体例

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

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

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

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

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

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

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

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

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

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

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

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

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

２−３．GROUP BY句とSELECT句の関係

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

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

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

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

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

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

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

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

実行例は次の通りです。

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

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

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

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

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

２−４．その他の集計関数

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

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

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

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

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

３．COUNT関数

３−１．COUNT関数の基本

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

以下はその実行例です。

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

３−２．COUNT(1)と言う書き方

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

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

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

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

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

３−３．NULLと集計関数

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

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

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

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

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

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

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

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

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

４．複合的な使用方法

４−１．GROUP BY句での関数の使用

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

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

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

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

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

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

４−２．WHERE句やORDER BY句とともにGROUP BY句を使用する

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

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

結果は次の通りです。

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

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

５．よくある間違い

５−１．２つの集計関数を使用する場合のよくある間違い

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

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

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

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

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

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

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

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

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

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

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

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

５−２．HAVING - GROUP BY後の条件指定

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

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

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

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

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

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

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

６．集計関数のないGROUP BY

６−１．集計関数のないGROUP BY

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

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

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

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

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

６−２．DISTINCT

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

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

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