В прошлом уроке мы собирали строки из нескольких таблиц. Ответы получались длинными: по строке на билет, по строке на сеанс. Директору кинотеатра такая простыня не нужна. Он спрашивает коротко: сколько билетов продано, сколько денег принёс сеанс, какой режиссёр чаще всех в афише. Сегодня учимся сворачивать много строк в одно число.
От стопки билетов к итогу
Вечером кассир кладёт перед собой стопку корешков. Можно посчитать, сколько их всего. Можно разложить стопку на кучки по сеансам и пересчитать каждую. Можно отложить кучки, где меньше двух корешков. Три действия, и у каждого есть своё слово в SQL: агрегатная функция, GROUP BY и HAVING.
COUNT: три способа считать
COUNT считает строки, но смотря как его спросить:
SELECT COUNT(*), COUNT(viewer_id), COUNT(DISTINCT viewer_id) FROM tickets;+----------+------------------+---------------------------+
| COUNT(*) | COUNT(viewer_id) | COUNT(DISTINCT viewer_id) |
+----------+------------------+---------------------------+
| 11 | 9 | 5 |
+----------+------------------+---------------------------+
1 row in set (0.00 sec)Три числа, три вопроса:
COUNT(*)считает строки целиком: билетов одиннадцать;COUNT(viewer_id)считает строки, где в колонке не NULL: два кассовых билета без зрителя выпали;COUNT(DISTINCT viewer_id)считает разные значения: билеты покупали пять разных зрителей.
Если нужно «сколько билетов», пиши COUNT(*). Если «сколько покупателей», то COUNT(DISTINCT viewer_id). Перепутать легко, а ошибка тихая: запрос отработает и вернёт правдоподобное число. Подробнее варианты разобраны в уроке для собеседования.
SUM, AVG, MIN и MAX
Остальные агрегаты работают с числами в колонке:
SELECT MIN(price), MAX(price), AVG(price), SUM(price) FROM screenings;+------------+------------+------------+------------+
| MIN(price) | MAX(price) | AVG(price) | SUM(price) |
+------------+------------+------------+------------+
| 150.00 | 900.00 | 400.000000 | 2800.00 |
+------------+------------+------------+------------+
1 row in set (0.00 sec)Посмотри на среднее: 400.000000, шесть знаков после запятой, хотя у price тип DECIMAL(7, 2). MySQL добавляет к точности среднего четыре знака. Для целой колонки получится четыре знака:
SELECT MIN(duration_min), MAX(duration_min), AVG(duration_min), SUM(duration_min) FROM films;+-------------------+-------------------+-------------------+-------------------+
| MIN(duration_min) | MAX(duration_min) | AVG(duration_min) | SUM(duration_min) |
+-------------------+-------------------+-------------------+-------------------+
| 10 | 184 | 122.8571 | 860 |
+-------------------+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)Хвост убирают через ROUND(AVG(duration_min), 1).
Все агрегаты, кроме COUNT(*), пропускают NULL. И ещё одна мелочь, которая ломает отчёты: сумма по пустому набору строк это не ноль, а NULL.
SELECT SUM(price) FROM screenings WHERE id > 100;+------------+
| SUM(price) |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)COUNT(*) на тех же строках вернёт 0. Если в отчёте нужен ноль, оберни сумму в COALESCE(SUM(price), 0).
GROUP BY: раскладываем по кучкам
GROUP BY делит строки на группы с одинаковым значением в колонке, и агрегат считается отдельно по каждой. Билеты по статусам:
SELECT status, COUNT(*) FROM tickets GROUP BY status ORDER BY COUNT(*) DESC;+----------+----------+
| status | COUNT(*) |
+----------+----------+
| paid | 7 |
| used | 3 |
| refunded | 1 |
+----------+----------+
3 rows in set (0.00 sec)Одна строка ответа на одну группу. Сервер прошёл по одиннадцати билетам, разложил их на три кучки по статусу и в каждой посчитал корешки. Колонка status в SELECT разрешена, потому что внутри кучки она у всех одинаковая. Это правило понадобится чуть ниже.
NULL тоже образует группу, все NULL попадают в одну:
SELECT director, COUNT(*) AS films_count FROM films GROUP BY director ORDER BY films_count DESC, director;+----------------------+-------------+
| director | films_count |
+----------------------+-------------+
| Тарковский | 2 |
| NULL | 1 |
| Балабанов | 1 |
| Меньшов | 1 |
| Норштейн | 1 |
| Рязанов | 1 |
+----------------------+-------------+
6 rows in set (0.00 sec)Группа по колонке ENUM сортируется не по алфавиту, а по порядку значений в объявлении типа, как в уроке про строковые типы. Число фильмов и средняя длительность по возрастному рейтингу:
SELECT age_rating, COUNT(*), ROUND(AVG(duration_min), 1) FROM films GROUP BY age_rating ORDER BY age_rating;+------------+----------+-----------------------------+
| age_rating | COUNT(*) | ROUND(AVG(duration_min), 1) |
+------------+----------+-----------------------------+
| 0+ | 1 | 10.0 |
| 6+ | 1 | 184.0 |
| 12+ | 3 | 160.0 |
| 16+ | 1 | 96.0 |
| 18+ | 1 | 90.0 |
+------------+----------+-----------------------------+
5 rows in set (0.00 sec)12+ стоит после 6+, хотя при сравнении строк «1» меньше «6». Рейтинги идут от детских к взрослым, ровно как их перечислили в CREATE TABLE. А ROUND(..., 1) срезал лишние знаки среднего до одного.
Группировать можно и по вычисленному значению. Зрители по году регистрации:
SELECT YEAR(registered_at) AS y, COUNT(*) FROM viewers GROUP BY y ORDER BY y;+------+----------+
| y | COUNT(*) |
+------+----------+
| 2025 | 1 |
| 2026 | 5 |
+------+----------+
2 rows in set (0.00 sec)ONLY_FULL_GROUP_BY: что можно писать в SELECT
Захотим вывести рядом с режиссёром название фильма:
SELECT director, title, COUNT(*) FROM films GROUP BY director;ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kino.films.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byОшибка 1055 по делу. У Тарковского два фильма, а строка в группе одна. Какое название в неё поставить, «Сталкер» или «Солярис»? Сервер не угадывает. Режим ONLY_FULL_GROUP_BY включён в MySQL 8.4 по умолчанию и требует, чтобы каждая колонка в SELECT была либо в GROUP BY, либо внутри агрегата. Старые версии MySQL без этого режима подставляли любое значение из группы, и из-за этого в старых статьях встречаются запросы, которые сейчас падают.
Родственная ошибка 1140 бывает без GROUP BY вовсе:
SELECT title, COUNT(*) FROM films;ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'kino.films.title'; this is incompatible with sql_mode=only_full_group_byCOUNT(*) сворачивает всю таблицу в одну строку, и для title в ней снова нет однозначного значения.
Есть важное исключение. Если группировать по первичному ключу, остальные колонки той же таблицы писать можно: у одного id ровно одно название, и сервер это знает. Это называется функциональной зависимостью. Число сеансов у каждого фильма:
SELECT f.id, f.title, COUNT(s.id) AS shows
FROM films f
LEFT JOIN screenings s ON s.film_id = f.id
GROUP BY f.id
ORDER BY f.id;+----+-------------------------------------------+-------+
| id | title | shows |
+----+-------------------------------------------+-------+
| 1 | Брат | 2 |
| 2 | Москва слезам не верит | 1 |
| 3 | Ирония судьбы | 1 |
| 4 | Сталкер | 1 |
| 5 | Ёжик в тумане | 1 |
| 6 | Новые имена | 1 |
| 7 | Солярис | 0 |
+----+-------------------------------------------+-------+
7 rows in set (0.00 sec)Здесь пригодилась разница из первого раздела. «Солярис» пришёл через LEFT JOIN с NULL в s.id, и COUNT(s.id) дал честный ноль. COUNT(*) посчитал бы эту строку и выдал единицу.
Выручка по сеансам
Соберём всё вместе: для каждого сеанса цену, число непогашенных билетов и выручку. Возвраты отсекаем в ON, как в прошлом уроке, чтобы сеанс без билетов не потерялся:
SELECT s.id, s.price, COUNT(t.id) AS sold, s.price * COUNT(t.id) AS revenue
FROM screenings s
LEFT JOIN tickets t ON t.screening_id = s.id AND t.status <> 'refunded'
GROUP BY s.id
ORDER BY revenue DESC, s.id;+----+--------+------+---------+
| id | price | sold | revenue |
+----+--------+------+---------+
| 4 | 900.00 | 2 | 1800.00 |
| 1 | 450.00 | 3 | 1350.00 |
| 3 | 400.00 | 2 | 800.00 |
| 2 | 350.00 | 1 | 350.00 |
| 5 | 300.00 | 1 | 300.00 |
| 6 | 150.00 | 1 | 150.00 |
| 7 | 250.00 | 0 | 0.00 |
+----+--------+------+---------+
7 rows in set (0.00 sec)s.price в SELECT разрешён без агрегата по тому же правилу первичного ключа: группа это один сеанс, у него одна цена. Сеанс 7 остался в отчёте с нулём.
HAVING: фильтр после подсчёта
Нужны сеансы, где продано два билета и больше. Первая мысль: написать условие в WHERE.
SELECT screening_id, COUNT(*) FROM tickets WHERE COUNT(*) > 1 GROUP BY screening_id;ERROR 1111 (HY000): Invalid use of group functionОшибка 1111. WHERE отбирает отдельные строки до того, как их разложили по кучкам, и никакого COUNT в этот момент ещё нет. Условие на итог группы пишут в HAVING:
SELECT screening_id, COUNT(*) AS sold FROM tickets GROUP BY screening_id HAVING COUNT(*) >= 2 ORDER BY screening_id;+--------------+------+
| screening_id | sold |
+--------------+------+
| 1 | 3 |
| 3 | 3 |
| 4 | 2 |
+--------------+------+
3 rows in set (0.00 sec)WHERE и HAVING часто работают вместе. Сначала WHERE оставляет только оплаченные билеты, потом HAVING оставляет крупные группы. В MySQL в HAVING можно сослаться на псевдоним колонки:
SELECT screening_id, COUNT(*) AS sold FROM tickets WHERE status = 'paid' GROUP BY screening_id HAVING sold >= 2 ORDER BY screening_id;+--------------+------+
| screening_id | sold |
+--------------+------+
| 3 | 2 |
| 4 | 2 |
+--------------+------+
2 rows in set (0.00 sec)Сеанс 1 выпал: его билеты уже погашены, статус used. Порядок работы запроса такой: FROM и JOIN, затем WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Он подробно разобран в уроке про WHERE и HAVING.
GROUP_CONCAT: склеить группу в строку
Иногда из группы нужно не число, а список. GROUP_CONCAT склеивает значения через разделитель. Места каждого зрителя одной строкой:
SELECT viewer_id, GROUP_CONCAT(seat ORDER BY seat SEPARATOR ', ') AS seats
FROM tickets
WHERE viewer_id IS NOT NULL
GROUP BY viewer_id
ORDER BY viewer_id;+-----------+--------+
| viewer_id | seats |
+-----------+--------+
| 1 | A1, C3 |
| 2 | A2, B1 |
| 3 | A1, A2 |
| 4 | V2 |
| 5 | D4, V1 |
+-----------+--------+
5 rows in set (0.00 sec)ORDER BY внутри скобок задаёт порядок в списке, SEPARATOR задаёт разделитель, по умолчанию это запятая без пробела. В PostgreSQL то же самое делает string_agg.
Что запомнить
COUNT(*)считает строки,COUNT(колонка)пропускаетNULL,COUNT(DISTINCT ...)считает разные значения.AVGот целых даётDECIMALс четырьмя знаками после запятой.GROUP BYраскладывает строки по группам, а вSELECTостаются только колонки группировки и агрегаты.WHEREотбирает строки до подсчёта,HAVINGотбирает группы после.GROUP_CONCATсклеивает значения группы в одну строку.
Попробуй сам
В песочнице база kino. Первый запрос считает продажи и выручку по фильмам.
Здесь SUM(s.price) честно считает выручку: каждая строка это один билет со своей ценой сеанса. Найди, каких фильмов нет в ответе, и объясни почему. Добавь HAVING sold >= 2. Почини второй запрос двумя способами: убери title или оберни его в GROUP_CONCAT.
Проверь себя
Задание
Дальше
Иногда для ответа нужен результат другого запроса: фильмы длиннее среднего, зрители, которые ничего не купили. Для этого запрос вкладывают в запрос. Следующий урок про подзапросы и UNION.
домашка