更新日:
システム開発の現場において、大量のデータから平均や合計といったグループごとの数値を算出するケースは日常茶飯事と言えます。本章で取り上げるのは、行のまとまりごとに情報を整理して計算するアプローチ。WHERE句による絞り込みとは一味違う、データ集約の手法を学んでいきましょう。まずは基本となる関数からスタートします。
集計関数（グループ関数）
複数の行をまとめて計算し、最終的に1つの結果を導き出す仕組みを集計関数と呼びます。記述できる場所は限定されており、主にSELECT文のSELECT句、ORDER BY句、あるいは後述するHAVING句のいずれか。ここでは、代表的な5つの集計関数について特徴を確認しておきましょう。
・AVG関数：AVG( n ) ➔ nの平均値を返す。対象として指定できるのは数値のみ。
・MAX関数：MAX( expr ) ➔ exprの最大値を出力。数値だけでなく文字列や日付型も指定可能で、文字列なら辞書順の死後、日付なら最も新しい日時が選ばれる仕組み。
・MIN関数：MIN( expr ) ➔ exprの最小値を取得。数値、文字列、日付型に対応しており、文字列なら辞書順の先頭、日付なら最も古い日時を返す。
・SUM関数：SUM( n ) ➔ nの合計値を算出。AVGと同様に数値型のみ受け付ける特性を持つ。
・COUNT関数：COUNT( expr ) ➔ 条件に合致する行数をカウント。引数には特定の列名だけでなく、*（アスタリスク）をセットすることもできる。
COUNT関数に*（アスタリスク）をセットした場合は、単純にすべての行数をカウントします。対して特定の列名を指定したケースでは、その列の値がNULLである行を自動的に除外して集計する仕組み。この挙動の違いは、システム開発現場のデータ抽出において非常に大きな分かれ目となります。
① 例）社員テーブルに何件のデータがあるか取得する。
| COUNT(*) |
|---|
| 19 |
② 例）社員テーブルから電話番号を登録しているのは何件あるか取得する。
| COUNT(tel) |
|---|
| 17 |
参考サイト：MySQL 公式サイト（Aggregate Functions）
ここで、混同しがちな単一行関数との差異を整理しておきましょう。
単一行関数は各行に対して個別に計算を行うため、検索結果の行数自体が変わることはありません。しかし集計関数は、対象となったすべての行をひとまとめにして計算し、最終的に1つの答えを導き出します。
結果として、結果表は必ず1行という形に集約されるのが大きな特徴です。これに伴い、複数行取得する列と集計関数を同時にSELECT文の選択列リストに入れることは原則できないため、コードを書く際は注意を払わなければなりません。
GROUP BY句とHAVING句
SQLには、指定した特定の基準に沿って検索結果をいくつかのチームやカテゴリに分けるグループ化という機能が備わっています。たとえば部署やクラスごとの平均点を算出したいとき、あるいは拠点（大阪本社、東京支社など）ごとの売上合計を弾き出したいときなどに重宝するシステム。グループ化を用いた集計処理は、以下のような構文スタイルに則って記述します。
| expense | SUM(expenditure) | AVG(expenditure) |
|---|---|---|
| 飲食費 | 1500 | 750 |
| 交通費 | 800 | 800 |
ちなみに、GROUP BY句とHAVING句の並び順は順不可となっています。もし並び替えを行うORDER BY句を併用する場合は、必ず全体の末尾に配置しなければなりません。
またGROUP BY句に指定するのは、SELECT句に用意した列の内、集計関数を使用していない列という鉄則もあります。SELECT句で集計関数を全く使用しないのであれば、GROUP BY句は不要です。
③ 例）収支テーブルから費目別の出費合計と出費平均を取得する。
内部的なメカニズムに目を向けると、グループ集計の処理は3つのステップに沿って進行します。
1.元のテーブルに対して WHERE 句による通常検索処理が行われ、行が絞り込まれる。
2.GROUP BY 句で指定された列に同じ値を持つ行ごとに分類される。
3.各グループに対して集計関数の処理が行われた後、SELECT 句の選択列リストによって列が絞り込まれる。
この処理順序があるからこそ、大きな制約が生まれます。WHERE句が動いている段階では、システム内部でまだ合計や平均の計算が行われていません。したがって、集計関数はWHERE句に利用することはできないという絶対的なルールが確立されるわけです。もし集計処理がすべて完了した後の結果表に対して、さらに絞り込みの条件を課したい場合は、専用のHAVING句を書き足すことになります。
④ 例）収支テーブルから費目別の出費合計が1円以上のデータを取得する。
| expense | SUM(expenditure) |
|---|---|
| 飲食費 | 1500 |
| 交通費 | 800 |
参考サイト：MySQL 公式サイト（GROUP BY Handling）
最後に、システム開発現場におけるちょっとしたコラムを紹介しておきます。実はHAVING句は、パフォーマンス面への影響から、実際のシステム開発プロジェクトで使用を禁じられるケースが少なくありません。一体なぜNGとされるのでしょうか。
本番環境のデータベースには、当然のように何百万件、あるいは何千万件もの膨大なデータが蓄積されています。しかしHAVING句は、一度全てのデータをグループ集計した後の結果表に対して、もう一度全件走査を行って絞り込みをかけるという内部仕様。安易に組み込むとサーバーのメモリやCPUに大きな負荷をかけてしまうため、システム開発現場で使用するにはインデックスの設計も含めた高い技術的注意が求められます。「それなら集計する前にWHERE句で行数を極限まで減らすべきだ」という開発思想から、一律で使用を禁止するルールを敷くチームが多いのが現状です。
