COUNT, SUM, AVG: read muutuvad arvudeks

3. peatükk — „Poe pulss”

„Analüütiku” luba langeb su läbipääsuloale sooja merevaigukollase triibuna. K. krüpteeritud plokk ei avanenudki, kuid selle pealkirja tead sa peast: „Toored read on müra. Mõte tekib siis, kui oskad neid kokku liita.” QUERY kustutab üleliigsed holopaneelid ja jätab alles ühe — tühja, mille keskel pulseerib üksainus punkt.

QUERY: Sa lugesid arhiivi rida-realt. Aitab. Täna kuulame selle südant.

Agregaatfunktsioonid: palju ridu → üks arv

Tavaline SELECT näitab ridu nii, nagu need on: tellimus tellimuse järel, kaup kauba järel. Agregaatfunktsioon surub reade hulga näitajaks, mille saab juba valjusti välja öelda: mitu tellimust, milline käive, milline keskmine tšekk. Arhivaari jaoks ongi see pulss: üks arv, milles kostab kogu vool.

Viis töökat tegijat:

Kui SELECT-is ei ole GROUP BY-d, surub agregaat kogu tabeli (või selle, mis pärast WHERE-i alles jääb) üheks reaks.

SELECT COUNT(*) AS заказов, SUM(total_amount) AS выручка
FROM orders
WHERE status = 'paid';

Siin jätab kõigepealt WHERE alles ainult makstud tellimused ja seejärel arvutavad agregaadid selle hulga kohta kokkuvõtte. Nii saad sa mitte toore loendi, vaid lühikese koondi — Kotomarketi esimese pulsilöögi.

Tähtis: ühes SELECT-is ei tohi segada agregaati ja tavalist veergu ilma GROUP BY-ta. Päring SELECT status, COUNT(*) FROM orders annab vea — SQL ei hakka arvama, milline status üldarvu kõrval näidata.

До этого момента ты в основном доставал строки.

Например:

SELECT id, user_id, status, total_amount
FROM orders
WHERE status = 'paid';

Такой запрос показывает оплаченные заказы один за другим:

Это полезно, если нужно посмотреть сами строки.

Но часто бизнесовый вопрос звучит иначе:

Сколько было оплаченных заказов?
Какая общая выручка?
Какой средний чек?
Какой был минимальный и максимальный чек?
Сколько разных покупателей сделали заказ?

На такие вопросы не нужен длинный список строк. Нужна сводка.

Для этого в SQL есть агрегатные функции.

Агрегатная функция берёт набор строк и сворачивает его в одно итоговое значение.

Например:

SELECT COUNT(*) AS orders_count
FROM orders;

Этот запрос не показывает каждый заказ отдельно. Он возвращает одно число: сколько строк есть в таблице orders.

Агрегатные функции: много строк → одно значение

Агрегаты превращают поток строк в показатель, который можно произнести вслух.

Не:

заказ 101
заказ 102
заказ 103
заказ 104

А:

всего 4 заказа

Не:

1200
3500
800
2100

А:

общая сумма 7600
средний чек 1900
минимальный чек 800
максимальный чек 3500

Пять основных агрегатных функций:

Например:

SELECT
    COUNT(*) AS orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders;

Если в запросе нет GROUP BY, агрегаты считают итог по всему набору строк, который дошёл до них.

То есть результатом будет одна строка:

COUNT: сколько строк

COUNT отвечает на вопрос «сколько?».

Самая частая форма:

COUNT(*)

Она считает строки.

Например:

SELECT COUNT(*) AS orders_count
FROM orders;

Результат:

Звёздочка в COUNT(*) не означает «выбери все столбцы». Здесь она означает:

считай сами строки.

COUNT(*) не смотрит, какие значения лежат внутри столбцов. Ему важно только, существует строка или нет.

Поэтому COUNT(*) подходит, когда вопрос звучит так:

Сколько всего заказов?
Сколько всего товаров?
Сколько всего пользователей?
Сколько строк осталось после фильтра?

Например, сколько оплаченных заказов:

SELECT COUNT(*) AS paid_orders_count
FROM orders
WHERE status = 'paid';

Здесь сначала WHERE оставляет только оплаченные заказы, а потом COUNT(*) считает оставшиеся строки.

SUM, AVG, MIN и MAX

SUM считает сумму.

SELECT SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid';

Так можно получить выручку по оплаченным заказам.

AVG считает среднее значение.

SELECT AVG(total_amount) AS avg_order_amount
FROM orders
WHERE status = 'paid';

Так можно получить средний чек по оплаченным заказам.

MIN ищет минимальное значение.

SELECT MIN(total_amount) AS min_order_amount
FROM orders
WHERE status = 'paid';

MAX ищет максимальное значение.

SELECT MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'paid';

Все эти функции можно использовать вместе:

SELECT
    COUNT(*) AS paid_orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'paid';

Так один запрос возвращает сводку по оплаченным заказам.

WHERE сначала отбирает строки, потом агрегат считает

Важно понимать порядок мысли.

В запросе:

SELECT
    COUNT(*) AS paid_orders_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'paid';

сначала работает фильтр:

WHERE status = 'paid'

Он оставляет только оплаченные заказы.

И только потом агрегаты считают итог по оставшимся строкам:

COUNT(*)
SUM(total_amount)

То есть запрос не считает все заказы, а потом как-то помечает оплаченные. Он сначала убирает лишние строки, а затем считает итог.

Это удобно для любых уточнений.

Выручка только по оплаченным заказам:

WHERE status = 'paid'

Количество отменённых заказов:

WHERE status = 'cancelled'

Средний чек за определённый период:

WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01'

Сначала формируется набор строк. Потом агрегат превращает его в число.

КВЕРИ: Прежде чем слушать пульс, выбери, чьё сердце слушаешь. Все заказы, оплаченные заказы и отменённые заказы звучат по-разному.

COUNT(*), COUNT(col), COUNT(DISTINCT col)

У COUNT есть несколько форм, и они отвечают на разные вопросы.

Разберём на маленьком примере.

Пусть есть таблица visits:

Запрос:

SELECT
    COUNT(*) AS rows_count,
    COUNT(promo_code) AS with_promo_code,
    COUNT(DISTINCT promo_code) AS different_promo_codes
FROM visits;

вернёт:

Почему так?

COUNT(*) считает все строки.

В таблице 5 строк, значит результат — 5.

COUNT(promo_code) считает строки, где promo_code заполнен.

Значения NULL он не считает. Заполненные промокоды есть в трёх строках:

SALE
SALE
VIP

Значит результат — 3.

COUNT(DISTINCT promo_code) считает разные заполненные значения.

Из заполненных значений:

SALE
SALE
VIP

разных только два:

SALE
VIP

Значит результат — 2.

Итого:

COUNT(*)                 → сколько строк
COUNT(promo_code)        → у скольких строк промокод заполнен
COUNT(DISTINCT promo_code) → сколько разных заполненных промокодов

Это три разных вопроса к данным, поэтому ответы могут быть разными.

Агрегаты и NULL

Общее правило:

агрегатные функции обычно игнорируют NULL.

Например, есть значения:

SUM(total_amount) сложит только заполненные значения:

1000 + 2000 = 3000

AVG(total_amount) тоже возьмёт только заполненные значения:

(1000 + 2000) / 2 = 1500

Не так:

(1000 + 2000 + NULL) / 3

MIN и MAX тоже ищут минимум и максимум среди заполненных значений.

Единственное важное исключение — COUNT(*).

COUNT(*) считает строки, а не значение конкретного столбца, поэтому NULL внутри столбцов его не волнует.

Сравни:

SELECT
    COUNT(*) AS rows_count,
    COUNT(total_amount) AS filled_amounts,
    SUM(total_amount) AS total_sum,
    AVG(total_amount) AS avg_amount
FROM orders;

COUNT(*) ответит на вопрос:

сколько строк

COUNT(total_amount) ответит на вопрос:

у скольких строк заполнен total_amount

Это не одно и то же.

Если после WHERE не осталось строк

Иногда фильтр может не найти ни одной строки.

Например:

SELECT
    COUNT(*) AS orders_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_amount,
    MIN(total_amount) AS min_order_amount,
    MAX(total_amount) AS max_order_amount
FROM orders
WHERE status = 'status_that_does_not_exist';

Если таких заказов нет, результат всё равно будет одной строкой, потому что агрегат без GROUP BY возвращает одну сводку по выбранному набору.

Но значения будут разными:

COUNT(*) вернёт 0, потому что строк нет.

А SUM, AVG, MIN и MAX вернут NULL, потому что им нечего суммировать, усреднять или сравнивать.

Если в отчёте вместо NULL нужна нулевая выручка, можно использовать COALESCE:

SELECT
    COUNT(*) AS orders_count,
    COALESCE(SUM(total_amount), 0) AS revenue
FROM orders
WHERE status = 'status_that_does_not_exist';

Это не меняет данные в таблице. Просто в результате запроса вместо пустого итога показывается 0.

COUNT(*) и COUNT(1)

Иногда в чужих запросах можно встретить:

COUNT(1)

Например:

SELECT COUNT(1)
FROM orders;

В PostgreSQL для обычного подсчёта строк это по смыслу то же самое, что:

SELECT COUNT(*)
FROM orders;

Почему?

Потому что 1 — это выражение, которое не бывает NULL. В каждой строке оно существует, значит COUNT(1) считает каждую строку.

Но для читаемости лучше писать:

COUNT(*)

Так сразу видно, что ты считаешь строки.

Миф о том, что COUNT(1) обязательно быстрее COUNT(*), для PostgreSQL не является полезным правилом. На этом уровне запомни проще:

нужны строки — пиши COUNT(*).

Почему обычный столбец рядом с агрегатом даёт ошибку

Посмотри на запрос:

SELECT status, COUNT(*)
FROM orders;

Кажется, что он должен показать статус и количество заказов.

Но без GROUP BY такой запрос падает с ошибкой.

Почему?

COUNT(*) без GROUP BY сворачивает все выбранные строки в одну итоговую строку.

Например:

А status — это обычный столбец. В таблице у разных заказов разные статусы:

Если весь набор строк схлопнулся в одну строку результата, какой status SQL должен показать рядом с общим количеством?

paid?
pending?
cancelled?

SQL не угадывает. Он требует, чтобы ты явно описал логику.

Если нужен общий счётчик всех заказов — убери status:

SELECT COUNT(*) AS orders_count
FROM orders;

Если нужно количество заказов по каждому статусу — добавь GROUP BY:

SELECT status, COUNT(*) AS orders_count
FROM orders
GROUP BY status;

Тогда результат будет не одна общая строка, а отдельная строка на каждый статус:

GROUP BY подробно разберём дальше. Пока важно понять саму причину ошибки:

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

КВЕРИ: Сырые строки показывают события. Агрегаты показывают пульс: сколько, на какую сумму, в каких границах и с каким средним ритмом.