COUNT, SUM, AVG и GROUP BY: честно сворачиваем строки
Превращаем много строк в несколько чисел и группируем их по товарам — и видим, как среднее может спрятать именно то, что тебе нужно знать.
Фильтрация сужает таблицу, но для некоторых вопросов нужно противоположное движение: свернуть много строк в одно число. Сколько столовая заработала за день? Сколько было заказов? Ответить показом строк нельзя — нужна агрегация, и ты уже встречался с этой идеей: это агрегация над множеством, то же движение из модуля логики, только на настоящей таблице.
SELECT COUNT(*) FROM sales
4 rows in, 1 row out
SELECT SUM(count) FROM sales
40 + 55 + 23 + 60 = 178Наружу выходит одна строка, сколько бы строк ни вошло.Четыре функции, которыми ты будешь пользоваться постоянно: COUNT считает строки (или значения), SUM складывает, AVG даёт среднее, MAX и MIN возвращают самое большое и самое маленькое. Каждая съедает много значений и возвращает ровно одно. COUNT(*) считает строки как есть; COUNT(paid) считает только строки, где paid не NULL — разница между ними часто и есть вся история.
Столовой нужно одно число на блюдо: сколько единиц каждого продано всего. Без группировки пришлось бы писать четыре отдельных запроса с WHERE item = 'soup', WHERE item = 'juice' и так далее. GROUP BY делает их все одним запросом и гарантирует каждому блюду ровно одну строку.
SELECT item, SUM(count)
FROM sales
GROUP BY item
+---------+------------+
| item | SUM(count) |
+---------+------------+
| soup | 40 |
| juice | 75 |
| salad | 23 |
| sandwich| 55 |
+---------+------------+Одна строка на товар: каждая группа сворачивается в свой итог.Вот механика. Четыре строки по значению item распадаются на четыре группы. Внутри каждой группы SUM(count) складывает значения count. В результате выходит одна строка на группу, а не на исходную строку. Из этого следует и то, какие столбцы могут стоять рядом с агрегатом в SELECT: только те, у которых внутри группы одно-единственное значение.
SELECT item, price, SUM(count)
FROM sales
GROUP BY item
item -> one value per group
SUM(count) -> one value per group
price -> many values per group, no single answerПочему смесь сгруппированных и несгруппированных столбцов не имеет ответа.Теперь самый тонкий капкан, и для него вообще не нужен неправильный SQL. Столовая измеряет очередь в минутах. Два дня, и средние почти совпадают. Говорит ли среднее 3.2 и среднее 3.4 о том, что эти дни были похожи?
queue times in minutes for two days
monday: 1, 1, 1, 1, 12 AVG = 3.2
tuesday: 2, 3, 3, 4, 5 AVG = 3.4
same average, different daysОдно и то же среднее, собранное из двух очень разных дней.Понедельник был спокойным: четыре ученика ждали минуту, и один несчастный ждал двенадцать. Вторник — ровное, умеренное ожидание для всех. Средние совпадают, дни не имеют ничего общего. Среднее это одно число, выполняющее работу целого распределения, и само по себе оно не может сказать, редки ли двенадцатиминутные ожидания или обычны.
Практика
Напиши один запрос, который показывает каждый товар с общим числом проданных единиц. В таблице sales столбцы item, price и count.
Подсказки
- В ответе должно быть по одной строке на товар, и именно GROUP BY создаёт одну строку на группу.
- Внутри каждой группы нужна сумма count, то есть SUM(count).
- Порядок такой: что оставить и как свернуть — в SELECT, таблица — в FROM, группировка — в GROUP BY.
Одноклассник пишет: SELECT item, price, SUM(count) FROM sales GROUP BY item. Почему база отказывается выполнять этот запрос?
Подсказки
- Про каждый столбец в SELECT спроси: есть ли у него внутри группы ровно одно значение?
- item — сама группа, а price — разные числа на разных строках одной группы.
Столовая видит, что средняя очередь вчера была 10 минут. Что полезнее всего проверить, прежде чем делать вывод?
Подсказки
- Среднее 10 можно собрать из 10 и 10, а можно из 1 и 19 — в какой очереди ты предпочёл бы стоять?
- Столовой не нужен точный список, чтобы действовать: ей нужно понять, редки ли долгие ожидания или обычны.