【SQL】AVG関数を使用して平均を求める方法｜NULL対策・GROUP BY・ウィンドウ関数まで完全解説

? この記事で学べること
・ AVG関数の基本構文と使い方
・ NULL値が平均計算に与える影響と対処法
・ DISTINCT で重複を除外した平均の求め方
・ GROUP BY でグループごとの平均を算出する方法
・ HAVING で平均値に条件を付けてフィルタする方法
・ CASE WHEN を使った条件付き平均
・ サブクエリで「平均以上」のデータを抽出する方法
・ ROUND / CAST で小数点を制御する方法
・ ウィンドウ関数 AVG() OVER で移動平均を計算する方法

SELECT AVG(列名) AS 別名
FROM テーブル名;

SELECT AVG(salary) AS avg_salary
FROM employees;

▼ 実行結果

avg_salary
----------
386000

-- NULLを除外して計算される（80 + 60 + 100）÷ 3 = 80
SELECT AVG(score) AS avg_score
FROM test_scores;

▼ 実行結果

avg_score
---------
80

-- 方法1: COALESCE（全RDBMS共通）
SELECT AVG(COALESCE(score, 0)) AS avg_score
FROM test_scores;

-- 方法2: IFNULL（MySQL専用）
SELECT AVG(IFNULL(score, 0)) AS avg_score
FROM test_scores;

-- 方法3: NVL（Oracle専用）
SELECT AVG(NVL(score, 0)) AS avg_score
FROM test_scores;

▼ 実行結果（すべて同じ）

avg_score
---------
60

注意：NULL変換の使い分け

-- データ: salary = 300000, 300000, 400000, 500000

-- ALL（デフォルト）: (300000+300000+400000+500000) ÷ 4 = 375000
SELECT AVG(ALL salary) AS avg_all
FROM employees;

-- DISTINCT: (300000+400000+500000) ÷ 3 = 400000
SELECT AVG(DISTINCT salary) AS avg_distinct
FROM employees;

? ALLとDISTINCTの違い

ALL（デフォルト）: 重複値もすべて含めて計算
DISTINCT: 重複値を除外してユニークな値のみで計算

DISTINCTは「何種類の給与水準があるか」を分析する場合などに有用です。通常の平均計算では指定不要です。

SELECT
    department,
    AVG(salary) AS avg_salary,
    COUNT(*) AS emp_count
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

▼ 実行結果

department | avg_salary | emp_count
-----------+------------+----------
開発       |     435000 |         2
営業       |     365000 |         2
総務       |     330000 |         1

SELECT
    department,
    position,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, position
ORDER BY department, avg_salary DESC;

SELECT
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 400000;

? WHERE と HAVING の違い

-- 平均以上のデータを取得
SELECT name, department, salary
FROM employees
WHERE salary >= (
    SELECT AVG(salary) FROM employees
);

▼ 実行結果（平均 386000 以上）

name   | department | salary
-------+------------+--------
佐藤   | 開発       | 420000
高橋   | 開発       | 450000

SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

SELECT
    student_name,
    AVG(CASE WHEN subject IN ('国語', '英語', '社会')
         THEN score END
    ) AS liberal_arts_avg,
    AVG(CASE WHEN subject IN ('数学', '理科')
         THEN score END
    ) AS science_avg
FROM exam_results
GROUP BY student_name;

-- 小数点以下2桁に四捨五入
SELECT ROUND(AVG(salary), 2) AS avg_salary
FROM employees;

-- 小数点以下を切り捨てて整数にする
SELECT ROUND(AVG(salary), 0) AS avg_salary
FROM employees;

-- SQL Server: INT列のAVGは整数になるためCASTが必要
SELECT AVG(CAST(score AS DECIMAL(5,2))) AS avg_score
FROM test_scores;

-- ROUNDとCASTを組み合わせる
SELECT
    department,
    ROUND(AVG(CAST(salary AS DECIMAL(10,2))), 1) AS avg_salary
FROM employees
GROUP BY department;

? RDBMS別の整数AVG動作

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER () AS company_avg,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS dept_avg,
    salary - AVG(salary) OVER (
        PARTITION BY department
    ) AS diff_from_dept_avg
FROM employees;

▼ 実行結果

name   | dept   | salary | company_avg | dept_avg | diff
-------+--------+--------+-------------+----------+------
田中   | 営業   | 350000 |      386000 |   365000 | -15000
鈴木   | 営業   | 380000 |      386000 |   365000 |  15000
佐藤   | 開発   | 420000 |      386000 |   435000 | -15000
高橋   | 開発   | 450000 |      386000 |   435000 |  15000
伊藤   | 総務   | 330000 |      386000 |   330000 |      0

SELECT
    sale_date,
    daily_amount,
    ROUND(
        AVG(daily_amount) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 0
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY sale_date;

SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    MAX(salary) - MIN(salary) AS salary_range,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- ❌ 悪い例: 1対多JOINで平均がずれる
SELECT AVG(e.salary) AS avg_salary
FROM employees e
JOIN orders o ON e.id = o.employee_id;
-- → 注文が多い社員の給与が重複カウントされる

-- ✅ 良い例: サブクエリで事前に集計
SELECT AVG(e.salary) AS avg_salary
FROM employees e
WHERE e.id IN (
    SELECT DISTINCT employee_id FROM orders
);

-- MySQL
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    ROUND(AVG(amount), 0) AS avg_amount,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

SELECT
    CASE
        WHEN age < 30 THEN '20代以下'
        WHEN age < 40 THEN '30代'
        WHEN age < 50 THEN '40代'
        ELSE '50代以上'
    END AS age_group,
    ROUND(AVG(salary), 0) AS avg_salary,
    COUNT(*) AS emp_count
FROM employees
GROUP BY age_group
ORDER BY avg_salary;

SELECT
    name,
    salary,
    AVG(salary) OVER () AS avg_salary,
    ROUND(
        (salary - AVG(salary) OVER ())
        / AVG(salary) OVER () * 100,
    1) AS deviation_pct
FROM employees
ORDER BY deviation_pct DESC;

⚡ AVG関数の最適化ポイント

• WHEREで先に絞り込む: AVGの計算対象を減らすことで高速化
• インデックスを活用: GROUP BY列やWHERE条件列にインデックスがあると効率的
• 相関サブクエリを避ける: ウィンドウ関数 AVG() OVER で代替すると高速
• 不要な列を含めない: SELECT * ではなく必要な列だけを指定
• 大量データは事前集計: 日次集計テーブルを作成し、そこからAVGする

-- この2つは同じ結果を返す
SELECT AVG(salary) AS avg1
FROM employees;

SELECT SUM(salary) / COUNT(salary) AS avg2
FROM employees;
-- ※ COUNT(*) ではなく COUNT(salary) を使うこと

-- 単純平均: 各商品の単価を均等に扱う
SELECT AVG(unit_price) AS simple_avg
FROM order_details;

-- 加重平均: 販売数量で重み付け
SELECT
    SUM(unit_price * quantity) / SUM(quantity) AS weighted_avg
FROM order_details;

-- 該当データなし → NULLが返る（0ではない）
SELECT AVG(salary) FROM employees WHERE 1 = 0;
-- 結果: NULL

-- NULLを0に変換したい場合はCOALESCEで囲む
SELECT COALESCE(AVG(salary), 0) AS avg_salary
FROM employees
WHERE department = '存在しない部署';
-- 結果: 0

⚠️ 文字列カラムへのAVG適用

AVG関数は数値型の列にのみ使用してください。文字列として格納された数値にはCASTで型変換してから使います。

-- ❌ エラー: WHERE句で集計関数は使えない
SELECT * FROM employees
WHERE salary > AVG(salary);

-- ✅ サブクエリを使う
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- ✅ ウィンドウ関数を使う
SELECT * FROM (
    SELECT *, AVG(salary) OVER() AS avg_sal
    FROM employees
) t
WHERE salary > avg_sal;

-- salary: 300000, 320000, 350000, 330000, 5000000（役員）
-- 平均: 1,260,000 ← 実態とかけ離れている

-- 対策1: 外れ値を除外して平均を計算
SELECT AVG(salary) AS trimmed_avg
FROM employees
WHERE salary < (SELECT AVG(salary) * 3 FROM employees);

-- 対策2: 中央値（MEDIAN）を使う
-- PostgreSQL
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

-- Oracle
SELECT MEDIAN(salary) AS median_salary
FROM employees;

? 平均値 vs 中央値 の使い分け

AVG関数 まとめ