Разброс длительности по рейтингу

Задача

Продюсер спрашивает, как устроен каталог по возрастным рейтингам: «В каждом рейтинге — насколько разные фильмы по длине? Дай минимальную и максимальную длительность и сколько всего фильмов в каждой группе».

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

Схема данных

Учебная база проката dvdrental, одна таблица:

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

Решение

SELECT rating,
       min(length) AS shortest,
       max(length) AS longest,
       count(*) AS films
FROM film
GROUP BY rating
ORDER BY rating;

Разбор

В прошлой задаче про самый длинный и короткий фильм MIN/MAX считались по всему каталогу и давали одну строку. Здесь добавляется GROUP BY rating — и та же логика применяется отдельно к каждой группе. Движок раскладывает фильмы по пяти корзинам-рейтингам и внутри каждой считает свои минимум, максимум и количество. На выходе — по строке на рейтинг.

Ключевое правило группировки: всё, что стоит в SELECT вне агрегатов, обязано быть в GROUP BY. Здесь это rating — единственное «неагрегатное» поле, и оно честно перечислено в GROUP BY. Если бы мы забыли и написали в SELECT ещё и title, PostgreSQL отказался бы выполнять запрос: он не знает, какое из сотни названий группы показать. Почему так строго — подробно разобрано в вопросе почему нельзя брать колонку без агрегата при GROUP BY.

Три агрегата в одном SELECT — MIN, MAX, COUNT — считаются за один проход по данным, движку не нужно перечитывать таблицу трижды. Это дешевле, чем три отдельных запроса, и читается как единая «карточка группы».

COUNT(*) показывает наполненность каждого рейтинга — полезно рядом с разбросом: минимум и максимум по группе из трёх фильмов и по группе из двухсот — это очень разная надёжность вывода. ORDER BY rating просто выстраивает рейтинги в стабильном порядке, чтобы отчёт не прыгал от запуска к запуску.

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

rating | shortest | longest | films
--- | --- | --- | ---
G | 47 | 185 | 178
PG | 46 | 185 | 194
PG-13 | 46 | 185 | 223
R | 49 | 185 | 195
NC-17 | 46 | 184 | 210