Как не получить лгущий дашборд: 7 ошибок в SQL-агрегациях
Отчёт по месячной выручке совпадает с бухгалтерскими данными, но стоит построить тот же показатель в разрезе товаров - и сумма внезапно оказывается на треть выше. Оба запроса написаны одним разработчиком, прошли ревью и не вызывают ошибок базы данных. Разница лишь в том, что во втором появился `JOIN` с позициями заказа. Один заказ превратился в несколько строк, а сумма начала учитываться повторно.
В этом и заключается главная особенность SQL: база данных почти никогда не сообщает, что вы задали неправильный вопрос. Она честно возвращает результат именно для той логики, которая записана в запросе. Ошибка не обязательно приводит к исключению, предупреждению или падению отчёта - чаще она незаметно попадает в дашборд и обнаруживается только при ручной сверке.
Ниже разобраны семь типичных проблем, из-за которых ломаются SQL аналитика данных и разработка BI дашбордов. Первые три связаны с `NULL`, следующие - с неожиданным изменением количества строк, а последняя касается временных границ. Примеры воспроизводятся в DuckDB 1.5.5 и SQLite; одинаковое поведение объясняется стандартной трёхзначной логикой SQL и характерно также для PostgreSQL и MySQL. Дополнительные примеры и пояснения по теме собраны в материале о том, как находить ошибки в SQL агрегациях.
1. `COUNT(*)`, `COUNT(column)` и `COUNT(DISTINCT column)` - разные показатели
Предположим, в таблице заказов часть полей заполнена не полностью. У клиента без промокода `promo_code` равен `NULL`, а сумма заказа может отсутствовать, если её ещё не рассчитала платёжная система.
В такой ситуации четыре визуально похожих счётчика способны вернуть разные результаты:
- `COUNT(*)` считает все строки;
- `COUNT(column)` учитывает только строки, где указанная колонка не равна `NULL`;
- `COUNT(DISTINCT column)` считает уникальные непустые значения;
- `COUNT(DISTINCT CASE WHEN ... END)` дополнительно зависит от условий фильтрации.
Каждый результат может быть правильным - вопрос лишь в том, что именно требуется измерить. Если "количество заказов" считают через `COUNT(amount)`, все заказы без рассчитанной суммы исчезнут из статистики. Аналогичная проблема возникает при сравнении двух отчётов: один использует `COUNT(*)`, другой - `COUNT(id)`. Пока идентификатор заполнен всегда, различий не видно. Но после `LEFT JOIN` появляются строки с пустыми полями, и показатели расходятся.
2. `AVG` автоматически исключает пропуски
Среднее значение ведёт себя аналогично. Функция `AVG(amount)` не воспринимает `NULL` как ноль - она исключает такие строки из расчёта.
Если в таблице пять заказов, но сумма указана только у четырёх, SQL разделит сумму на четыре. Человек, который вручную сложит значения и поделит на пять, получит другой показатель.
Оба подхода могут быть оправданными. Если отсутствие суммы означает "заказ ещё не обработан", `AVG` работает корректно. Если же бизнес-правило трактует отсутствие значения как нулевую выручку, потребуется явное преобразование:
```sql
AVG(COALESCE(amount, 0))
```
Выбор нужно закрепить в методологии отчёта. Иначе проверка корректности данных в BI дашбордах будет постоянно выявлять расхождения, вызванные не ошибкой базы, а разным пониманием метрики.
3. Сравнение с `NULL` не даёт ни истинного, ни ложного результата
Рассмотрим клиентов, у одного из которых не указан регион. Запросы вроде этих выглядят естественно:
```sql
WHERE region = 'Север'
WHERE region <> 'Север'
```
Однако клиент с `NULL` не попадёт ни в одну выборку. В SQL выражение `NULL = 'Север'` возвращает не `FALSE`, а `UNKNOWN`. Аналогично, `NULL <> 'Север'` также даёт неопределённый результат. А оператор `WHERE` оставляет только строки, для которых условие истинно.
Из-за этого отчёт по регионам может внутренне выглядеть убедительно, но сумма всех групп окажется меньше общего количества клиентов или заказов. Расхождение часто обнаруживается спустя недели, когда итог начинают сверять с бухгалтерией или CRM.
Чтобы поведение было явным, пропуски можно выделить в отдельную категорию:
```sql
COALESCE(region, 'Не указан')
```
Другой вариант - запретить `NULL` на уровне схемы и обязать систему заполнять регион при создании записи.
4. `NOT IN` ломается из-за одного `NULL`
Особенно опасна конструкция:
```sql
WHERE customer_id NOT IN (11, NULL)
```
На первый взгляд она должна выбрать всех клиентов, кроме клиента с идентификатором 11. Но SQL интерпретирует условие как:
```sql
customer_id <> 11
AND customer_id <> NULL
```
Вторая часть возвращает `UNKNOWN`, поэтому всё выражение не становится истинным ни для одной строки. В итоге запрос возвращает пустой результат.
Надёжнее использовать `NOT EXISTS`:
```sql
WHERE NOT EXISTS (
SELECT 1
FROM blocked_clients b
WHERE b.customer_id = c.customer_id
)
```
Либо заранее исключать `NULL` из подзапроса. При этом `IN` обычно ведёт себя менее неожиданно: если найдено хотя бы одно истинное совпадение, наличие `NULL` в списке не мешает вернуть строку. Тем не менее полагаться на неявное поведение не стоит - фильтры лучше формулировать явно.
5. `JOIN` может размножить строки
Самая заметная причина завышенной выручки - соединение таблиц с отношением "один ко многим". В таблице заказов одна строка соответствует заказу, а в таблице позиций этому заказу могут соответствовать три, пять или десять записей.
Если после соединения выполнить:
```sql
SUM(order.amount)
```
сумма заказа будет прибавлена столько раз, сколько у него позиций. Поэтому перед агрегацией нужно понимать гранулярность данных: что представляет собой одна строка результата - заказ, товарную позицию, клиента или событие.
Исправить проблему можно несколькими способами:
- сначала агрегировать позиции до уровня заказа;
- суммировать стоимость позиций вместо суммы заказа;
- использовать `SUM(DISTINCT ...)` только при гарантированной уникальности значения;
- проверять количество строк до и после каждого `JOIN`.
Последний метод особенно полезен при отладке: рост числа строк после соединения не всегда ошибочен, но он должен быть ожидаемым и объяснимым.
6. `LEFT JOIN` легко превратить в `INNER JOIN`
`LEFT JOIN` сохраняет строки из левой таблицы, даже если соответствия справа нет. Но фильтр по правой таблице в секции `WHERE` меняет это поведение:
```sql
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id
WHERE p.status = 'paid'
```
Заказы без платежа получают `NULL` в полях `p`, а условие `p.status = 'paid'` их отбрасывает. Фактически запрос начинает работать как `INNER JOIN`.
Если нужно сохранить все заказы и ограничить только присоединяемые платежи, условие следует перенести в `ON`:
```sql
FROM orders o
LEFT JOIN payments p
ON p.order_id = o.id
AND p.status = 'paid'
```
Такая ошибка особенно часто появляется в отчётах о конверсии, возвратах и оплатах, где отсутствие события - самостоятельный бизнес-факт, а не лишняя строка.
7. Среднее по группам не равно общему среднему
Допустим, у одного канала 100 посетителей и конверсия 10%, а у другого - 10 000 посетителей и конверсия 2%. Простое среднее двух процентов даст 6%, хотя реальная конверсия по всей аудитории будет близка к 2,08%.
Проблема возникает, когда сначала рассчитывают конверсию по сегментам, а затем усредняют получившиеся значения:
```sql
AVG(conversion_rate)
```
Корректнее считать общие числитель и знаменатель:
```sql
SUM(converted_users) * 1.0 / SUM(total_users)
```
Или использовать взвешенное среднее, если структура данных требует именно этого. В BI-дашбордах агрегировать нужно не только готовые проценты, но и исходные количества - так меньше риск получить красивую, но ложную цифру.
8. `BETWEEN` может потерять события последнего дня
Для дат без времени конструкция `BETWEEN '2025-01-01' AND '2025-01-31'` часто выглядит безопасно. Но если поле хранит timestamp, верхняя граница обычно интерпретируется как начало 31 января - `2025-01-31 00:00:00`. Все события, произошедшие позже, будут исключены.
Надёжный вариант - использовать полуоткрытый интервал:
```sql
WHERE created_at >= '2025-01-01'
AND created_at < '2025-02-01'
```
Так в выборку попадает весь последний день, включая 23:59:59 и доли секунды. Это особенно важно для ежедневных отчётов, финансовых периодов и расчёта KPI.
Как проверять агрегаты до публикации
Перед выпуском отчёта полезно тестировать не только синтаксис SQL, но и инварианты данных: сумма по группам должна совпадать с общим итогом, количество строк после `JOIN` должно быть объяснимым, а доля `NULL` - контролируемой. Для ключевых показателей стоит подготовить небольшой набор эталонных данных, где заранее известен правильный результат.
Не менее важна фиксация гранулярности. В документации к метрике нужно указывать, что считается единицей измерения: заказ, пользователь, сессия или позиция. Без этого даже корректный запрос легко использовать не по назначению.
Наконец, полезно регулярно сравнивать показатели между независимыми системами и запускать автоматические проверки на выбросы. Если выручка внезапно выросла на 30%, а количество заказов осталось прежним, это повод проверить `JOIN`, `NULL`, временные фильтры и уровень агрегации ещё до того, как цифра попадёт руководству. Практические приёмы для оптимизации SQL запросов и контроля агрегатов помогают сделать такие проверки частью обычного процесса разработки.
SQL не пытается ввести аналитика в заблуждение - он лишь последовательно выполняет заданные правила. Поэтому надёжный дашборд начинается не с красивой визуализации, а с точного определения метрики, понимания структуры данных и системной проверки каждого шага расчёта.
