Самые длинные фильмы в своём рейтинге

Задача

Отдел закупок думает про хронометраж по возрастным категориям: «В каждом рейтинге (G, PG, R и так далее) найди самые длинные фильмы — те, длиннее которых в этом же рейтинге нет ничего. Хотим понять, где у нас „тяжёлый“ контент».

Верни название, рейтинг и длительность фильмов, которые не короче любого другого фильма с тем же рейтингом.

Схема данных

Учебная база проката dvdrental:

• film(film_id, title, length, rating, ...) — каталог; rating — возрастной рейтинг (G, PG, PG-13, R, NC-17), length — длительность в минутах.

Решение

SELECT f.title, f.rating, f.length
FROM film f
WHERE f.length >= ALL (
  SELECT f2.length
  FROM film f2
  WHERE f2.rating = f.rating
)
ORDER BY f.rating, f.length DESC, f.title;

Разбор

«Не короче любого другого в своём рейтинге» — это в точности оператор ALL. Конструкция f.length >= ALL (...) истинна, когда length фильма больше или равен каждому значению из подзапроса. Подзапрос отдаёт длительности всех фильмов того же рейтинга — значит, условие выполнено ровно для тех, кто в своём рейтинге на вершине (или делит её с кем-то).

Подзапрос коррелированный: он связан с внешней строкой через WHERE f2.rating = f.rating. Для каждого фильма движок как бы заново собирает список длительностей его собратьев по рейтингу и сверяет с ним. Псевдонимы f и f2 — это одна и та же таблица film, взятая дважды: внешняя копия перебирает фильмы-кандидаты, внутренняя — их «одногруппников». Без двух псевдонимов движок не понял бы, какой film где.

Почему ALL, а не MAX? Их можно свести друг к другу: length >= ALL (…) эквивалентно length = (SELECT max(length) …). ALL тут выбран, чтобы показать сам оператор — на собеседовании про ANY/ALL спрашивают, а живьём их видят редко. Через MAX тот же смысл, и это разбирается в задаче самый прибыльный фильм каждой категории.

Ловушка с NULL: если бы у какого-то фильма в рейтинге length был пустым, >= ALL сломался бы — сравнение с NULL даёт «неизвестно», и всё выражение перестало бы быть истинным. Тогда в ответ не попал бы никто из этого рейтинга. В нашей базе длительности заполнены, но помнить про этот эффект нужно всегда. Как NULL ведёт себя в сравнениях — в вопросе как NULL ведёт себя в SQL.

Если рейтинги в этой задаче держат несколько фильмов одинаковой максимальной длины — вернутся все они; ORDER BY f.rating, f.length DESC, f.title держит вывод детерминированным. А порог «относительно среднего по всему каталогу», а не по группе, — в задаче фильмы длиннее среднего.

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

title | rating | length
--- | --- | ---
Control Anthem | G | 185
Darn Forrester | G | 185
Muscle Bright | G | 185
Worst Banger | PG | 185
Chicago North | PG-13 | 185
Gangs Pride | PG-13 | 185
Pond Seattle | PG-13 | 185
Home Pity | R | 185
Soldiers Evolution | R | 185
Sweet Brotherhood | R | 185
… всего строк: 14 (показаны первые 10)