Связаться с нами
Backend

Подзапросы и CTE MySQL: строим отчёт по выручке

4 минут на чтение
Иконка чата 0
Иконка глаза 18

Часть 8 из 16

Мы уже умеем считать выручку отдельно по каждому событию. Следующий вопрос сложнее: какие события заработали больше среднего и как показать в запросе несколько шагов расчёта? Для этого пригодятся подзапросы и CTE — временные именованные результаты, из которых строится основной отчёт.

В этой части сначала проверим простой агрегат, затем добавим сравнение со средним и посмотрим, что происходит с событиями без продаж. Так длинный SQL станет понятнее, а не просто длиннее.

Сначала агрегат

SELECT event_id,SUM(price) AS revenue
FROM ticket_sales GROUP BY event_id;

Получили одну строку на событие с продажами. Не подключайте сюда performances, чтобы не размножить билеты.

Именованные шаги CTE

WITH per_event AS (
 SELECT event_id,SUM(price) AS revenue
 FROM ticket_sales GROUP BY event_id
), avg_revenue AS (
 SELECT AVG(revenue) AS amount FROM per_event
)
SELECT e.name,pe.revenue
FROM per_event AS pe
JOIN events AS e ON e.event_id=pe.event_id
CROSS JOIN avg_revenue AS ar
WHERE pe.revenue > ar.amount
ORDER BY pe.revenue DESC;

Наборы после WITH живут только в одном запросе. Каждый шаг можно запустить отдельно, чтобы проверить промежуточный результат.

Что, если продаж нет?

Событие без продаж отсутствует в per_event. Чтобы показать и его, начинайте SELECT от events, присоедините CTE через LEFT JOIN и замените отсутствующее значение на ноль с COALESCE.

Структура прежде сложного SQL

Подзапросы не чинят плохую схему. Повторяющиеся названия и списки артистов через запятую усложнят любой отчёт. В следующей части разберём связи и решения, которые предотвращают дублирование данных.

Подзапрос отвечает на промежуточный вопрос

Можно сформулировать отчёт двумя шагами: сначала «какая выручка у каждого события?», потом «какие события выше средней?». Подзапрос после FROM создаёт промежуточный набор строк; MySQL требует дать ему алиас, например per_event. Подзапрос в WHERE ... IN полезен, если нужно проверить попадание ключа в набор.

Зачем CTE

Оператор WITH даёт имя одному или нескольким этапам. В нашем примере per_event — таблица выручки, а avg_revenue — среднее по этим суммам. Основной SELECT читается почти как короткий план анализа. CTE существует только пока выполняется этот запрос и не занимает постоянное место в базе.

Чтобы видеть и события без продаж, измените основу запроса: начните от events, присоедините per_event через LEFT JOIN, а NULL замените на ноль с COALESCE. Не путайте отсутствие строки агрегата с суммой 0: это разные состояния, и решение о показе принимает отчёт.

Упражнение: временно выполните только SELECT из CTE per_event, сверьте 2–3 результата вручную, затем верните внешний запрос. В следующей части разберём, почему отдельные таблицы делают такой анализ надёжнее.

Выбираем между IN и EXISTS

Подзапрос с IN отвечает на вопрос «значение входит в список результата?». Например, можно выбрать события, у которых есть хотя бы один билет. EXISTS проверяет, существует ли подходящая строка, и часто яснее выражает именно такую задачу:

SELECT e.event_id, e.name
FROM events AS e
WHERE EXISTS (
  SELECT 1 FROM ticket_sales AS s
  WHERE s.event_id = e.event_id
);

Число 1 внутри SELECT не является данными отчёта: EXISTS интересует сам факт наличия строки. Если нужно показать сумму, используйте агрегат, а не EXISTS.

Сначала проверьте промежуточное значение

При ошибке в длинном запросе временно выполните CTE как отдельный запрос и убедитесь, что в нём одна строка на событие. Потом отдельно проверьте среднее, затем верните финальную часть.

Это похоже на проверку промежуточных результатов в отладчике: вместо догадок вы находите конкретный этап, где логика разошлась.

Если событий без билетов тоже нужно показывать, сравните EXISTS-фильтр с LEFT JOIN. Первый намеренно исключает строки без продаж; второй сохраняет их. Формулируйте это требование до написания SQL, а не после того, как заметили пропавшие события.

Планируйте запрос как короткую цепочку

  1. Сформулируйте итоговый вопрос обычными словами.
  2. Выполните внутренний SELECT и проверьте его строки.
  3. Назовите промежуточный результат через CTE, если он помогает чтению.
  4. Добавьте внешний фильтр и отдельно решите, включать ли события без продаж.

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

← Предыдущая статья: GROUP BY и SUM в MySQL: считаем продажи билетов

Следующая статья: Нормализация MySQL: проектируем связи без дубликатов →

Рекомендуемые статьи