はじめに
お疲れ様です。
今回は、 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
| SQL | 役割 |
|---|---|
ROW_NUMBER() | 各カフェごとにレビューに順位を付ける（1位が最新） |
PARTITION BY | 「カフェごと」にグループを分ける |
ORDER BY review_date DESC | 日付の新しい順に並べる（＝最新が上に来る） |
WITH（CTE） | サブクエリを先に定義して、あとで使いやすくする |
WHERE rn = 1 | 各グループで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句は、SQLクエリ内で使い回せる一時的な名前付きテーブルみたいなものです。
複雑な処理を整理して書けます。
SELECT
r.*,
ROW_NUMBER() OVER (PARTITION BY cafe_id ORDER BY review_date DESC) AS rn
FROM
food.reviews rWITH句の中身 実行結果：
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）
まとめ
通常の GROUP BY では「グループごとの代表値」しか取れませんが、
ウィンドウ関数（ROW_NUMBER() + PARTITION BY）を使いこなすことで、
グループごとの順位付けや最新データの抽出が自由に行えるようになります。
ログ解析や履歴データの絞り込みにも大変便利なので、活用してみてください。
最後まで読んでくださってありがとうございました。
コメント