☕️【CTE、ROW_NUMBER】各カフェの“最新レビューだけ”を表示してみよう!

💭 はじめに：

「最近のレビューだけを見て、どんなカフェが注目されてるか知りたい！」
でも、複数レビューのうち最新の1件だけを取るのってちょっと難しそうですよね。
今回は、そんな時に便利な ROW_NUMBER() を使って、各カフェごとに最新のレビューを1件だけ取得する方法をご紹介します🌱

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

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

やりたいこと：

• 各カフェの最新レビュー1件だけを表示したい
• 日付が一番新しいレビューだけに絞る
• 「最新の評価だけを見るランキング」が作れるように！

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

SQL：

WITH ranked_reviews AS (
  SELECT
    r.*,
    ROW_NUMBER() OVER (PARTITION BY cafe_id ORDER BY review_date DESC) AS rn
  FROM
    food.reviews r
)
SELECT
  cf.name AS cafe_name,
  rr.rating,
  rr.review_date
FROM
  ranked_reviews rr
JOIN
  food.cafes cf ON rr.cafe_id = cf.id
WHERE
  rr.rn = 1;

まず、WITH句の中を見てみましょう。

WITH句（CTE）は、SQLクエリ内で使い回せる「一時的な名前付きテーブル」を定義する機能です。
複雑な処理をサブクエリより見やすく・整理して書けるのが特徴です!

SELECT
    r.*,
    ROW_NUMBER() OVER (PARTITION BY cafe_id ORDER BY review_date DESC) AS rn
  FROM
    food.reviews r

WITH句の中身 実行結果：

cafe_id別に順位の高い順(=レビューの投稿日時が新しい順に)に番号が振られています。

全文クエリ実行結果：

💡 こんなふうに、カフェごとに最新の1件だけがしっかり表示されてるのがポイントです!
データがどれだけ増えても、最新レビューだけを見るランキングやレポートに応用できます✨

補足：ROW_NUMBER()って何？

ROW_NUMBER() は、並び順を決めて1件ずつに番号を振ってくれる関数です。
PARTITION BY を使うことで、カフェごとにリセットして順位付けできます。

ROW_NUMBER() OVER (
  PARTITION BY cafe_id
  ORDER BY review_date DESC
)

• PARTITION BY cafe_id：カフェごとに分ける
• ORDER BY review_date DESC：新しい順に並べる
• → 1番目（rn = 1）だけを取り出す！

🎀 まとめ：

• 最新レビューだけを取りたいなら、ROW_NUMBER()＋CTE(WITH句)＋WHERE rn = 1
• 「各グループで1件だけ取る」ときに ROW_NUMBER() は超便利です！
• 今後、売上の最新記録や在庫の最新状態などにも応用できます◎