Пары «фильм — категория»

Задача

Аналитик контента начинает исследование жанров и просит заготовку: «Дай мне простыню „фильм — его категория“. Дальше я сам буду крутить, но сперва нужен базовый список названий с жанром. Первые 20 по алфавиту названий — на посмотреть формат».

Верни название фильма и название его категории.

Схема данных

Учебная база проката dvdrental. Связь фильма и жанра хранится через отдельную таблицу-связку:

• film(film_id, title, ...) — каталог фильмов.
• film_category(film_id, category_id) — связка: какой фильм к какой категории относится.
• category(category_id, name, ...) — справочник жанров.

Решение

SELECT f.title, cat.name AS category
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category cat ON cat.category_id = fc.category_id
ORDER BY f.title, cat.name
LIMIT 20;

Разбор

Модель тут заложена «на вырост»: film_category — связующая (junction) таблица для связи «многие-ко-многим». В принципе фильм мог бы относиться к нескольким жанрам, а жанр — содержать много фильмов; такая схема это позволяет. В учебной базе dvdrental по факту у каждого фильма ровно одна категория, поэтому наш список получается «один к одному», но джойн строим по-взрослому — через связку, чтобы запрос не сломался, если завтра фильму добавят второй жанр.

Соединяем в два прыжка: film → film_category по film_id, затем film_category → category по category_id. Оба — INNER JOIN, так что в результат попадают только фильмы, у которых категория проставлена (в этой базе — все). Название жанра берём из справочника category.name, а не пытаемся хранить его в фильме: справочник — единый источник правды, переименуют жанр один раз, и он поменяется во всех парах.

ORDER BY f.title, cat.name даёт стабильный порядок; названия фильмов в dvdrental уникальны, так что первого ключа хватило бы, но второй ключ — дешёвая страховка от неоднозначности. LIMIT 20 отрезает витрину для примера.

Такая же схема связки, только между фильмами и актёрами, разбирается в задаче актёры конкретного фильма. А если поверх этих пар посчитать выручку по жанрам — получится задача выручка по категориям фильмов.

Ожидаемый результат

title | category
--- | ---
Academy Dinosaur | Documentary
Ace Goldfinger | Horror
Adaptation Holes | Documentary
Affair Prejudice | Horror
African Egg | Family
Agent Truman | Foreign
Airplane Sierra | Comedy
Airport Pollock | Horror
Alabama Devil | Horror
Aladdin Calendar | Sports
… всего строк: 20 (показаны первые 10)