Размножение строк при JOIN

Размножение строк при соединении «один ко многим»

Для изучения кардинальности соединений подготовим базу из трёх связанных таблиц: заказы (orders), позиции заказов (items) и платежи (payments).

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

CREATE TABLE items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  amount INTEGER NOT NULL
);

CREATE TABLE payments (
  payment_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  amount INTEGER NOT NULL
);

INSERT INTO orders (order_id, client_id) VALUES
  (1, 10),
  (2, 10),
  (3, 20);

INSERT INTO items (item_id, order_id, amount) VALUES
  (1, 1, 500),
  (2, 1, 300),
  (3, 2, 200),
  (4, 3, 100);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (10, 1, 400),
  (11, 1, 400),
  (12, 2, 200),
  (13, 3, 100);

Соединим таблицу заказов с таблицей позиций для заказа 1:

SELECT o.order_id, o.client_id, i.item_id, i.amount AS item_amount
FROM orders o
JOIN items i ON o.order_id = i.order_id
WHERE o.order_id = 1
ORDER BY i.item_id;

Результат запроса:

order_id	client_id	item_id	item_amount
1	10	1	500
1	10	2	300

В таблице orders заказ 1 представлен ровно одной строкой. В таблице items этому заказу соответствуют две строки: позиция 1 на сумму 500 и позиция 2 на сумму 300. Условие ON o.order_id = i.order_id находит совпадение для каждой строки из дочерней таблицы. В результате одна строка заказа соединяется сначала с первой позицией, затем со второй.

Число строк в результирующей таблице изменилось: вместо одной строки для заказа 1 запрос вернул две. Изменилась гранулярность результата: если в orders одна строка описывала заказ целиком, то после соединения строка описывает отдельную товарную позицию в рамках заказа. Первичный ключ orders.order_id перестал быть уникальным идентификатором строки результата. Уникальным ключом строки теперь выступает items.item_id (или составная пара (order_id, item_id)).

Кардинальность промежуточного соединения для одного родительского ключа определяется числом совпадений в дочерней таблице. Если у родительской записи ровно $N$ дочерних строк, внутреннее соединение вернёт $1\times N=N$ строк. Это штатное поведение реляционного соединения: база данных формирует все допустимые комбинации строк, удовлетворяющие предикату соединения.

Перемножение двух независимых дочерних таблиц

Теперь добавим к заказу 1 вторую дочернюю таблицу — платежи (payments). Позиции и платежи не связаны между собой напрямую: позиции фиксируют состав покупки, а платежи — транзакции оплаты. Обе таблицы ссылаются только на родительскую таблицу orders по ключу order_id.

Выполним последовательное соединение всех трёх таблиц без агрегации:

SELECT o.order_id, i.item_id, i.amount AS item_amount, p.payment_id, p.amount AS pay_amount
FROM orders o
JOIN items i ON o.order_id = i.order_id
JOIN payments p ON o.order_id = p.order_id
WHERE o.order_id = 1
ORDER BY i.item_id, p.payment_id;

Результат:

order_id	item_id	item_amount	payment_id	pay_amount
1	1	500	10	400
1	1	500	11	400
1	2	300	10	400
1	2	300	11	400

В таблице items для заказа 1 есть 2 записи. В таблице payments для заказа 1 также есть 2 записи (платежи 10 и 11 по 400). На выходе получилось 4 строки.

Такой результат возникает из-за последовательной работы оператора соединения:

1. Первый шаг orders JOIN items сформировал 2 строки: (заказ 1, позиция 1) и (заказ 1, позиция 2).
2. Второй шаг присоединяет таблицу payments по условию o.order_id = p.order_id. Для каждой из двух строк промежуточного набора СУБД находит в таблице payments все строки с order_id = 1. Их две.
3. Строка (заказ 1, позиция 1) соединяется с платежом 10 и платежом 11. Строка (заказ 1, позиция 2) также соединяется с платежом 10 и платежом 11.

Внутри одного ключа заказа образовалось локальное декартово произведение строк двух несвязанных списков. Если у заказа $M$ позиций и $P$ платежей, число строк промежуточного соединения равно ровно $M\times P$. В данном случае $2\times 2=4$ строки. Если бы у заказа было 3 позиции и 2 платежа, результат содержал бы 6 строк. База данных выполнила ровно то, что предписано предикатами: нашла все пары строк, у которых совпадает order_id.

Искажение агрегатной функции SUM

Попытка вычислить итоговую стоимость позиций и сумму платежей в одном запросе с группировкой по order_id приводит к математической ошибке:

SELECT o.order_id,
       SUM(i.amount) AS distorted_item_sum,
       SUM(p.amount) AS distorted_pay_sum
FROM orders o
JOIN items i ON o.order_id = i.order_id
JOIN payments p ON o.order_id = p.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

Результат выполнения:

order_id	distorted_item_sum	distorted_pay_sum
1	1600	1600
2	200	200
3	100	100

Проверим фактические суммы заказа 1 напрямую по исходным таблицам:

• Позиции заказа 1: 500 + 300 = 800.
• Платежи заказа 1: 400 + 400 = 800.

В отчёте вместо 800 в обеих колонках выведено 1600. Причина искажения видна в четырёх детальных строках промежуточного соединения из предыдущего раздела:

Строка 1: позиция 500, платёж 400
Строка 2: позиция 500, платёж 400
Строка 3: позиция 300, платёж 400
Строка 4: позиция 300, платёж 400

Функция SUM работает построчно над потоком данных, сформированным блоком FROM и JOIN. Она не знает, из какой исходной таблицы пришёл столбец:

• Сумма позиций вычисляется как: 500 + 500 + 300 + 300 = 1600.
• Сумма платежей вычисляется как: 400 + 400 + 400 + 400 = 1600.

Каждая позиция сложилась ровно 2 раза — столько строк платежей зарегистрировано для заказа 1. Каждый платёж сложился ровно 2 раза — столько позиций открыто в заказе 1.

Общее правило: при объединении двух независимых таблиц с кардинальностями $M$ и $P$ каждое значение первой таблицы суммируется $P$ раз, а каждое значение второй таблицы — $M$ раз. Итоговая сумма по первой таблице завышается в $P$ раз, по второй — в $M$ раз.

Для заказов 2 и 3 суммы не исказились (200 и 100), потому что у каждого из них ровно 1 позиция и 1 платёж: $1\times 1=1$. Однако корректность крайних случаев не делает запрос верным.

Условие корректности SUM: агрегат даёт верный результат только тогда, когда каждое учитываемое числовое значение попадает в поток агрегации ровно один раз. Размножение строк нарушает это условие.

Диагностика размножения и ловушка GROUP BY

Оператор GROUP BY o.order_id сворачивает строки и возвращает ровно одну строку на заказ. Это создаёт иллюзию корректности: структура отчёта выглядит правильно, дублирующихся строк заказов на экране нет, однако вычисленные агрегаты уже содержат искажения.

Для проверки наличия размножения строк применяются два диагностических шага.

Шаг 1: Проекция первичных ключей всех соединённых таблиц без агрегации

В запрос добавляются первичные ключи каждой участвующей таблицы без GROUP BY:

SELECT o.order_id, i.item_id, p.payment_id
FROM orders o
JOIN items i ON o.order_id = i.order_id
JOIN payments p ON o.order_id = p.order_id
WHERE o.order_id = 1;

Если на один order_id приходится несколько различных пар (item_id, payment_id), строки размножаются.

Шаг 2: Проверка кардинальности промежуточного соединения через COUNT(*)

Оценим количество строк, формируемых соединением для каждого заказа:

SELECT o.order_id, COUNT(*) AS intermediate_row_cnt
FROM orders o
JOIN items i ON o.order_id = i.order_id
JOIN payments p ON o.order_id = p.order_id
WHERE o.client_id = 10
GROUP BY o.order_id
ORDER BY o.order_id;

Результат:

order_id	intermediate_row_cnt
1	4
2	1

Затем подсчитаем число записей в каждой дочерней таблице независимо:

SELECT 'items' AS source, order_id, COUNT(*) AS row_cnt, SUM(amount) AS total_amount
FROM items
WHERE order_id = 1
GROUP BY order_id
UNION ALL
SELECT 'payments' AS source, order_id, COUNT(*) AS row_cnt, SUM(amount) AS total_amount
FROM payments
WHERE order_id = 1
GROUP BY order_id;

Результат сверки:

source	order_id	row_cnt	total_amount
items	1	2	800
payments	1	2	800

В таблице items для заказа 1 получено 2 строки. В payments — 2 строки. Произведение $2\times 2=4$ точно совпадает со значением intermediate_row_cnt из промежуточного соединения. Это подтверждает наличие размножения.

Случайная делимость полученной суммы на число строк (например, 1600 делится на 2) не является доказательством размножения: корректная сумма тоже может делиться нацело на любое число. Единственным строгим подтверждением ошибки служит сопоставление вычисленного агрегата с результатом независимого запроса к исходной таблице.