После JOIN сумма выросла: как детализация ломает BI-витрину

Отчёт обновился без ошибок. Количество заказов совпало с источником. А сумма оказалась выше, чем до добавления товарных позиций. Можно начать искать дубли в загрузке, но сначала стоит проверить сам JOIN: иногда он совершенно правильно соединяет строки, которые нельзя суммировать вместе.

Разберём небольшой учебный пример: три заказа, их позиции и оплаты. Покажу, почему COUNT(DISTINCT) может скрыть проблему, SUM(DISTINCT) — испортить результат, а предварительная агрегация — исправить расчёт только при правильно выбранной детализации. SQL-примеры проверены в SQLite; это разбор логики данных, без привязки к конкретной BI-системе.

Сначала определим, что считаем

В примере сумма заказа — стоимость оформленного заказа, а оплата — поступившая по нему сумма. Это разные показатели; ни один здесь не назван бухгалтерской выручкой. Момент признания выручки, возвраты и налоги не моделируются: разберём только сохранность двух простых сумм при соединении таблиц.

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

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

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    amount_kopecks INTEGER NOT NULL
);

CREATE TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    sku TEXT NOT NULL,
    item_amount_kopecks INTEGER NOT NULL
);

CREATE TABLE payments (
    payment_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    paid_kopecks INTEGER NOT NULL
);

INSERT INTO orders VALUES
    (101, 10000), (102, 10000), (103, 20000);

INSERT INTO order_items VALUES
    (1, 101, 'A', 6000),
    (2, 101, 'B', 4000),
    (3, 102, 'A', 10000),
    (4, 103, 'C', 20000);

INSERT INTO payments VALUES
    (1, 101, 6000),
    (2, 101, 4000),
    (3, 102, 10000);

В таблице orders одна строка соответствует одному заказу. В order_items — одной позиции, в payments — одной оплате. Это и есть детализация, или зерно, таблицы. У заказа 101 две позиции и две оплаты, у 103 пока нет оплат.

Контрольные суммы: заказов на 400 рублей, оплат на 200 рублей. Совпадение сумм позиций и заказов заложено в учебные данные; в настоящей системе доставка, скидки и округления могут потребовать отдельной сверки.

JOIN не создал дубли в источнике

Добавим к заказам позиции и посчитаем показатели:

SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(o.amount_kopecks) AS order_amount_kopecks
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id;

Получится 4 строки, 3 уникальных заказа и 50000 копеек вместо 40000.

Заказ 101 встретился дважды: один раз для позиции A, второй — для B. Поэтому его полная стоимость тоже попала в сумму дважды. Условие соединения правильное, первичные ключи уникальны, загрузка ничего не продублировала. Ошибка в том, что показатель уровня заказа суммируется на уровне позиций.

COUNT(DISTINCT order_id) при этом возвращает правильное число заказов. Если проверять только этот счётчик, витрина пройдёт проверку с неверной денежной суммой.

Полезный диагностический запрос:

SELECT o.order_id, COUNT(*) AS rows_after_join
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.order_id
HAVING COUNT(*) > 1;

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

Почему SUM(DISTINCT) не лечит расчёт

Хочется убрать повторяющиеся суммы:

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

Результат — 30000 копеек. DISTINCT убирает одинаковые значения аргумента, а не повторения конкретного заказа. Заказы 101 и 102 стоят по 100 рублей: один из этих разных заказов исчез из суммы вместе с лишним повторением.

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

Две таблицы деталей умножаются между собой

Теперь добавим оплаты:

SELECT
    o.order_id,
    COUNT(*) AS row_count,
    SUM(i.item_amount_kopecks) AS items_kopecks,
    SUM(p.paid_kopecks) AS paid_kopecks
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
LEFT JOIN payments AS p ON p.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

Для заказа 101 получим четыре строки: каждая из двух позиций соединилась с каждой из двух оплат. Сумма позиций станет 200 рублей вместо 100, сумма оплат — тоже 200 вместо 100.

GROUP BY order_id не отменяет размножение: группировка суммирует уже соединённые строки. Если у заказа m позиций и n оплат, то при m > 0 и n > 0 такое соединение даст m × n строк. Пустые стороны LEFT JOIN ведут себя иначе: непарная строка сохраняется с NULL.

Приведём обе стороны к одному зерну

Если нужен отчёт по заказам, сначала соберём каждую таблицу деталей до одной строки на order_id:

CREATE VIEW order_mart AS
WITH items_by_order AS (
    SELECT order_id, SUM(item_amount_kopecks) AS items_kopecks
    FROM order_items
    GROUP BY order_id
),
payments_by_order AS (
    SELECT order_id, SUM(paid_kopecks) AS paid_kopecks
    FROM payments
    GROUP BY order_id
)
SELECT
    o.order_id,
    o.amount_kopecks,
    COALESCE(i.items_kopecks, 0) AS items_kopecks,
    COALESCE(p.paid_kopecks, 0) AS paid_kopecks
FROM orders AS o
LEFT JOIN items_by_order AS i ON i.order_id = o.order_id
LEFT JOIN payments_by_order AS p ON p.order_id = o.order_id;

SELECT
    COUNT(*) AS order_count,
    SUM(amount_kopecks) AS amount_kopecks,
    SUM(paid_kopecks) AS paid_kopecks
FROM order_mart;

Теперь результат: 3 заказа, 40000 копеек стоимости и 20000 копеек оплат. Заказ 103 сохранён с нулевой оплатой.

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

Предварительная агрегация сохраняет нужный итог, но убирает детализацию. Из этой витрины нельзя получить оплату по SKU: у платежа нет связи с товарной позицией. Для такого отчёта потребуется правило распределения — например, пропорционально стоимости позиций, с обработкой скидок и округлений. Его нужно назвать в модели; JOIN не придумает бизнес-смысл за нас.

Что проверять перед выдачей витрины в BI

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

Первая — уникальность ключа на обещанном уровне:

SELECT order_id, COUNT(*) AS row_count
FROM order_mart
GROUP BY order_id
HAVING COUNT(*) <> 1;

Ожидаемый результат — пустой набор. Эта проверка обнаружит повторения, но не потерянные заказы: отсутствующий ключ в GROUP BY не появится.

Вторая — сверка с базовой таблицей:

SELECT
    (SELECT COUNT(*) FROM orders) AS source_count,
    (SELECT COUNT(*) FROM order_mart) AS mart_count,
    (SELECT SUM(amount_kopecks) FROM orders) AS source_amount,
    (SELECT SUM(amount_kopecks) FROM order_mart) AS mart_amount;

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

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

Что записать рядом с метрикой

Короткого описания часто достаточно, чтобы следующий JOIN не вернул ошибку:

• одна строка витрины — один заказ;

• уникальный ключ — order_id;

• стоимость заказа и полученная оплата — разные показатели;

• валюта и единица суммы определены;

• соединение со справочником должно давать не более одного совпадения;

• разрез по позициям требует другого зерна и правил расчёта показателей.

Сначала стоит ответить на вопрос «что означает одна строка после всех соединений?», а уже потом писать SUM. Успешно завершившийся запрос подтверждает, что база смогла его выполнить. Сохранность смысла показателя приходится проверять отдельно.