【Oracle】HAVING句の使い方完全ガイド｜WHERE との違い・複数条件・サブクエリ・ROLLUP・パフォーマンスまで解説

-- 構文
SELECT column, aggregate_function(column)
FROM table_name
[WHERE row_condition]         -- 行レベルのフィルタ
GROUP BY column
HAVING aggregate_condition    -- グループレベルのフィルタ
[ORDER BY ...];

-- 例: 部門ごとの平均給与が 5000 以上の部門を抽出
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 5000
ORDER BY avg_salary DESC;

-- COUNT: 社員数が 5 名以上の部門
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 5;

-- SUM: 売上合計が 100 万以上の顧客
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) >= 1000000;

-- MAX: 最高給与が 10000 を超える部門
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

-- MIN: 最低給与が 3000 未満の部門
SELECT department_id, MIN(salary) AS min_salary
FROM employees
GROUP BY department_id
HAVING MIN(salary) < 3000;

-- NG: WHERE に集計関数を書くとエラー
-- SELECT department_id, COUNT(*)
-- FROM employees
-- WHERE COUNT(*) >= 5   -- ORA-00934: group function is not allowed here
-- GROUP BY department_id;

-- OK: 集計結果の条件は HAVING に書く
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 5;

-- WHERE + HAVING を併用（推奨パターン）
-- WHERE で先に行を絞り、HAVING で集計結果を絞る
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'ACTIVE'           -- 先にアクティブ社員だけに絞る
GROUP BY department_id
HAVING AVG(salary) >= 5000        -- その中で平均給与 5000 以上の部門
ORDER BY avg_salary DESC;

-- AND: 両方の条件を満たすグループ
SELECT department_id,
       COUNT(*) AS emp_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 5
   AND AVG(salary) >= 5000;

-- OR: いずれかの条件を満たすグループ
SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 10
    OR SUM(salary) >= 500000;

-- AND + OR の組み合わせ（括弧で優先順位を明示）
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id
HAVING (COUNT(*) >= 5 AND AVG(salary) >= 5000)
    OR COUNT(*) >= 20;

-- テーブル全体の合計が 100 万以上なら結果を返す
SELECT SUM(amount) AS total_amount
FROM orders
HAVING SUM(amount) >= 1000000;
-- 条件を満たさなければ 0 行が返る

-- テーブル全体のレコード数が 1000 以上か確認
SELECT COUNT(*) AS total_count
FROM employees
HAVING COUNT(*) >= 1000;

-- 全社平均より高い平均給与の部門を抽出
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees   -- 全社平均
);

-- 最も社員数が多い部門と同じ人数以上の部門を抽出
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= (
    SELECT MAX(cnt) FROM (
        SELECT COUNT(*) AS cnt FROM employees GROUP BY department_id
    )
);

-- ROLLUP で部門別 + 全体合計を生成
SELECT department_id, job_id,
       SUM(salary) AS total_salary,
       COUNT(*) AS emp_count
FROM employees
GROUP BY ROLLUP(department_id, job_id)
HAVING SUM(salary) >= 50000;   -- 合計 50000 以上のグループだけ表示

-- GROUPING 関数と組み合わせて小計行のみ抽出
SELECT department_id, job_id,
       SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id)
HAVING GROUPING(job_id) = 1     -- job_id が集約された行 = 部門小計行
ORDER BY department_id;

-- 「高給（10000以上）の社員が 3 名以上いる部門」を抽出
SELECT department_id,
       COUNT(CASE WHEN salary >= 10000 THEN 1 END) AS high_salary_count,
       COUNT(*) AS total_count
FROM employees
GROUP BY department_id
HAVING COUNT(CASE WHEN salary >= 10000 THEN 1 END) >= 3;

-- 「エラー率が 5% を超える月」を抽出
SELECT TO_CHAR(log_date, 'YYYY-MM') AS month,
       COUNT(*) AS total_requests,
       COUNT(CASE WHEN status_code >= 500 THEN 1 END) AS error_count,
       ROUND(
           COUNT(CASE WHEN status_code >= 500 THEN 1 END) * 100.0
           / COUNT(*), 2
       ) AS error_rate_pct
FROM access_log
GROUP BY TO_CHAR(log_date, 'YYYY-MM')
HAVING COUNT(CASE WHEN status_code >= 500 THEN 1 END) * 100.0
       / COUNT(*) > 5
ORDER BY month;

-- NG: WHERE で書ける条件を HAVING に書いている
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING department_id IN (10, 20, 30)   -- 集計関数ではない
   AND AVG(salary) >= 5000;

-- OK: 行条件は WHERE、集計条件は HAVING に分離
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id IN (10, 20, 30)    -- WHERE で先にフィルタ
GROUP BY department_id
HAVING AVG(salary) >= 5000;            -- 集計条件だけ HAVING に

-- email が重複している行を検出
SELECT email, COUNT(*) AS dup_count
FROM employees
GROUP BY email
HAVING COUNT(*) >= 2
ORDER BY dup_count DESC;

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       SUM(amount) AS monthly_sales
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
HAVING SUM(amount) < 1000000
ORDER BY month;

SELECT customer_id,
       MAX(order_date) AS last_order,
       TRUNC(SYSDATE - MAX(order_date)) AS days_since_last
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) < SYSDATE - 90   -- 90 日以上注文なし
ORDER BY last_order;

-- 部門平均給与が全社平均の 2 倍以上、または 0.5 倍以下の部門
SELECT department_id,
       AVG(salary) AS dept_avg,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees) * 2
    OR AVG(salary) < (SELECT AVG(salary) FROM employees) * 0.5;