[SQL(Window)]RANK関数について

はじめに

「カフェのメニューって種類がいっぱいあるけど、カテゴリごとにカロリーが高い順に並べたい！」

って思ったことありませんか？

そんな時に役立つのがSQLのウィンドウ関数・RANK()を使った方法です。

早速解説していきます。

RANK関数って何？

RANK()はウィンドウ関数の1つで、同じ値なら同じ順位をつけてくれるランキング関数です。
今回は、PARTITION BYでカテゴリ別に区切って、ORDER BYでカロリーの高い順に順位を決めています。

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

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

クエリ

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;

出力結果

カテゴリごとにカロリーが高いメニューをランキング形式で表示できました!

まとめ

カテゴリごとに順位をつけたいとき、RANK() OVER (PARTITION BY ... ORDER BY ...)を使うことで、

同じカロリーなら同じ順位になるから、公平なランキングが作れます。

ぜひ活用してみてください。