☕️【GROUP BYとHAVING】「レビュー件数3件以上」のカフェだけ表示して評価ランキングを作る方法

💭 はじめに：

「評価が高いカフェが知りたい！」でも、レビュー1件だけで★5とか出てくると信用できない…そんな時にSQLで“ちゃんとした評価順”に並べる方法をご紹介します💡

テーブル作成とインポート：

-- スキーマ作成（存在しない場合）
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）

🛠 使用する材料（構文）：

クエリ：

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;

実行結果：

Komorebi Coffeeがトップ評価！レビュー数が少なすぎるカフェは除外されているので、信頼できるランキングになりました。

🌷 補足：HAVINGって何？：

• HAVINGは、GROUP BYのあとに条件をつけるために使います。
• WHEREでは集計条件は書けないので注意！

📝 つまり：

WHERE COUNT(*) >= 3

-- OK
HAVING COUNT(*) >= 3

🎗️おまけ：

ROUND()で評価を小数第2位までに整えてます。
→ そのままだと「4.333333333…」みたいになるので見づらいので。

🎀 まとめ

• SQLでレビュー数をフィルタする時は、HAVINGを使いましょう
• 評価順に並べるには、ORDER BY AVG()が使えます
• 集計したい単位（今回はカフェ）は必ずGROUP BYしましょう