集約関数
集約関数
🌍 現実世界の例え
スーパーマネージャーがレシートの山を見ている場面を想像してください：
- COUNT = 今日のレシートが何枚あるか数える（カウント）
- SUM = すべてのレシートの金額を合計する（合計）
- AVG = レシート1枚あたりの平均金額を計算する（平均）
- MAX = 最も金額の高いレシートを見つける（最大値）
- MIN = 最も金額の低いレシートを見つける（最小値）
集約関数はSQLの「電卓」であり、大量のデータから集計結果を素早く計算できます。
🎯 コアコンセプト
COUNT — カウント
COUNTは行数を数えるために使用します。一般的な2つの形式があります：
-- COUNT(*)：すべての行をカウント（NULLを含む）
SELECT COUNT(*) AS total_employees FROM employees;
-- COUNT(column_name)：そのカラムがNULLでない行をカウント
SELECT COUNT(email) AS has_email FROM employees;
| 構文 | 説明 |
|---|---|
COUNT(*) |
NULLに関係なく、すべての行をカウント |
COUNT(column_name) |
そのカラムがNULLでない行のみカウント |
COUNT(DISTINCT column_name) |
そのカラムのNULLでないユニークな値をカウント |
SUM — 合計
数値カラムの合計を計算し、NULL値は自動的に無視します。
SELECT SUM(salary) AS total_salary FROM employees;
AVG — 平均
数値カラムの平均を計算し、NULL値は自動的に無視されます（NULLは計算に含まれません）。
SELECT AVG(salary) AS avg_salary FROM employees;
MAX / MIN — 最大値 / 最小値
カラムの最大値または最小値を検索し、NULL値は自動的に無視します。
SELECT MAX(salary) AS highest, MIN(salary) AS lowest FROM employees;
集約関数とNULLの挙動
これは初心者が最も陥りやすい落とし穴です：
| 関数 | NULLの取り扱い |
|---|---|
COUNT(*) |
NULLも数える（行をカウント） |
COUNT(column_name) |
NULLを無視 |
SUM(column_name) |
NULLを無視 |
AVG(column_name) |
NULLを無視（分母からNULL行を除外） |
MAX(column_name) |
NULLを無視 |
MIN(column_name) |
NULLを無視 |
-- employeesテーブルが10行で、emailカラムに3つのNULLがある場合
SELECT COUNT(*) AS total, -- 10
COUNT(email) AS has_email -- 7
FROM employees;
AVGを計算する際、分母はNULLでない行数であり、総行数ではありません。5人の従業員のうち2人の給料がNULLの場合、AVG(salary)はNULLでない3人の給料のみで平均を計算します。
集約におけるDISTINCTの重複排除
集約関数内でDISTINCTを使用すると、集約前に重複を排除します：
-- 従業員が所属している部門はいくつありますか？
SELECT COUNT(DISTINCT department_id) AS dept_count FROM employees;
-- すべてのユニークな給料の合計（重複排除後の合計）
SELECT SUM(DISTINCT salary) AS unique_salary_sum FROM employees;
📝 基本構文
-- 基本的な集約関数の構文
SELECT aggregate_function(column_name) AS alias
FROM table_name
WHERE condition;
-- 複数の集約関数を同時に使用可能
SELECT COUNT(*) AS total_count,
SUM(column_name) AS total_sum,
AVG(column_name) AS average,
MAX(column_name) AS maximum,
MIN(column_name) AS minimum
FROM table_name;
-- 集約におけるDISTINCTの重複排除
SELECT COUNT(DISTINCT column_name) AS distinct_count
FROM table_name;
- 集約関数は通常
GROUP BYと一緒に使用します（次のレッスンで解説）が、単独でも使用できます（テーブル全体を集約） - 集約関数は
WHERE条件では直接使用できません — 代わりにHAVINGを使用してください（後述） - 集約操作では
NULLは自動的にスキップされます — これは機能でもあり落とし穴でもあるので、注意してください
📌 例
例：従業員の基本統計
SELECT COUNT(*) AS total_employees,
COUNT(email) AS has_email,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MAX(salary) AS highest_salary,
MIN(salary) AS lowest_salary
FROM employees;
出力：
total_employees has_email total_salary avg_salary highest_salary lowest_salary
--------------- --------- ----------- ---------- -------------- -------------
8 7 112000.00 14000.00 20000.00 9000.00
例：条件付き集約 + DISTINCT
-- 技術部門の従業員数は？ユニークな給料はいくつ？
SELECT COUNT(*) AS tech_dept_count,
COUNT(DISTINCT salary) AS distinct_salary_count,
AVG(salary) AS tech_dept_avg_salary
FROM employees
WHERE department_id = 1;
出力：
tech_dept_count distinct_salary_count tech_dept_avg_salary
--------------- --------------------- --------------------
3 3 17666.67
WHEREがまず技術部門の従業員をフィルタリングし、その後フィルタリングされた結果に対して集約が実行されます。
例：複数の集約関数 + 条件付きフィルタリング
-- 注文金額の統計：完了した注文のみ
SELECT COUNT(*) AS order_count,
SUM(total_amount) AS total_amount,
AVG(total_amount) AS avg_order_amount,
MAX(total_amount) AS largest_order,
MIN(total_amount) AS smallest_order
FROM orders
WHERE status = 'completed';
出力：
order_count total_amount avg_order_amount largest_order smallest_order
----------- ----------- ---------------- ------------- --------------
5 42500.00 8500.00 15000.00 2800.00
WHEREがまず完了した注文をフィルタリングし、それらの注文に対して集約計算を実行します。WHEREは集約前に実行されることに注意してください。
🎬 実践シナリオ
シナリオ1：人事レポート — 部門別給与概要
人事部門は、会社全体の給与状況と連絡先を持つ従業員の数を把握するレポートが必要です。
SELECT
COUNT(*) AS total_headcount,
COUNT(email) + COUNT(phone) AS total_contacts,
SUM(salary) AS annual_salary_expense,
AVG(salary) AS avg_monthly_salary,
MAX(salary) - MIN(salary) AS max_salary_gap
FROM employees
WHERE hire_date >= '2024-01-01';
WHEREで2024年以降に採用された従業員をフィルタリングし、複数の集約関数で異なる側面からデータを要約します。
シナリオ2：売上分析 — 注文金額の分布
発送済みのすべての注文の金額分布を分析します。
SELECT
COUNT(*) AS shipped_order_count,
COUNT(DISTINCT customer_id) AS unique_customer_count,
SUM(total_amount) AS total_sales,
AVG(total_amount) AS avg_order_value,
MAX(total_amount) AS largest_order,
MIN(total_amount) AS smallest_order
FROM orders
WHERE status = 'shipped';
COUNT(DISTINCT customer_id)は、注文数だけでなく、注文を行ったユニークな顧客数を把握するのに役立ちます。
❓ よくある質問
質問：COUNT(*)とCOUNT(1)に違いはありますか？ 回答： 最新のデータベースでは実質的な違いはなく、オプティマイザは同じ実行計画として扱います。
COUNT(*)がより標準的な構文なので、それを推奨します。
質問：AVGはNULLをどう処理しますか？ 回答：
AVGはNULL値を無視します。5人の従業員のうち2人の給料がNULLの場合、AVG(salary)はNULLでない3人の給料のみで計算し、5では割りません。NULLを0として扱いたい場合は、AVG(COALESCE(salary, 0))を使用してください。
質問：集約関数をネストできますか？ 回答： 直接はできません。例えば、
MAX(AVG(salary))はエラーになります。「平均給与が最も高い部門」を見つけるには、GROUP BY+ORDER BY AVG(salary) DESC LIMIT 1を使用するか、サブクエリを使用してください。
質問：空のテーブルに対してSUMは何を返しますか？ 回答：
SUM、AVG、MAX、MINは空のテーブルまたはすべての値がNULLの場合にNULLを返します（0ではありません）。COUNTは0を返します。代わりに0を返したい場合は、COALESCE(SUM(column_name), 0)を使用してください。
📖 まとめ
| 関数 | 用途 | NULLの取り扱い | DISTINCTとの併用 |
|---|---|---|---|
| COUNT(*) | すべての行をカウント | 数える | N/A |
| COUNT(col) | NULLでない行をカウント | 無視 | ✅ |
| SUM(col) | 合計 | 無視 | ✅ |
| AVG(col) | 平均 | 無視（分母から除外） | ✅ |
| MAX(col) | 最大値 | 無視 | ✅ |
| MIN(col) | 最小値 | 無視 | ✅ |
- 集約関数はSQLの「統計ツール」であり、複数の行を単一の集計値に圧縮します
- 集約関数は
COUNT(*)を除き、NULLを自動的に無視します DISTINCTで集約前に重複を排除できます- 集約関数は
WHEREでは使用できません — 代わりにHAVINGを使用してください（次のレッスンで解説）
📝 演習
演習1（⭐）：employeesテーブルをクエリし、総従業員数、最高給与、最低給与、平均給与を求めてください。
演習2（⭐⭐）：ordersテーブルをクエリし、2025年の完了注文数、合計金額、平均金額を求めてください。要件：status = 'completed'の注文のみカウントしてください。
演習3（⭐⭐⭐）：employeesテーブルをクエリし、給与帯ごとの従業員数をカウントしてください。ルール：給与 >= 15000は「高」、>= 10000は「中」、それ以外は「初級」。ヒント：CASE WHENと集約関数を組み合わせて使用してください。
次のレッスン
👉 14-group-by - グループ化クエリ：GROUP BYによるグループ化クエリを学び、カテゴリ別の統計、HAVINGによるグループフィルタリングなどをマスターしましょう！
