MySQLの集計関数とGROUP BYの詳細な解説
集計関数はデータ分析の中核をなすものであり、統計、グループ化、要約はすべてこれに基づいています。
このレッスンでは、集計関数とグループ化クエリについて詳しく解説します。
graph TB
A[Aggregate Query Process] --> B[FROM Data Sources]
B --> C[WHERE Filter Rows]
C --> D[GROUP BY Grouping]
D --> E[Aggregate Functions<br/>COUNT/SUM/AVG/MAX/MIN]
E --> F[HAVING Filter Group]
F --> G[SELECT Output]
G --> H[ORDER BY Sort]
H --> I[WITH ROLLUP Summary]
1. 学習内容
- COUNT/SUM/AVG/MAX/MIN の使用方法
- GROUP BY：グループ化された統計情報
- フィルターグループの「HAVING」
- ロールアップ集計行を含む
- 集計関数とNULLの関係
2. 報告書の統計データに隠された実話
(1) 課題：Excelはデータ分析を行うには処理が遅すぎる
オペレーションマネージャーは、各部門の従業員数、平均給与、および最高給与に関する統計をまとめる必要があります。
Excelを使用するには、複数の数式を入力する必要があり、データが更新されるたびに計算をやり直さなければなりません。
(2) 集計関数の解法
SQL
SELECT
department,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;
3. 集計関数
(1) COUNT
SQL
-- Count the total number of lines
SELECT COUNT(*) FROM employees;
-- Count non-NULL rows
SELECT COUNT(email) FROM employees;
-- Count the number of unique entries
SELECT COUNT(DISTINCT department) FROM employees;
⚠️ 注：
COUNT(*) はすべての行をカウントしますが、COUNT(col) は col が NULL ではない行のみをカウントします。
(2) SUM/AVG：合計／平均
SQL
-- Total Wages
SELECT SUM(salary) FROM employees;
-- Average Wage
SELECT AVG(salary) FROM employees;
-- Conditional Summation
SELECT SUM(amount) FROM orders WHERE status = 'paid';
(3) MAX/MIN 最大値／最小値
SQL
-- Highest Salary
SELECT MAX(salary) FROM employees;
-- Minimum Wage
SELECT MIN(salary) FROM employees;
-- Earliest/Latest Date
SELECT MIN(hire_date) AS earliest, MAX(hire_date) AS latest FROM employees;
4. GROUP BY
▶ 例：単一フィールドによるグループ化
SQL
-- By Department
SELECT
department,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
出力：
TEXT
+-------------+-----------+-----------+
| department | emp_count | avg_salary|
+-------------+-----------+-----------+
| Engineering | 15 | 8500.00 |
| Sales | 10 | 6000.00 |
| Marketing | 8 | 5500.00 |
| HR | 5 | 5000.00 |
+-------------+-----------+-----------+
▶ 例：複数フィールドによるグループ化
SQL
-- By department and job title
SELECT
department,
job_title,
COUNT(*) AS count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_salary DESC;
▶ 例：式によるグループ化
SQL
-- Orders by Year
SELECT
YEAR(order_date) AS year,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY YEAR(order_date);
-- Statistics by Age Group
SELECT
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age BETWEEN 18 AND 30 THEN '18-30'
WHEN age BETWEEN 31 AND 50 THEN '31-50'
ELSE 'Over 50'
END AS age_group,
COUNT(*) AS count
FROM users
GROUP BY age_group;
5. HAVINGによるフィルタリングとグループ化
WHERE は行を絞り込み、HAVING はグループを絞り込みます。
▶ 例：HAVING の使用
SQL
-- Find the average wage > 6000 the department
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 6000;
-- Find the number of orders > 10 's customers
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING order_count > 10;
-- WHERE + GROUP BY + HAVING Combination
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01' -- Filter the rows first
GROUP BY department -- Regroup
HAVING avg_salary > 6000; -- Final Filter Group
6. WITH ROLLUP の概要
▶ 例：ROLLUP 集計行
SQL
-- Automatically generate total rows
SELECT
department,
COUNT(*) AS emp_count,
SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;
出力：
TEXT
+-------------+-----------+--------------+
| department | emp_count | total_salary |
+-------------+-----------+--------------+
| Engineering | 15 | 127500.00 |
| Sales | 10 | 60000.00 |
| Marketing | 8 | 44000.00 |
| HR | 5 | 25000.00 |
| NULL | 38 | 256500.00 | -- Total
+-------------+-----------+--------------+
7. 集計関数とNULL
| 関数 | NULL の扱い |
|---|---|
COUNT(*) |
NULL行が含まれています |
COUNT(col) NULLを除外 |
|
SUM(col) NULLを無視 |
|
AVG(col) |
NULLを無視（分母にNULLが含まれていない） |
MAX(col) NULLを無視 |
|
MIN(col) NULLを無視 |
▶ 例：NULL値の扱い
SQL
-- COUNT(*) vs COUNT(email)
SELECT
COUNT(*) AS total_rows,
COUNT(email) AS has_email,
COUNT(*) - COUNT(email) AS no_email
FROM users;
-- AVG Ignore NULL
SELECT AVG(salary) FROM employees; -- NULL Not included in the calculation
❓ よくある質問
Q WHERE と HAVING は併用できますか？
A はい。実行順序は、WHERE（行のフィルタリング）→ GROUP BY（グループ化）→ HAVING（グループのフィルタリング）→ SELECT → ORDER BY です。
Q GROUP BY 句のフィールドは、SELECT 句にも含める必要がありますか？
A MySQL の厳格モードでは、SELECT 句に含まれる集計対象外のフィールドは、GROUP BY 句にも含める必要があります。
Q COUNT(*) と COUNT(1) のどちらが速いですか？
A どちらも同じくらい速いです。MySQL のオプティマイザが自動的に最適な方法を選択します。
Q 集計関数はネストできますか？
A 直接ネストすることはできません
AVG(SUM(...))。サブクエリを使用してください：SELECT AVG(total) FROM (SELECT SUM(amount) AS total FROM orders GROUP BY customer_id) t。📖 まとめ
- COUNT/SUM/AVG/MAX/MIN は、5つの主要な集計関数です
- GROUP BY フィールドごとにグループ化します。複数のフィールドを組み合わせることができます。
- HAVING はグループ単位でフィルタリングを行い（集計関数を使用可能）、WHERE は行単位でフィルタリングを行います（集計関数は使用不可）
- ロールアップあり 集計行を自動的に生成します
- NULL は集計関数では無視されます（COUNT(*) を除く）
📝 練習問題
-
基本問題（難易度：⭐）：各部署の従業員数と平均給与を算出してください。
-
応用問題（難易度 ⭐⭐）：注文総額が 10,000 を超える顧客を特定し、総額の多い順に並べ替えてください。
-
課題（難易度：⭐⭐⭐）：各月の注文数、合計金額、平均金額を算出し、
WITH ROLLUPを使用して年間の集計行を追加してください。
