От множества кортежей — к одному числу

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

Эти ответы выражаются через

Формально:

$\text{aggregate}: \text{multiset}(T) \rightarrow T'$

На входе — мультимножество значений (потому что в реальном SQL мы работаем с bag, не с set). На выходе — одно скалярное значение. Тип результата может отличаться от типа входа: AVG от INT вернёт NUMERIC, потому что среднее — это дробь.

Пять стандартных агрегатов

Запустим первое:

Обрати внимание: одна строка на выходе. Все 20 товаров «коллапсировали» в один кортеж с пятью атрибутами. Это и есть агрегация в чистом виде — без GROUP BY агрегат превращает всю таблицу в одну строку.

NULL-семантика: главная ловушка агрегатов

А теперь — самое важное правило, которое разделяет тех, кто понимает SQL, и тех, кто думает, что понимает. Все агрегаты, кроме COUNT(*), игнорируют NULL.

Это не баг, это следствие семантики NULL — «значение неизвестно». Если у клиента не указан birth_year, СУБД не может его сложить, не может усреднить, не может сравнить. Самое честное — пропустить.

Главная ловушка спрятана в AVG. Многие думают, что AVG = SUM / COUNT, и удивляются, когда получают «не то» среднее. Правильное правило: AVG(col) = SUM(col) / COUNT(col). То есть среднее делится на число не-NULL значений, а не на общее число строк.

Видишь разницу между avg_years (правильное среднее, делится на 11) и avg_if_we_divided_by_total (неправильное, делится на 12)? На одной строке NULL это маленькая ошибка, но на боевом датасете с 30% NULL твоё «среднее» становится бессмыслицей. Запомни: AVG уже всё посчитал правильно.

Есть ещё одно следствие: если все значения в столбце NULL, то SUM, AVG, MIN, MAX вернут NULL, а не ноль. А COUNT(col) вернёт 0. Это редко, но критично — если ты пишешь COALESCE(SUM(x), 0), чтобы при пустой выборке не падало, ты делаешь это именно из-за этой особенности.

COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)

Три формы COUNT — три разных вопроса.

• COUNT(*) — «сколько строк в группе?». Не смотрит на конкретный столбец, считает по существованию кортежа. Это самая быстрая форма.
• COUNT(col) — «сколько не-NULL значений в столбце col?». Может быть меньше, чем COUNT(*).
• COUNT(DISTINCT col) — «сколько уникальных не-NULL значений в col?». Самая дорогая форма — требует sort или hash.

В этой выборке COUNT(*) = COUNT(customer_id), потому что customer_id в orders — NOT NULL. А COUNT(DISTINCT customer_id) меньше — это число уникальных клиентов, у которых вообще есть заказы. Типичный analytics-вопрос «сколько уникальных пользователей сделали покупку в марте» — это именно COUNT(DISTINCT ...).

STRING_AGG и ARRAY_AGG — собрать значения в одно

Кроме пяти стандартных, в Postgres есть очень полезные «собирающие» агрегаты:

• STRING_AGG(col, separator) — склеивает значения столбца в одну строку через разделитель.
• ARRAY_AGG(col) — собирает значения в массив.

Оба особенно удобны вместе с GROUP BY (следующий урок), но работают и без него:

Опция ORDER BY внутри агрегата (STRING_AGG(col, sep ORDER BY ...)) гарантирует порядок — без неё порядок элементов в строке/массиве не определён, потому что мы всё ещё в реляционной модели, где порядок входных кортежей не зафиксирован.

И ещё одна полезная пара:

• BOOL_AND(predicate) — TRUE, если все значения TRUE. Аналог логического И по столбцу.
• BOOL_OR(predicate) — TRUE, если хотя бы одно TRUE.

Тонкие моменты

SUM на пустой выборке возвращает NULL, не 0. Это часто ломает дашборды: «выручка за день, когда заказов не было», ожидается 0, а возвращается NULL, и фронтенд показывает пустоту. Стандартное лекарство — COALESCE(SUM(amount), 0).

COUNT(*) на пустой выборке возвращает 0, а не NULL. Это единственное исключение — COUNT всегда возвращает целое число.

MIN и MAX работают не только с числами. С датами вернут самую раннюю/позднюю, со строками — лексикографически минимальную/максимальную. Это часто удобно для «найти последний заказ клиента» через MAX(placed_at).

AVG деньгами — будь осторожен с типом. В нашей схеме price_cents — это INT. AVG(price_cents) вернёт NUMERIC с длинным хвостом после запятой. Часто хочется округлить: ROUND(AVG(price_cents)::numeric, 2).

Что дальше

Сейчас мы агрегировали всю таблицу в одну строку. Реальные запросы обычно делают это по группам: «сумма заказов по каждому клиенту», «средняя цена в каждой категории», «число позиций в каждом заказе». Для этого нужен GROUP BY — следующий урок целиком про него.

Чек-лист

• Агрегаты превращают много значений в одно. Без GROUP BY сжимают всю таблицу в одну строку.
• SUM, AVG, MIN, MAX игнорируют NULL. На пустой выборке возвращают NULL.
• AVG(col) = SUM(col) / COUNT(col), не / COUNT(*). Среднее считается по не-NULL значениям.
• COUNT(*) — число строк, COUNT(col) — число не-NULL значений в col, COUNT(DISTINCT col) — число уникальных не-NULL значений.
• COUNT на пустой выборке возвращает 0, остальные — NULL. Используй COALESCE(SUM(x), 0) где нужно.
• STRING_AGG, ARRAY_AGG, BOOL_AND, BOOL_OR — расширенные агрегаты Postgres. С ORDER BY внутри гарантируют порядок.