SQLによる検索と集計

要点と定義

SQLの問合せでは、対象表をFROMで指定し、WHEREで集計前の行を絞り、GROUP BYで集計単位を決めます。SUMは合計、COUNT(*)は行数、MAXは最大値を求めます。集計後の値に条件を付けるのがHAVING、結果の表示順を指定するのがORDER BYです。たとえば「会員ごとの利用料金の合計が基準以上」を探すなら、会員番号でまとめ、料金の合計をHAVINGで判定します。最高料金だけを調べるMAXでは総額は分かりません。

GROUP BYには、結果をどの単位でまとめるかを表す列を指定します。年齢・商品分類ごとの販売額なら、販売明細ごとの「販売時単価×個数」を合計し、年齢と分類でまとめます。商品表の単価を後から変更できる場合、販売時点の価格を保持した列を使わないと過去の販売額を再現できません。また、合計販売額はグループ化して求める値なので、集計対象の列と混同してGROUP BYに加えません。

外部キーは、別表のキーを参照する列です。FOREIGN KEYとREFERENCESで参照先を定義する参照制約により、参照先に存在しないキー値を登録できないようにします。

適用条件と例

購入明細表の一行が一回の購入に対応するなら、購入回数はCOUNT(*)で数えられます。例として、2016年中のコーヒー明細を対象に、会員ごとの購入額が1万円以上、かつ明細が5行以上かを調べる場合、期間と分類は各明細に対する条件なのでWHEREに置きます。会員番号でGROUP BYし、SUM(単価 * 個数)とCOUNT(*)の条件をHAVINGで判定します。複数の明細行が一つの購入を構成する設計なら、行数は購入回数を表さないため、数える単位を確認します。

入園者ごとの精算額を求める場合は、入園券の入園者に対象を絞り、入園者番号でグループ化して、利用料金のSUMを判定します。アトラクション番号でまとめると入園者ごとの合計にならず、料金のMAXでは最も高い一回の料金しか分かりません。

MAX(日付)は、そのグループで最も新しい日付を示します。会員ごとに入園日をまとめ、最大日付が2016年1月1日より前なら、2016年以降の入園がないと判断できます。ただし、この判断には会員ごとに少なくとも一回の入園記録がある前提が必要です。記録がない会員は、入園者表との結合や集計でグループとして現れないことがあるためです。