【SQL】集計とグループ化 まとめ
はじめに
『スッキリわかるSQL入門 第2版』を読んで、問題に取り組みつつ、メモとしてまとめました。
なお、一部SQL文の例などを引用させていただいています。
SQLの処理順
SQL の処理順は書き順と違っていて、GROUP BY は以下の順序で動きます。
1. FROM（テーブル決定）
2. WHERE（行をフィルタ）
3. GROUP BY（残った行をグループ化）
4. HAVING（グループ単位の条件でフィルタ）
5. SELECT（列や計算結果を選択）
6. ORDER BY（並び替え）
データの集計
集計関数
SELECT SUM(出金額) AS 出金額合計
FROM 家計簿
集計関数を用いたSQLが処理される流れ
① 検索
- 選択列リストによる列の絞り込み
- WHERE句による行の絞り込み
② 集計
- 指定された列を集計する
集計関数の特徴
普通の関数：各行に対して実行される（1行ずつ処理を繰り返す）
LENGTHやCOALESCEなどの関数は、検索結果の各行に対して、同じ処理や計算を行なうように命令するもの
検索結果の行が増えたり減ったりするものではない
集計関数：まとめて1回実行される
検索結果のある列に対して行われる
- 検索結果の全行をまとめて扱い、1回だけ集計処理を行なう
- 結果表は必ず1行になる
集計関数の使い方
代表的な集計関数
集計
| 関数名 | 説明 |
|---|---|
| SUM | 各行の値の合計を求める |
| MAX | 各行の値の最大値を求める |
| MIN | 各行の値の最小値を求める |
| AVG | 各行の値の平均値を求める |
計数
COUNT : 行数をカウントする
SUM(列)
MAX(列)
MIN(列)
AVG(列)
引数として1つの列名を渡す
列名だけでなく、列名を含む式も指定可
SELECT
SUM(出金額) AS 合計出金額,
AVG(出金額) AS 平均出金額,
MAX(出金額) AS 最も大きな出費,
MIN(出金額) AS 最も少ない出費
FROM 家計簿
検索結果の行数を求める
-- 検索結果の行数（NULLの行もカウントする）
COUNT(*)
-- 検索結果の指定列に関する行数（NULLはカウントしない）
COUNT(列)
COUNT関数は該当した行数を取得する関数
→ 検索結果の値自体が何であるかは問わない
SELECT COUNT(*) AS 食費の行数
FROM 家計簿
WHERE 費目 = '食費'
SELECT COUNT(DISTINCT 費目)
FROM 家計簿
DISTINCTを指定してすることで、その列で重複している値を除いた状態で集計できる
集計に関する4つの注意点
SELECT文でしか利用できない
SELECT文の選択列リスト・ORDER BY 句・HAVING 句の中で利用する
集計関数は検索結果に対して行なうための道具
→ UPDATE文・INSERT文・DELETE文では利用できない
検索結果がデコボコになってはならない
SELECT 日付, SUM(出金額) AS 出金額計
FROM 家計簿
- 日付の列：複数行になる
- 出金額計の列：1行になる
上記のクエリはデータベースで結果表として認められない。
結果表は、常に列ごとの行数に一致する長方形型にならないといけない。
引数に許される型が異なる
SUM関数とAVG関数は、それぞれ文字列型と日付型は利用できない
文字列に対してMAX関数やMIN関数を使った場合、DBMSが定めれる照合順序（文字コード順、アルファベット順など）で並び替え、その最初や最後となる文字列が結果として得られる
NULLの取り扱い
NULLを0に読み替えて集計したい場合はCOALESCE関数を使用する
SELECT AVG(COALESCE(出金額, 0)) AS 出金額の平均
FROM 家計簿
データをグループに分ける
グループ別の集計
グループ化
グループ化：集計に先立って、指定した基準で検索結果をいくつかのまとまりにわける機能
SELECT グループ化の基準列名..., 集計関数
FROM テーブル名
(WHERE 絞り込み条件)
GROUP BY グループ化の基準列名...
SELECT 費目, SUM(出金額) AS 費目別の出金額合計
FROM 家計簿
GROUP BY 費目
グループ集計の流れ
1. 検索（WHERE句による行の絞り込み）
元の表に対してWHERE句による通常の検索処理が行われ、行が絞り込まれる
2. グループ化（グループごとに検索結果を分類）
検索結果はGROUP BY句で指定された列に同じ値を持つ行ごとに分類される
3. 集計・列選択（各グループを集計し、SELECT句によって列の絞り込み）
各グループに対して集計関数の処理が行われた後、SELECT句の選択列リストによって列が絞り込まれ、結果表になる
最終的な結果表の行数は、グループの数と等しくなる。
ポイント
- グループ化するには、GROUP BY 句に基準となる列を指定する
- 集計関数は、データの値をグループごとにまとめて計算する
- 集計関数の結果表の行数は、必ずグループの数と一致する
複数の列によるグループ化
GROUP BY 句に複数の列をカンマで区切って指定することで、複数の列を基準にしたグループ化をすることもできる。
複数の列によるグループ化は、それらの列を組み合わせて、値が同じになるものを収集してグループが作られる。
SELECT department, job_title, COUNT(*)
FROM employees
GROUP BY department, job_title;
× department ごとに集計して、さらに job_title ごとに集計… という二段階ではない
〇 (department, job_title) のペアごとに1グループ
→ これらの 組み合わせごとに COUNT が計算される
グループ集計後の絞り込み
WHERE句を処理する段階では、まだ集計が終わっていないため、集計関数はWHERE句に利用できない
HAVING句 : 集計処理結果に対して絞り込みを行なう
SELECT グループ化の基準列名..., 集計関数
FROM テーブル名
(WHERE 元の表に対する絞り込み条件)
GROUP BY グループ化の基準列名...
HAVING 集計結果に対する絞り込み条件
WHERE句同様、ANDやORの論理演算子で複数の条件式を組み合わせることもできる。
また、WHERE句と違い、集計関数を記述することができる。
絞り込みタイミングの違い
- WHERE句：検索時に絞り込み
- HAVING句：集計後に絞り込み
SELECT 費目, SUM(出金額) AS 費目別出金額合計
FROM 家計簿
GROUP BY 費目
HAVING SUM(出金額) > 0
グループ集計と選択列リスト
グループ集計を行なうSELECT文の選択列リストに指定する列は、以下のいずれかに当てはまる必要がある。
- GROUP BYでグループ化の基準列として指定されている
- 集計関数による集計の対象となっている
このいずれかの条件を満たしていないと、長方形型にならなくなってしまう（エラー）。
集計テーブルの活用
大量のデータ集計
大量のデータの集計を毎回計算することは非効率であるため、あらかじめ集計結果をまとめたテーブルを利用することが効果的
集計テーブルの活用
集計テーブル
- あるテーブルの集計結果を格納するための別テーブル（集計テーブル）を作成する
- 集計関数を用いて集計処理を1回行ない、結果を集計テーブルにINSERTする
- 集計結果が必要な場合、すでに作った集計テーブルに格納されている計算済みの集計結果を利用する
集計テーブルを更新
集計テーブルに格納されている結果は、最新の集計より古くなってしまう可能性がある。
- 集計の再実行（集計用SELECT文）
- 集計表の更新（更新用UPDATE文）
これを手動で毎回行なうのは大変なので、自動化する方法があるが、それは別の章で。
参考図書
『スッキリわかるSQL入門 第2版』
Discussion