COUNT, SUM, AVG: rijen worden getallen

Hoofdstuk 3 — «De pols van de winkel»

De toegang «Analist» legt zich als een warme amberkleurige streep op je pas. Het versleutelde blok van K. ging nooit open, maar de kop ervan ken je uit je hoofd: «Ruwe rijen zijn ruis. Betekenis ontstaat zodra je ze kunt optellen». QUERY dimt de overbodige holopanelen en laat er één over — leeg, met een eenzame pulserende stip in het midden.

QUERY: Je hebt het archief regel voor regel gelezen. Genoeg. Vandaag luisteren we naar zijn hart.

Aggregaatfuncties: veel rijen → één getal

Een gewone SELECT toont de rijen zoals ze zijn: bestelling na bestelling, product na product. Een aggregaatfunctie perst een verzameling rijen samen tot een cijfer dat je hardop kunt uitspreken: hoeveel bestellingen, hoeveel omzet, wat de gemiddelde bestelwaarde is. Voor een archivaris is dat de pols: één getal waarin de hele stroom doorklinkt.

De vijf werkpaarden:

Als er geen GROUP BY in de SELECT staat, vouwt het aggregaat de hele tabel (of wat er na WHERE overblijft) samen tot één rij.

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

Hier houdt WHERE eerst alleen de betaalde bestellingen over, en daarna berekenen de aggregaten het totaal over die verzameling. Zo krijg je geen ruwe lijst, maar een korte samenvatting — de eerste hartslag van «Kotomarket».

Belangrijk: je mag in één SELECT geen aggregaat en een gewone kolom mengen zonder GROUP BY. De query SELECT status, COUNT(*) FROM orders loopt vast met een fout — SQL gaat niet raden welke status het naast het totaalcijfer moet tonen.

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

Например:

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 не выбирает одно значение за тебя.

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