Фильмы длиннее среднего

Задача

Редактор каталога отбирает «полный метр»: «Дай фильмы, которые длиннее среднего по нашей базе. Не фиксированный порог в минутах, а именно относительно среднего — чтобы отбор сам подстраивался под каталог. Двадцати самых длинных для примера хватит».

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

Схема данных

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

• film(film_id, title, length, ...) — каталог; length — длительность фильма в минутах.

Решение

SELECT title, length
FROM film
WHERE length > (SELECT avg(length) FROM film)
ORDER BY length DESC, title
LIMIT 20;

Разбор

«Длиннее среднего» — фраза, которая почти дословно переводится в скалярный подзапрос. Скалярный — значит возвращающий ровно одно значение (одну строку, один столбец). Подзапрос (SELECT avg(length) FROM film) считает среднюю длительность по всему каталогу и отдаёт наружу одно число. Внешний запрос сравнивает с этим числом каждый фильм: length > <среднее>.

Ключевая мысль: порог не зашит константой, а вычисляется из тех же данных. Если завтра в каталог зальют пачку короткометражек, среднее сдвинется вниз, и отбор автоматически пересоберётся — в этом сила подзапроса против «магического числа» вроде length > 115.

Почему нельзя написать просто WHERE length > avg(length)? Потому что avg — агрегат, а агрегаты в WHERE запрещены: WHERE работает на уровне отдельной строки и выполняется до того, как посчитан любой агрегат. Подзапрос обходит это ограничение — он считает среднее отдельным, самостоятельным запросом, а наружу отдаёт уже готовое число, обычную константу для сравнения.

Тонкость про NULL: если бы у части фильмов length был пустым, avg их просто проигнорировала бы (агрегаты пропускают NULL), а строки с NULL в length не прошли бы условие > — сравнение с NULL даёт «неизвестно», а не «истину». Как NULL ведёт себя в сравнениях — в вопросе как NULL ведёт себя в SQL.

ORDER BY length DESC, title ставит самые длинные наверх, а title вторым ключом разводит фильмы одинаковой длины в стабильном порядке, чтобы LIMIT 20 каждый раз отрезал одну и ту же двадцатку.

Когда порог считается не по всей таблице, а по группе — например, «длиннее всех в своём рейтинге», — подзапрос становится коррелированным, смотри задачу самые длинные фильмы в своём рейтинге.

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

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