はじめに
データベースからデータを集計する際、グループごとの集計結果（GROUP BY）に対してさらに条件で絞り込みたい（HAVING）というシーンは頻繁に登場します。
(例えば、レビューの平均点が高い店舗を取得したいが、レビュー数が1件だけの店舗が上位に表示されてしまうとデータとしての信頼性に欠けるといったケース）
WHERE句は集計前の行単位のフィルタリングしかできませんが、HAVING句を組み合わせることで、
集計結果に基づいたフィルタリングが可能になります。
今回は、GROUP BY による集計処理と、HAVING による集計後の条件指定を組み合わせた、
データ抽出の方法を説明していきます。
よろしくお願いいたします!!
テーブル作成とインポート
-- スキーマ作成（存在しない場合）
CREATE SCHEMA IF NOT EXISTS food;
-- カテゴリ（メニュー分類）
CREATE TABLE food.categories (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
-- カフェ情報
CREATE TABLE food.cafes (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
location TEXT,
opening_date DATE
);
-- メニューアイテム
CREATE TABLE food.menu_items (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
category_id INT REFERENCES food.categories(id),
price INT,
calories INT
);
-- カフェとメニューの中間テーブル（多対多）
CREATE TABLE food.cafe_menu (
cafe_id INT REFERENCES food.cafes(id),
menu_item_id INT REFERENCES food.menu_items(id),
PRIMARY KEY (cafe_id, menu_item_id)
);
-- レビュー
CREATE TABLE food.reviews (
id SERIAL PRIMARY KEY,
cafe_id INT REFERENCES food.cafes(id),
rating INT CHECK (rating BETWEEN 1 AND 5),
review_date DATE
);
-- カテゴリ
INSERT INTO food.categories (name) VALUES
('ドリンク'), ('スイーツ'), ('軽食');
-- メニューアイテム（15種類）
INSERT INTO food.menu_items (name, category_id, price, calories) VALUES
('カフェラテ', 1, 450, 120),
('エスプレッソ', 1, 350, 30),
('カプチーノ', 1, 470, 110),
('アイスコーヒー', 1, 400, 5),
('抹茶ラテ', 1, 480, 140),
('チーズケーキ', 2, 500, 320),
('チョコブラウニー', 2, 480, 400),
('モンブラン', 2, 520, 370),
('いちごタルト', 2, 550, 360),
('アップルパイ', 2, 510, 390),
('ホットサンド', 3, 600, 450),
('クロックムッシュ', 3, 620, 500),
('フレンチトースト', 3, 650, 550),
('ベーグルサンド', 3, 580, 430),
('トーストセット', 3, 500, 420);
-- カフェ（6店舗）
INSERT INTO food.cafes (name, location, opening_date) VALUES
('Cafe Avenir', 'Tokyo', '2020-05-01'),
('Morning Glory Cafe', 'Osaka', '2022-03-15'),
('Komorebi Coffee', 'Kyoto', '2021-09-10'),
('Cafe Lumiere', 'Fukuoka', '2023-01-20'),
('Hoshikuzu Cafe', 'Sapporo', '2021-12-05'),
('Noir et Blanc', 'Nagoya', '2019-11-23');
-- カフェとメニューの関連（中間テーブル）
INSERT INTO food.cafe_menu (cafe_id, menu_item_id) VALUES
-- Cafe Avenir
(1, 1), (1, 6), (1, 11), (1, 7), (1, 13),
-- Morning Glory Cafe
(2, 2), (2, 7), (2, 10), (2, 12),
-- Komorebi Coffee
(3, 3), (3, 8), (3, 14), (3, 5),
-- Cafe Lumiere
(4, 4), (4, 9), (4, 13), (4, 15),
-- Hoshikuzu Cafe
(5, 1), (5, 6), (5, 9), (5, 11), (5, 12),
-- Noir et Blanc
(6, 2), (6, 8), (6, 10), (6, 14), (6, 15);
-- レビュー（20件）
INSERT INTO food.reviews (cafe_id, rating, review_date) VALUES
(1, 5, '2024-01-10'),
(1, 4, '2024-02-14'),
(1, 3, '2024-03-01'),
(1, 5, '2024-04-10'),
(2, 4, '2024-02-20'),
(2, 3, '2024-03-05'),
(2, 5, '2024-05-01'),
(3, 4, '2024-03-22'),
(3, 5, '2024-04-01'),
(3, 5, '2024-04-15'),
(4, 3, '2024-02-10'),
(4, 4, '2024-03-12'),
(4, 5, '2024-04-20'),
(5, 4, '2024-03-30'),
(5, 5, '2024-04-05'),
(5, 4, '2024-05-03'),
(6, 3, '2024-01-25'),
(6, 4, '2024-03-18'),
(6, 5, '2024-05-10'),
(6, 2, '2024-05-15');やりたいこと
各カフェの平均評価（AVG）
レビュー数（COUNT）が3件以上のカフェに限定
評価の高い順にソート（ORDER BY）
使用する構文
| SQL | 役割 |
|---|---|
GROUP BY | カフェ単位で集計する |
COUNT() | レビュー件数を数える |
AVG() | 評価の平均を出す |
HAVING | 条件（3件以上）で絞る |
ORDER BY | 評価の高い順に並べる |
クエリ
SELECT
cf.name AS cafe_name,
COUNT(r.id) AS review_count,
ROUND(AVG(r.rating), 2) AS avg_rating
FROM
food.cafes cf
JOIN
food.reviews r ON cf.id = r.cafe_id
GROUP BY
cf.name
HAVING
COUNT(r.id) >= 3
ORDER BY
avg_rating DESC;補足：
ROUND()で評価を小数第2位までに整えてます。
→ そのままだと「4.333333333…」みたいになるので見づらいので。
実行結果
Komorebi Coffeeがトップ評価になりました。
レビュー数が少なすぎるカフェは除外され、
少しだけ信ぴょう性の高いランキングになりました。
結局、HAVINGって何？
HAVINGは、GROUP BYのあとに条件をつけるために使います。
WHEREでは集計条件は書けないので注意が必要です。
まとめ
いかがだったでしょうか。
GROUP BYを使うことで、どの粒度でデータをまとめるか明確にすることができました。
また、WHERE ではなく HAVING を使うと、レビュー数が一定数以上など、集計結果（COUNT や SUM など）に対してフィルタをかけることができました。
加えて、平均値などの集計関数を ORDER BY と組み合わせることで、
より信頼性の高い評価順・ランキングを実現できました。
グループ化・集計後フィルタ・並び替えの適切な活用で、
データベースから「意味のある正しいデータ」を抽出できるようになりたいです。
最後まで読んでくださってありがとうございました!
コメント