Крупные категории: фильтр через HAVING

Задача

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

Верни название категории и её суммарную выручку, но только для категорий с выручкой выше 4000.

Схема данных

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

• payment(payment_id, rental_id, amount, ...) — платежи.
• rental(rental_id, inventory_id, ...) — аренды.
• inventory(inventory_id, film_id, ...) — копии дисков.
• film_category(film_id, category_id) — фильм ↔ категория.
• category(category_id, name) — справочник жанров.

Решение

SELECT cat.name AS category,
       sum(p.amount) AS revenue
FROM payment p
JOIN rental r USING (rental_id)
JOIN inventory i USING (inventory_id)
JOIN film_category fc USING (film_id)
JOIN category cat USING (category_id)
GROUP BY cat.name
HAVING sum(p.amount) > 4000
ORDER BY revenue DESC;

Разбор

Это та же выручка по категориям, что в соседней задаче, но теперь нужно отсечь мелкие жанры. И вот главный вопрос: почему фильтр стоит в HAVING, а не в WHERE?

Потому что мы фильтруем по агрегату — по SUM(p.amount), которого до группировки ещё не существует. Порядок такой: сначала JOIN и WHERE работают со строками, потом GROUP BY схлопывает их в группы, и только затем считаются агрегаты. WHERE выполняется раньше группировки — на этом этапе суммы по жанру ещё нет, и написать WHERE sum(p.amount) > 4000 нельзя, PostgreSQL выдаст ошибку «aggregate functions are not allowed in WHERE». HAVING же работает уже после группировки — по готовым суммам. Это ровно то, ради чего он придуман: фильтровать группы, а не строки. Полный разбор границы — в вопросе чем WHERE отличается от HAVING.

Практическое правило: WHERE — про отдельные строки (например, «только платежи 2007 года»), HAVING — про группы («только жанры с выручкой выше порога»). Они не конкурируют, а работают на разных этапах и часто стоят в одном запросе вместе: сначала WHERE сужает исходные строки, потом GROUP BY их собирает, потом HAVING отсекает слабые группы.

Обрати внимание, что в HAVING мы пишем сам агрегат sum(p.amount), а не алиас revenue: по стандарту SQL в HAVING алиасы из SELECT не видны (в отличие от ORDER BY, где revenue работает). PostgreSQL тут строг, так что повтор выражения — не лишняя перестраховка, а необходимость.

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

category | revenue
--- | ---
Sports | 4892.19
Sci-Fi | 4336.01
Animation | 4245.31
Drama | 4118.46
Comedy | 4002.48