Toxtaga
Разработка

Почему после JOIN выручка удваивается: ошибка в SQL, которую легко перенести в BI

В таблице заказов выручка составляет 3 000 рублей. После объединения с товарами в заказах отчёт показывает 6 000. Синтаксической ошибки нет: запрос выполняется, данные выглядят правдоподобно. Но считать такую сумму нельзя.

Разберём причину на небольшом учебном примере. Все данные вымышлены; это не клиентский кейс.

Что именно считаем

В таблице orders одна строка соответствует одному заказу. Поле amount — сумма всего заказа, а не стоимость отдельного товара.

Заказ 1: 1 000 рублей. Заказ 2: 2 000 рублей. Итого: 3 000 рублей.

В таблице order_items уже другой уровень детализации: одна строка — один товар в заказе. В каждом из двух заказов по два товара.

Пример можно воспроизвести в SQLite:

CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, amount INTEGER NOT NULL );
CREATE TABLE order_items ( order_id INTEGER, product TEXT );
INSERT INTO orders VALUES (1, 1000), (2, 2000); INSERT INTO order_items VALUES (1, 'Клавиатура'), (1, 'Мышь'), (2, 'Монитор'), (2, 'Кабель');

Запрос, который удваивает сумму

SELECT SUM(o.amount) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id;

Результат: 6 000.

JOIN нашёл две подходящие строки товаров для каждого заказа. Поэтому после объединения сумма первого заказа встретилась дважды, и сумма второго — тоже:

Заказ 1 — Клавиатура — 1 000 Заказ 1 — Мышь — 1 000 Заказ 2 — Монитор — 2 000 Заказ 2 — Кабель — 2 000

Это не «дубли» в исходной таблице заказов. Это ожидаемый результат связи «один ко многим». Ошибка возникает позже: сумму заказа складывают на уровне отдельных товаров.

Как исправить

Если товары вообще не нужны для расчёта, не присоединяйте их:

SELECT SUM(amount) AS revenue FROM orders;

Если нужно посчитать сумму только заказов, у которых есть хотя бы один товар, подойдёт EXISTS:

SELECT SUM(o.amount) AS revenue FROM orders o WHERE EXISTS ( SELECT 1 FROM order_items i WHERE i.order_id = o.order_id );

На нашем наборе оба запроса вернут 3 000. Но смысл у них различается: EXISTS исключит заказ без товаров, если такой появится. В рабочем отчёте это должно быть осознанным условием.

Другой вариант — перед JOIN привести правую таблицу к одной строке на заказ:

SELECT SUM(o.amount) AS revenue FROM orders o JOIN ( SELECT DISTINCT order_id FROM order_items ) i ON i.order_id = o.order_id;

Он тоже вернёт 3 000 и тоже исключит заказы без товаров. Если нужны фильтры по товарам, их следует применить внутри подзапроса.

Почему SUM(DISTINCT amount) — плохая заплатка

На первых двух заказах она случайно даст правильный результат. Но добавим третий заказ на 1 000 рублей:

INSERT INTO orders VALUES (3, 1000);

Теперь верная общая сумма — 4 000. А SUM(DISTINCT amount) по таблице orders вернёт 3 000: функция оставит только разные значения сумм — 1 000 и 2 000. Два разных заказа с одинаковой суммой она не различает.

Что проверить перед загрузкой в BI

  • Что означает одна строка в каждой таблице: заказ, товар, платёж или событие?
  • Уникален ли ключ на стороне, которую вы считаете справочником?
  • Сколько строк получается на один заказ после JOIN?
  • Сходится ли контрольная сумма до и после объединения при одинаковых условиях отбора?
  • Не повторяется ли сумма заказа в каждой товарной строке выгрузки?

Если сумма уже размножилась в SQL-выгрузке, обычный SUM в BI добросовестно сложит повторённые значения. Поэтому проверять нужно не только формулу показателя, но и уровень детализации данных.

Для воспроизведения: документация SQLite.

Правила соединения таблиц: документация PostgreSQL.