Краткий ответ

Причина — размножение строк при соединении: один заказ соединяется с несколькими позициями, поэтому его сумма повторяется в результирующем наборе перед выполнением агрегата. Если затем суммировать поле заказа, одна и та же величина будет учтена столько раз, сколько строк позиции ей соответствует.

Исторический контекст

Реляционная модель позволяет сначала соединять отношения, а затем группировать полученный результат. В SQL при этом обычно сохраняются повторяющиеся строки: результат имеет свойства множества строк с дубликатами, а не математического множества уникальных записей.

Такой порядок удобен для построения аналитических запросов, но требует явно контролировать зернистость данных — что именно представляет одна строка на каждом этапе запроса.

Постановка проблемы

Пусть таблица заказов содержит одну строку на заказ, а таблица позиций — несколько строк на один заказ. После соединения одна строка заказа превращается в несколько строк, по одной на каждую позицию.

Если сумма заказа хранится в таблице заказов и агрегируется после такого соединения, итог зависит не только от заказов, но и от количества их позиций. Заказ с пятью позициями повлияет на сумму в пять раз сильнее заказа с одной позицией.

Подробное решение

Нужно сопоставить агрегат с правильной зернистостью. Если требуется сумма по заказам, сначала следует получить одну строку на заказ, а затем выполнять соединение с таблицами, которые могут размножить строки, либо агрегировать уже подготовленные данные.

Минимальный пример предварительной агрегации:

WITH item_totals AS (
    SELECT order_id, SUM(quantity * price) AS item_total
    FROM order_items
    GROUP BY order_id
)
SELECT o.customer_id, SUM(i.item_total) AS revenue
FROM orders AS o
JOIN item_totals AS i ON i.order_id = o.order_id
GROUP BY o.customer_id;

Здесь таблица позиций сначала сворачивается до одной строки на заказ. Поэтому последующее соединение не повторяет сумму заказа из-за количества позиций.

SUM(DISTINCT order_total) иногда устраняет повторение, но это не универсальное исправление: два разных заказа могут иметь одинаковую сумму, и тогда один из них будет ошибочно исключён. Аналогично, удаление дубликатов после соединения безопасно только при доказанной семантической эквивалентности строк, а не просто потому, что результат выглядит правильнее.

Другой вариант — агрегировать непосредственно поля таблицы позиций, если именно они являются источником выручки. Это обычно надёжнее, чем суммировать рассчитанную сумму заказа, но требует проверить правила округления, скидок, налогов и возвратов.

Ситуация из практики

В отчёте по клиентам сумма заказов стала выше суммы в финансовой системе после добавления количества проданных товаров. Анализ показал, что заказ соединяли с позициями, а затем суммировали одновременно сумму заказа и количество позиций.

Рассматривались три варианта. SUM(DISTINCT order_total) был отклонён: одинаковые суммы у разных заказов дали бы занижение. Удаление дубликатов после соединения тоже было рискованным, поскольку позиции содержали разные характеристики товара. Выбранным решением стала предварительная агрегация позиций до уровня заказа, после чего финансовые показатели агрегировались по клиенту.

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

Что кандидаты часто упускают

Всегда ли предварительная агрегация устраняет завышение?

Нет. Она устраняет размножение только относительно той связи, которую предварительно свернули. Если после этого заказ соединяется ещё с другой таблицей «один-ко-многим», например с платежами или возвратами, строки могут снова размножиться. Нужно проверять зернистость после каждого соединения и при необходимости заранее агрегировать каждую многозначную сторону.

Почему COUNT(*) после такого соединения тоже может быть неверным?

COUNT(*) считает строки результата соединения, а не исходные заказы. Поэтому он фактически считает пары «заказ–позиция», если один заказ связан с несколькими позициями. Для подсчёта заказов используют COUNT(DISTINCT order_id) либо предварительно формируют набор с одной строкой на заказ; выбор зависит от того, нужно ли считать уникальные идентификаторы или уже подготовленные бизнес-сущности.

Когда допустимо агрегировать после соединения без предварительной свёртки?

Это допустимо, если агрегируемый показатель относится к стороне, которая не размножается, либо если повторение не влияет на нужный результат. Например, сумму стоимости позиций можно считать после соединения с таблицей заказов, если соединение не создаёт дополнительных копий каждой позиции. Однако это требует гарантии кардинальности: связь должна быть один-к-одному или многие-к-одному относительно агрегируемых строк. Одного визуального отсутствия дубликатов в тестовой выборке недостаточно.