Как JOIN незаметно умножает суммы в SQL Server

Отчет соединяет заказы, позиции и платежи, группирует по заказу и возвращает одну строку на заказ. Форма выглядит правильно, но суммы завышены. GROUP BY скрывает дополнительные комбинации, не отменяя их влияние на SUM. Главный вопрос заключается в том, какую бизнес-сущность представляет одна строка каждого входа.

Посчитать комбинации до суммирования

Заказ с двумя позициями и тремя платежами дает шесть комбинаций, если оба дочерних набора независимо соединены по OrderId. Каждая позиция появляется три раза, каждый платеж дважды. База выполняет указанные условия, а не случайно дублирует сохраненные записи.

У обеих позиций в примере намеренно одинаковая сумма. Это одновременно показывает ошибочность исправления через SUM(DISTINCT Amount).

DECLARE @Orders TABLE (OrderId int PRIMARY KEY);
DECLARE @Lines TABLE
(LineId int PRIMARY KEY, OrderId int, Amount decimal(12,2));
DECLARE @Payments TABLE
(PaymentId int PRIMARY KEY, OrderId int, Amount decimal(12,2));

INSERT @Orders VALUES (1), (2);
INSERT @Lines VALUES (11, 1, 10), (12, 1, 10);
INSERT @Payments VALUES (21, 1, 5), (22, 1, 7), (23, 1, 8);

SELECT o.OrderId,
       SUM(l.Amount) AS WrongLineTotal,
       SUM(p.Amount) AS WrongPaymentTotal
FROM @Orders AS o
LEFT JOIN @Lines AS l ON l.OrderId = o.OrderId
LEFT JOIN @Payments AS p ON p.OrderId = o.OrderId
GROUP BY o.OrderId;

;WITH L AS
(
    SELECT OrderId, SUM(Amount) AS LineTotal
    FROM @Lines GROUP BY OrderId
),
P AS
(
    SELECT OrderId, SUM(Amount) AS PaymentTotal
    FROM @Payments GROUP BY OrderId
)
SELECT o.OrderId,
       COALESCE(l.LineTotal, 0) AS LineTotal,
       COALESCE(p.PaymentTotal, 0) AS PaymentTotal
FROM @Orders AS o
LEFT JOIN L AS l ON l.OrderId = o.OrderId
LEFT JOIN P AS p ON p.OrderId = o.OrderId
ORDER BY o.OrderId;

Для заказа 1 первый запрос возвращает сумму позиций 60 и платежей 40. Правильные значения равны 20 и 20. Второй запрос сначала сворачивает каждый дочерний набор до одной строки заказа. Заказ 2 сохраняется с нулями, поскольку в выбранной трактовке отсутствие позиций и платежей означает ноль.

SUM(DISTINCT l.Amount) дал бы 10, а не 20. Он удаляет равные значения, а не дополнительные появления конкретной позиции. Две настоящие позиции могут иметь одинаковую цену. Финальный SELECT DISTINCT тоже не исправляет уже вычисленную завышенную сумму.

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

Агрегировать на нужном уровне

Исправленные CTE обеспечивают не больше одной строки на OrderId за счет GROUP BY. Их названия не создают автоматическую материализацию; безопасность дает логическая группировка. Производные таблицы или подходящие агрегирующие APPLY могут выражать ту же идею.

Одного OrderId бывает недостаточно. Отчет по заказу и валюте не должен складывать разные валюты в одну сумму. Отчет по продукту требует деталей, уже потерянных при свертке только по заказу. Сначала запишите ожидаемый ключ выходной строки.

Применяйте фильтр к правильной мере. Если нужен весь объем заказа, но только проведенные платежи, отфильтруйте платежный вход до агрегации. Условие по правой таблице в финальном WHERE может удалить заказы без платежа и фактически превратить LEFT JOIN во внутреннее соединение.

Если дочерняя таблица нужна лишь для проверки существования, используйте EXISTS. Требование хотя бы одного одобренного платежа не означает, что каждый такой платеж должен размножить позиции. Явное выражение намерения защищает смысл и часто уменьшает работу.

Проверить несимметричные случаи

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

COALESCE в ноль является бизнес-решением. Если отсутствие означает неизвестные данные или незавершенный импорт, ноль вводит в заблуждение. COUNT(*) после LEFT JOIN также считает сохраненную родительскую строку без ребенка. Для числа реальных детей считайте ненулевой дочерний ключ.

Уникальные ограничения справочников предотвращают другую причину размножения: несколько соответствий в якобы однозначном справочнике. Проверяйте реальный ключ, включая арендатора и период действия при необходимости. Соглашение приложения само по себе не доказывает уникальность.

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

Техническая документация: Microsoft Learn: JOIN semantics · Microsoft Learn: SUM.