После JOIN выросла сумма: как соединить заказы, позиции и оплаты

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

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

Сначала выберите одну строку отчёта

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

В orders номер заказа уникален. В items и payments он повторяется по смыслу. Уникальность собственных идентификаторов позиции и платежа не делает order_id уникальным. Проверка COUNT(DISTINCT o.id) обнаружит четыре заказа даже после размножения строк; денежные суммы от этого не восстановятся.

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

Запустите пример с двумя таблицами деталей

Сохраните код в join_report.py и выполните python join_report.py. Нужен только стандартный Python с sqlite3. Суммы хранятся в копейках; все заказы имеют одну валюту. Заказ 13 ещё не оплачен, а у заказа 14 нет записей позиций: эти случаи помогают проверить сохранность исходного списка.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders(id INTEGER PRIMARY KEY, amount INTEGER NOT NULL);
CREATE TABLE items(id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL);
CREATE TABLE payments(id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL,
                      amount INTEGER NOT NULL);
INSERT INTO orders VALUES (11,15000),(12,15000),(13,8000),(14,12000);
INSERT INTO items VALUES (1,11),(2,11),(3,11),(4,12),(5,13),(6,13);
INSERT INTO payments VALUES (1,11,7500),(2,11,7500),(3,12,15000),(4,14,12000);
""")
bad = db.execute("""
SELECT COUNT(DISTINCT o.id), SUM(o.amount), SUM(p.amount)
FROM orders o
LEFT JOIN items i ON i.order_id=o.id
LEFT JOIN payments p ON p.order_id=o.id
""").fetchone()
assert bad == (4, 133000, 72000)

query = """
WITH item_counts AS (
 SELECT order_id, COUNT(*) AS item_count FROM items GROUP BY order_id
), paid AS (
 SELECT order_id, SUM(amount) AS paid_amount FROM payments GROUP BY order_id
)
SELECT o.id, o.amount, COALESCE(i.item_count,0), COALESCE(p.paid_amount,0)
FROM orders o
LEFT JOIN item_counts i ON i.order_id=o.id
LEFT JOIN paid p ON p.order_id=o.id
ORDER BY o.id
"""
rows = db.execute(query).fetchall()
assert len({r[0] for r in rows}) == len(rows) == 4
assert sum(r[1] for r in rows) == db.execute(
    "SELECT SUM(amount) FROM orders").fetchone()[0] == 50000
assert sum(r[3] for r in rows) == db.execute(
    "SELECT SUM(amount) FROM payments").fetchone()[0] == 42000
assert rows[-1] == (14, 12000, 0, 12000)
print("bad:", bad)
print("order totals:", sum(r[1] for r in rows))
print("payment totals:", sum(r[3] for r in rows))

Первый результат показывает четыре заказа, 133000 копеек стоимости и 72000 копеек оплат. Исправленный запрос возвращает стоимость 50000 и оплаты 42000 копеек. Обе суммы дополнительно сверяются с отдельными таблицами, поэтому тест обнаружит ошибку в любом из присоединённых наборов.

LEFT JOIN сохраняет заказ без совпадения в деталях. COALESCE подставляет ноль для отсутствующего количества позиций или оплаты. Здесь отсутствие платежа означает, что поступления нет. Если рабочая выгрузка может опаздывать, добавьте контроль её полноты: ноль от неполной загрузки нельзя использовать как подтверждение отсутствия денег.

Почему DISTINCT не заменяет модель

SUM(DISTINCT amount) устраняет повторяющиеся денежные значения. Два разных заказа по 150 рублей в примере имеют одинаковую стоимость, поэтому один из них исчезнет из такого расчёта. Удалять нужно лишние совпадения сущностей, а не одинаковые числа.

Группировка по заказу после исходного соединения тоже не поможет: она сложит уже размноженные платежи. Предагрегация должна произойти до объединения независимых деталей. Если нужен отчёт об оплате конкретного товара, одной связи payment → order недостаточно. Сначала потребуется отдельное правило распределения платежа между позициями.

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

Соберите расчёт вместе с отчётом

В платном курсе «Практическая аналитика данных» есть работа с детализацией таблиц, раздуванием сумм при JOIN, возвратами и сверкой итогов. Дальше вы построите отчёты по продажам, клиентам и рекламе, а затем объедините их в DataLens. Откройте программу курса, чтобы пройти путь от проверки выгрузки до объяснения результата бизнесу.