Havingの活用

前回の記事でも少しふれましたが、groupbyを使った後の抽出条件などでうまく使えると効果を発揮するHavingの効果について記載していこうと思います。

HAVINGの基本概念

HAVING は GROUP BY でグループ化したデータに対して条件を指定する句です。WHERE が個々の行を絞り込むのに対し、HAVING はグループ化後の結果を絞り込みます。

簡単なサンプル

-- 部門ごとの平均給与が50万円以上の部門を抽出
SELECT 
    department,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 500000;

-- 2人以上いる部門だけを表示
SELECT 
    department,
    COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2;

WHEREとHAVINGの違い

WHERE: グループ化前に個々の行を絞り込む
HAVING: グループ化後に集計結果を絞り込む

-- WHERE: グループ化前に給与50万円以上の人だけを対象
SELECT department, COUNT(*)
FROM employees
WHERE salary >= 500000
GROUP BY department;

-- HAVING: グループ化後に平均給与50万円以上の部門を抽出
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) >= 500000;

応用例1: 全生徒が課題を提出した学部の抽出

テーブル定義

-- 学生テーブル
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

-- 課題提出テーブル
CREATE TABLE submissions (
    submission_id INT PRIMARY KEY,
    student_id INT,
    assignment_name VARCHAR(100),
    submit_date DATE  -- 提出日（NULLの場合は未提出）
);

サンプルデータ

INSERT INTO students VALUES
(1, '田中太郎', '情報学部'),
(2, '佐藤花子', '情報学部'),
(3, '鈴木一郎', '情報学部'),
(4, '山田美咲', '経済学部'),
(5, '高橋健太', '経済学部'),
(6, '伊藤涼子', '法学部'),
(7, '渡辺大輔', '法学部'),
(8, '中村真理', '法学部');

INSERT INTO submissions VALUES
(1, 1, '夏季レポート', '2024-08-20'),
(2, 2, '夏季レポート', '2024-08-22'),
(3, 3, '夏季レポート', '2024-08-19'),
(4, 4, '夏季レポート', '2024-08-21'),
(5, 5, '夏季レポート', NULL),  -- 高橋くんは未提出
(6, 6, '夏季レポート', '2024-08-18'),
(7, 7, '夏季レポート', '2024-08-23'),
(8, 8, '夏季レポート', '2024-08-17');

SQL: 全員が提出した学部を見つける

-- 学部ごとに全生徒数と提出済み生徒数を比較
SELECT 
    s.department,
    COUNT(*) AS total_students,
    COUNT(sub.submit_date) AS submitted_students
FROM 
    students s
    INNER JOIN submissions sub ON s.student_id = sub.student_id
GROUP BY 
    s.department
HAVING 
    COUNT(*) = COUNT(sub.submit_date);  -- 全員提出の条件

結果:

department	total_students	submitted_students
情報学部	3	3
法学部	3	3

ポイント:

• COUNT(*): 学部の全学生数（NULLも含む）
• COUNT(submit_date): 提出日が入力されている学生数（NULLは除外）
• この2つが一致すれば、未提出者がいないことを意味する

---

応用例2: 指定した商品をすべて取り揃えている店舗の抽出

テーブル定義

-- 商品マスタ
CREATE TABLE items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100)
);

-- 店舗テーブル
CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(100)
);

-- 在庫テーブル
CREATE TABLE inventory (
    inventory_id INT PRIMARY KEY,
    store_id INT,
    item_id INT,
    quantity INT,
    FOREIGN KEY (store_id) REFERENCES stores(store_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id)
);

サンプルデータ

INSERT INTO items VALUES
(1, 'りんご'),
(2, 'バナナ'),
(3, 'オレンジ'),
(4, 'ぶどう');

INSERT INTO stores VALUES
(1, '新宿店'),
(2, '渋谷店'),
(3, '池袋店');

INSERT INTO inventory VALUES
(1, 1, 1, 50),  -- 新宿店: りんご
(2, 1, 2, 30),  -- 新宿店: バナナ
(3, 1, 3, 40),  -- 新宿店: オレンジ
(4, 1, 4, 20),  -- 新宿店: ぶどう（全商品あり）
(5, 2, 1, 45),  -- 渋谷店: りんご
(6, 2, 2, 35),  -- 渋谷店: バナナ
(7, 2, 3, 25),  -- 渋谷店: オレンジ（ぶどうなし）
(8, 3, 1, 60),  -- 池袋店: りんご
(9, 3, 4, 15);  -- 池袋店: ぶどう（バナナとオレンジなし）

SQL: 全商品を取り揃えている店舗を見つける

-- 店舗ごとの商品数と全商品数を比較
SELECT 
    s.store_id,
    s.store_name,
    COUNT(DISTINCT i.item_id) AS items_in_stock
FROM 
    stores s
    INNER JOIN inventory inv ON s.store_id = inv.store_id
    INNER JOIN items i ON inv.item_id = i.item_id
GROUP BY 
    s.store_id, s.store_name
HAVING 
    COUNT(DISTINCT i.item_id) = (SELECT COUNT(*) FROM items);

結果:

store_id	store_name	items_in_stock
1	新宿店	4

ポイント:

• 店舗ごとの在庫商品数と、商品マスタの総数を比較
• 一致すれば全商品を取り揃えている