Причины неверных агрегатов (коротко)
- Двойная агрегация / мультипликация строк при JOIN: если вы соединяете таблицы «один‑ко‑многим» с другими «многие», строки детальной таблицы дублируются и суммы/COUNT суммируются несколько раз.
- Пример: если в таблице A одна запись, в B — $2$ строки, в C — $2$ строки, то обычный JOIN A–B–C даст $2\times 2=4$ строки вместо ожидаемых $2$.
- WHERE, превращающее LEFT JOIN в INNER JOIN: условие на поля правой таблицы в WHERE отбрасывает NULL и меняет семантику.
- Неправильное использование COUNT/COUNT(DISTINCT)/COUNT(column): $COUNT(\ast )$ считает строки, $COUNT(col)$ игнорирует NULL, $COUNT(DISTINCTcol)$ — уникальные значения.
- NULL‑поведение агрегатов: $SUM$ и $AVG$ игнорируют NULL; если вы хотите считать NULL как $0$, используйте COALESCE.
- GROUP BY включает лишние столбцы: групировка по ненужным столбцам дробит группы.
- Накладывающиеся агрегаты: агрегирование после объединения необработанных строк вместо предварительной агрегации в подзапросах/CTE.
Как исправить (шаблоны и приёмы)
1. Агрегируйте «детали» до JOIN:
- Вместо
SELECT a.id, SUM(d.amount) FROM a LEFT JOIN d ON d.a_id = a.id GROUP BY a.id
(если есть ещё JOIN’ы, приводящие к дублированию)
- Сделайте:
WITH d_sum AS (
SELECT a_id, SUM(amount) AS sum_amount FROM d GROUP BY a_id
)
SELECT a.id, COALESCE(d_sum.sum_amount, $0$) FROM a LEFT JOIN d_sum ON d_sum.a_id = a.id;
2. Используйте агрегаты с DISTINCT, когда нужно:
- $SUM(DISTINCTvalue)$ или $COUNT(DISTINCTid)$ — осторожно: медленно на больших данных.
3. Применяйте COALESCE для NULL в арифметике:
- $SUM(COALESCE(d.amount,0))$ — чтобы NULL не "скрывал" суммы.
4. Правильно помещайте фильтры:
- Фильтр по правой таблице при LEFT JOIN — ставьте в ON, если хотите сохранить строки левой таблицы:
ON d.a_id = a.id AND d.status = 'active'
(а не WHERE d.status = 'active')
5. Используйте условную агрегацию:
- SUM(CASE WHEN cond THEN value ELSE $0$ END)
6. Если нужна строка‑за‑строкой агрегация — применяйте оконные функции или DISTINCT ON/LATERAL:
- Можно взять первую строку с LATERAL или суммировать в подзапросе, чтобы избежать умножения.
7. Проверяйте GROUP BY — оставляйте только ключи/поля, которые действительно нужны для группировки.
Как тестировать и отлавливать ошибки
- Небольшой воспроизводимый набор: создайте тестовую выборку с известными числами (например, одна запись в A, $2$ в B, $2$ в C) и вручную посчитайте ожидаемое.
- Посмотреть «сырые» строки перед агрегированием:
- Выполните тот же JOIN без GROUP BY и SELECT * для визуального контроля дублей.
- Счётчики-дубликаты:
- Добавьте временную колонку marker = $1$ и проверьте SUM(marker) до/после агрегирования, чтобы увидеть умножение строк.
- Сравнение двух стратегий:
- Выполните «агрегировать сначала, затем JOIN» и «JOIN сначала, затем агрегировать» и сравните результаты.
- Проверки NULL:
- SELECT COUNT(*) FILTER (WHERE right.id IS NULL) — проверяет, сколько строк теряются после JOIN.
- Сравните $SUM(col)$ и $SUM(COALESCE(col,0))$.
- EXPLAIN/EXPLAIN ANALYZE: убедитесь, что план JOIN совпадает с ожиданием (left join vs nested loop и т.д.).
- Мелкие unit‑тесты запросов: для ключевых отчётов храните контрольные значения и автоматизированно сверяйте результаты после изменений.
- Логика шагов: делайте промежуточные CTE/временные таблицы и проверяйте каждый шаг (количество строк, суммарные значения), чтобы локализовать момент искажения.
Краткие контрольные вопросы при диагностике
- Соединяю ли я несколько «многие» таблиц одновременно? (возможна мультипликация)
- Есть ли условия на правой таблице в WHERE?
- Правильно ли использую COUNT/COUNT(DISTINCT) и COALESCE?
- Аггрегирую ли я на нужном этапе (до или после JOIN)?
Если нужно — могу разобрать конкретный SQL‑запрос и показать, где и как исправить.