☕️【ウィンドウ関数】カテゴリ別にカロリーの高いメニューをランキングする方法

💭 はじめに：

「カフェのメニューって種類がいっぱいあるけど、カテゴリごとにカロリーが高い順に並べたい！」って思ったことありませんか？
そんな時に役立つのがSQLのウィンドウ関数RANK()を使った方法です。今回はやさしく解説していきます♪

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

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

やりたいこと：

メニューを「ドリンク」「スイーツ」「軽食」などのカテゴリごとに分ける
その中でカロリーが高い順にランキング（順位）をつける
結果をカテゴリごとにまとまるように表示する

🛠 材料（使うSQL構文）：

SQL構文	役割
JOIN	メニューとカテゴリを結びつける
RANK() OVER()	カテゴリ内で順位をつける
PARTITION BY	カテゴリごとにグループ分け
ORDER BY	カロリーの高い順に並べる

SQL：

SELECT
  mi.name AS menu_name,
  c.name AS category_name,
  mi.calories,
  RANK() OVER (PARTITION BY c.name ORDER BY mi.calories DESC) AS cal_rank
FROM
  food.menu_items mi
JOIN
  food.categories c ON mi.category_id = c.id
ORDER BY
  category_name, cal_rank;

🌻出力結果：