Этот урок — про неправильные числа

В предыдущих уроках мы построили модель: агрегаты, GROUP BY, HAVING, FILTER, multi-level. Теперь время поговорить о том, что чаще всего ломается в реальной жизни. Все ловушки этого урока — реальные баги, которые крадут деньги, ломают дашборды и заставляют data engineers ночью отлаживать отчёты.

Главное правило отладки агрегации: если число выглядит подозрительно большим — почти всегда виноват JOIN, размноживший строки. Поехали.

Ловушка 1: JOIN до GROUP BY размножает строки

Это самая частая и самая болезненная ошибка. Представь задачу: «общая выручка по каждому клиенту». Очевидное решение:

Если у заказа 3 позиции (order_items) и 1 платёж (payments), то после JOIN он появится 3 раза. Каждое появление умножит платёж: вместо одного payments.amount_cents мы суммируем три копии. payments_rub будет завышен в 3 раза.

Правильное решение — агрегировать каждую таблицу отдельно и потом склеивать:

Каждый подзапрос группирует своё, на своей грануляции, без размножения. Потом эти уже-агрегированные результаты склеиваются по customer_id. Альтернатива — WITH (CTE), о которой мы поговорим в модуле 7.

Правило: если в одном GROUP BY собираешь агрегаты из таблиц с разной грануляцией — почти всегда нужны подзапросы или DISTINCT.

Ловушка 2: COUNT(*) после JOIN — то же самое

Аналогично, COUNT(*) после JOIN считает не «сколько заказов», а «сколько пар (заказ, позиция)»:

Лекарство — COUNT(DISTINCT o.id), чтобы считать уникальные заказы, а не пары:

COUNT(DISTINCT o.id) теперь правильное число заказов. COUNT(*) — это число позиций (что тоже может быть полезно, но это другая метрика).

Ловушка 3: AVG(a) + AVG(b) ≠ AVG(a + b)

Математически очевидно, но в SQL легко забыть. Допустим, тебе нужна «средняя стоимость позиции» = qty * unit_price. Соблазн написать AVG(qty) * AVG(unit_price_cents) — это неверно:

Среднее произведения не равно произведению средних. Та же ловушка с суммой/средним: AVG(a + b) обычно равно AVG(a) + AVG(b), но только когда нет NULL в обоих столбцах. Если в a есть NULL, а в b нет, то знаменатели у AVG(a) и AVG(b) разные, и сумма не сойдётся.

Правило: всегда агрегируй выражение, которое тебе на самом деле нужно. Не разбивай агрегат на куски в надежде, что арифметика средних работает интуитивно.

Ловушка 4: SUM с NULL в выражении

Если в выражении внутри SUM есть NULL, всё выражение становится NULL — и эта строка молча выпадает из суммы. Это часто незаметно:

Видишь: rows_total больше, чем non_null_product — на 1 строку. Эта строка с NULL «потерялась». Если ожидалось, что NULL — это 0, надо явно: SUM(COALESCE(qty, 0) * COALESCE(unit_price_cents, 0)). Но чаще «отсутствие qty» — это бизнес-проблема, и её надо лечить на уровне данных, а не маскировать в запросе.

Ловушка 5: фильтр в WHERE при OUTER JOIN превращает его в INNER

Очень частая беда:

После LEFT JOIN у клиента без заказов o.status = NULL. Условие o.status = 'delivered' возвращает NULL, что не TRUE — строка отбрасывается. Клиент исчезает из результата, хотя мы ожидали увидеть delivered_orders = 0.

Правильное решение — перенести фильтр в ON:

Теперь клиенты без delivered-заказов остаются с COUNT(o.id) = 0. К этой ловушке мы вернёмся в модуле 5 (JOIN’ы), но в контексте агрегации она встречается особенно часто.

Ловушка 6: алиас в SELECT нельзя использовать в WHERE/GROUP BY

Логический порядок обработки SQL: FROM → WHERE → GROUP BY → SELECT → ORDER BY. К моменту обработки WHERE алиасы из SELECT ещё не существуют:

Лечится тремя способами: HAVING SUM(oi.qty * oi.unit_price_cents) > 100000, или подзапрос, или CTE. В ORDER BY алиас уже разрешён, потому что SELECT отрабатывает раньше.

Ловушка 7: пустая выборка → NULL, не 0

SUM, AVG, MIN, MAX на пустой выборке возвращают NULL. На дашбордах это часто превращается в «пустую ячейку», где ожидалось 0.

COUNT дал 0, SUM — NULL, COALESCE спас. Это особенно важно, когда агрегация идёт через FILTER: SUM(amount) FILTER (WHERE type='income') для клиента без income-операций вернёт NULL.

Ловушка 8: GROUP BY по выражению vs по позиции

PostgreSQL разрешает GROUP BY 1, 2 — это группировка по первой и второй колонке SELECT. Удобно, но опасно: добавил кто-то колонку в SELECT посередине — и GROUP BY 1, 2 теперь группирует по другим колонкам.

Правило стиля: для одноразовых ad-hoc запросов GROUP BY 1, 2 — нормально. Для долгоживущего кода (миграции, dbt, продакшен-запросы) — пиши имена явно.

Финальный чек-лист для агрегационных запросов

Перед тем как поверить в результат запроса с GROUP BY, прогони его по списку:

1. Числа выглядят подозрительно большими? Проверь грануляцию: где JOIN, где 1:N, не ли где 1:N:M. Если есть размножение — переделай через подзапросы.
2. COUNT(*) после JOIN? Скорее всего нужен COUNT(DISTINCT pk). Уточни, что именно ты считаешь.
3. AVG или SUM на колонках с NULL? Убедись, что семантика «пропустить» — это то, что нужно. Если нет — COALESCE.
4. LEFT JOIN + WHERE по правой таблице? Перенеси фильтр в ON, иначе LEFT превратится в INNER.
5. Алиас из SELECT используется в WHERE или GROUP BY? Не работает — повтори выражение или используй CTE.
6. SUM может вернуть NULL? COALESCE(SUM(x), 0) — почти всегда правильно для отчётов.
7. GROUP BY 1, 2? Для долгоживущего кода замени на имена колонок.

Чек-лист модуля

• JOIN с таблицами разной грануляции размножает строки и портит SUM/COUNT(*). Лечится подзапросами или COUNT(DISTINCT).
• AVG(a) * AVG(b) ≠ AVG(a * b). Среднее не дистрибутивно — всегда агрегируй конечное выражение.
• NULL в выражении внутри SUM/AVG обнуляет всю строку выражения. Используй COALESCE, если NULL — это бизнес-ноль.
• WHERE по правой части LEFT JOIN превращает его в INNER JOIN. Фильтры по nullable-стороне идут в ON.
• Алиасы из SELECT доступны в ORDER BY и (в Postgres) в HAVING, но не в WHERE/GROUP BY.
• Агрегаты на пустой выборке: COUNT = 0, остальные = NULL. COALESCE(SUM(x), 0) — стандартное лекарство для отчётов.