Агрегаты: COUNT, GROUP BY и HAVING

← назад к разделу

До сих пор мы разглядывали строки. Но половина реальных вопросов — не «покажи», а «посчитай»: сколько заказов, на какую сумму, какой средний чек. Для этого есть агрегатные функции — они схлопывают множество строк в одно число.

Пять функций

живой пример

SELECT COUNT(*) FROM orders WHERE status = 'PAID';

COUNT(*) — сколько строк. Остальные четыре работают со значениями столбца:

• SUM(total_amount) — сумма;
• AVG(total_amount) — среднее;
• MIN(total_amount) / MAX(total_amount) — минимум и максимум.

Можно несколько сразу:

живой пример

SELECT COUNT(*), SUM(total_amount), AVG(total_amount) FROM orders WHERE status = 'PAID';

Один нюанс, который отличает профессионала: COUNT(*) считает строки, а COUNT(total_amount) — строки, где total_amount не пуст (не NULL). На чистых данных числа совпадут, на грязных — разойдутся, и эта разница сама по себе находка. А COUNT(DISTINCT customer_id) считает уникальные значения: не «сколько заказов», а «сколько разных клиентов заказывали».

живой пример

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS customers
FROM orders;

Двадцать три и семь: первое число отвечает «сколько раз купили», второе — «сколько человек купили». Путать их в отчёте дорого, а выглядят они одинаково убедительно. Тем же приёмом считают «сколько разных товаров продано» (COUNT(DISTINCT product_id) по позициям заказов) и «за сколько разных дней были заказы».

Пустые значения пропускает не один COUNT: SUM, AVG, MIN и MAX тоже считают так, будто строк с NULL нет вовсе. Отсюда две неожиданности в отчётах. Среднее делится не на все строки, а только на заполненные — и потому выходит выше, чем ждали. А SUM по набору, где не осталось ни одной строки, возвращает не ноль, а NULL: увидели в отчёте пустую клетку вместо нуля — причина, скорее всего, здесь.

Отработать здесь: Разброс цен на витрине

GROUP BY: посчитать по группамспросят на собеседовании

Одно число на всю таблицу — редко то, что нужно. Чаще нужно «в разрезе»: сколько заказов в каждом статусе. Это делает GROUP BY:

живой пример

SELECT status, COUNT(*), SUM(total_amount)
FROM orders
GROUP BY status;

status          | count | sum
PAID            | 4     | 23920.00
SHIPPED         | 2     | 12480.00
REFUNDED        | 1     |  6990.00
DELIVERED       | 3     | 21930.00
DISPUTE         | 1     |  5480.00
COMPLETED       | 5     | 33440.00
EXPIRED         | 1     | 12480.00
PENDING_PAYMENT | 2     | 16970.00
CANCELLED       | 4     |  8680.00

База разложила строки по кучкам с одинаковым status и посчитала каждую кучку отдельно — девять статусов, девять строк итога. Порядок строк здесь такой, какой отдала база: ORDER BY в запросе нет, и в другой раз статусы могут выстроиться иначе. Нужен предсказуемый порядок — допишите ORDER BY status или ORDER BY count(*) DESC. Группировать можно по нескольким столбцам: GROUP BY status, customer_id — «по каждому статусу, а внутри него по каждому клиенту».

Главное правило GROUP BY: в SELECT могут стоять только столбцы из GROUP BY и агрегатные функции. Запрос SELECT status, total_amount … GROUP BY status не выполнится — база резонно спросит: «в кучке „PAID" два разных total_amount, который из них показывать?»

Одно послабление в PostgreSQL всё же есть: если группировать по первичному ключу таблицы, её остальные колонки в SELECT писать можно. Угадывать тут нечего — на один c.id приходится ровно одна фамилия, база это знает и не спорит:

живой пример

SELECT c.id, c.last_name, COUNT(*)
FROM customer c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Во всех остальных случаях правило действует буквально, так что привыкать лучше к нему.

Чего в результате не будет — групп, которых нет в данных. GROUP BY customer_id по таблице заказов покажет только тех, у кого заказы есть: cus-07 и cus-08 не появятся нулём, они не появятся вовсе. Это самая частая причина жалобы «в отчёте кого-то не хватает», и ошибка молчаливая — строка просто отсутствует, а не помечена. Чтобы в выдаче были все, группируют от той таблицы, где лежит полный список, а вторую присоединяют через LEFT JOIN:

живой пример

SELECT c.id, c.last_name, COUNT(o.id) AS orders_count
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.last_name
ORDER BY orders_count;

Здесь важна деталь: COUNT(o.id), а не COUNT(*). Звёздочка считает строки, а у покупателя без заказов строка после LEFT JOIN всё равно одна — с пустыми колонками заказа, — и он получил бы в отчёте единицу вместо нуля. COUNT по колонке пустые значения пропускает и честно даёт ноль.

Отработать здесь: Заказы и выручка по продавцам · Заказы в разрезе статусов

HAVING: фильтр после подсчётаспросят на собеседовании

Напишите WHERE COUNT(*) > 5, и PostgreSQL откажет: aggregate functions are not allowed in WHERE. WHERE фильтрует строки до группировки, а считать ещё нечего. А если условие — на сам результат подсчёта («покажи клиентов, у которых больше пяти заказов»)? Для этого есть HAVING — фильтр после подсчёта:

живой пример

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Они спокойно живут вместе, и запомнить просто: WHERE — про строки, HAVING — про группы. Например, WHERE status = 'PAID' сначала оставит оплаченные, GROUP BY customer_id сгруппирует, HAVING SUM(total_amount) > 10000 оставит только крупных клиентов.

В HAVING живёт не только COUNT. Условие «клиенты, которые принесли больше двадцати тысяч» — это сумма:

живой пример

SELECT customer_id, SUM(total_amount) AS revenue
FROM orders
WHERE status IN ('PAID', 'COMPLETED', 'DELIVERED')
GROUP BY customer_id
HAVING SUM(total_amount) > 20000
ORDER BY revenue DESC;

Обратите внимание, что оба фильтра в одном запросе делают разную работу, и не только по смыслу. WHERE отсекает строки до группировки, то есть до самой тяжёлой части — базе остаётся считать меньше. HAVING фильтрует уже посчитанные группы. Поэтому условие, которое можно записать в WHERE, туда и записывают: при группировке по статусу HAVING status = 'PAID' формально сработает, но база сначала посчитает все девять групп и только потом выбросит восемь. В HAVING оставляют то, что без подсчёта не проверить.

Отработать здесь: Покупатели, которые заказывали больше двух раз

Деньги считают в NUMERIC, округляют в конце

AVG(total_amount) в примерах выше возвращает что-то вроде 2843.3333333333333333: шестнадцать знаков после запятой, потому что сумма в песочнице хранится как numeric, точный десятичный тип, и среднее считается точно. Это правильно, и это единственный тип для денег. float и double precision хранят двоичную дробь, в которой 0.1 не представляется точно: сумма тысячи чеков по 0.10 даёт 99.9999999999986, а отчёт за месяц расходится с бухгалтерией на копейки, которые никто не может найти.

Округляют только на выводе, последним шагом, и только то, что показывают:

живой пример

SELECT customer_id, round(avg(total_amount), 2) AS avg_check, sum(total_amount) AS total
FROM orders
GROUP BY customer_id;

round(x, 2) работает с numeric; для double precision его нет, и запрос упадёт с «function round(double precision, integer) does not exist», это первый признак, что колонка объявлена не тем типом. Округлять до суммирования нельзя: сумма округлённых слагаемых отличается от округлённой суммы, и отчёт «по позициям» перестанет сходиться с отчётом «по заказам».

Где это применяется

Агрегаты — это сверка миров: интерфейс говорит «у вас 17 заказов» — COUNT(*) по базе обязан сказать то же самое; отчёт насчитал выручку — SUM её подтверждает или опровергает. Плюс поиск аномалий одной строкой: GROUP BY email HAVING COUNT(*) > 1 находит дубли пользователей, MIN(created_at) отвечает «с какого года у нас данные», сравнение COUNT(*) и COUNT(email) мгновенно показывает, у скольких записей не заполнена почта.

Где спотыкаются начинающие:

• Ставят агрегат в WHERE (WHERE COUNT(*) > 5) — ошибка: условия на результат подсчёта живут в HAVING.
• Смешивают в SELECT агрегаты и «просто столбцы», которых нет в GROUP BY. База честно отказывается угадывать, какое из многих значений показать.
• Считают по задвоенным строкам после JOIN. Сначала убедитесь, что соединение не размножило строки, потом считайте — иначе SUM и COUNT врут в разы.

Коротко

• Пять функций: COUNT, SUM, AVG, MIN, MAX; COUNT(*) считает строки, COUNT(колонка) только не-NULL.
• GROUP BY схлопывает строки в группы: в SELECT могут быть только колонки группировки и агрегаты.
• WHERE фильтрует строки до подсчёта, HAVING группы после него.
• Деньги хранят в numeric, не во float; округляют round(x, 2) только на выводе.
• COUNT(DISTINCT …) отвечает на «сколько разных», а не «сколько строк»: заказов двадцать три, покупателей семь.
• Групп, которых нет в данных, в отчёте не будет: чтобы показать и пустых, группируют от полного списка через LEFT JOIN и считают COUNT(колонка), а не COUNT(*).

Что почитать дальше

• NULL и типы данных — почему COUNT и AVG пропускают NULL.
• Оконные функции — посчитать, не схлопывая строки.
• Подзапросы — агрегат внутри условия.