Какие товары покупают вместе: self JOIN без двойного счёта

В отчёте пара товаров встретилась четыре раза. В самих заказах — только три. Разбираем, как дубли позиций превращают покупку в лишнее совпадение.

SQL Studio / Разборы SQL / Товарные пары

Корзинный анализ ищет товары, оказавшиеся в одном заказе. Одна «корзина» здесь — один order_id, а не строка order_items и не пользователь. Берём заказы со статусом paid или delivered, не включаем cancelled. Для каждой пары нужно посчитать число уникальных заказов, в которых встречаются оба товара, не размножая варианты A–B и B–A.

Это не то же самое, что ABC-анализ выручки (сколько денег дал товар) или обычный JOIN заказов и позиций. Здесь единица результата — пара товаров, а основная защита — a.product_id < b.product_id и удаление повторных строк «заказ — товар» до соединения.

Почему одного условия a.product_id < b.product_id мало

В шести заказах есть повторные позиции: товар №12 дважды записан в заказе №1, товар №11 дважды — в заказе №5. Это может быть результатом импорта отдельных строк или ошибкой источника; для вопроса о наличии товара в заказе оба случая следует свести к одному присутствию. Заказ №4 отменён и исключён.

Следующий запрос уже убрал зеркальные пары, но всё ещё считает строки self JOIN. Он запускается без ошибки — опаснее всего то, что результат выглядит правдоподобно.

WITH orders(order_id,status) AS (
  VALUES (1,'delivered'),(2,'delivered'),(3,'paid'),
         (4,'cancelled'),(5,'delivered'),(6,'delivered')
), order_items(order_id,product_id) AS (
  VALUES (1,11),(1,12),(1,12),(1,13),
         (2,11),(2,12),
         (3,11),(3,12),(3,14),
         (4,11),(4,14),
         (5,11),(5,11),
         (6,15),(6,16)
)
SELECT a.product_id AS product_a,b.product_id AS product_b,
       COUNT(*) AS row_pairs_wrong
FROM order_items a
JOIN order_items b ON a.order_id=b.order_id AND a.product_id<b.product_id
JOIN orders o ON o.order_id=a.order_id
WHERE o.status IN ('paid','delivered')
GROUP BY a.product_id,b.product_id
ORDER BY row_pairs_wrong DESC,product_a,product_b;
Число соединённых строк, а не число корзин
product_aproduct_brow_pairs_wrong
11124
12132
11131
11141
12141
15161

Товар №12 повторён в первой корзине, поэтому пара 11–12 начисляется дважды за один заказ. Пара 12–13 завышена по той же причине. COUNT(DISTINCT a.order_id) непосредственно в итоговом запросе тоже мог бы исправить число заказов; предварительное SELECT DISTINCT order_id, product_id удобнее, потому что устраняет размножение ещё до соединения.

Правильный self JOIN по уникальному набору товаров

Сначала отфильтруем оплаченные или доставленные заказы и сведём позиции к уровню «заказ — товар». Затем соединяем этот набор с самим собой по заказу. Условие a.product_id < b.product_id гарантирует ровно одно направление пары, а COUNT(*) после дедупликации означает именно количество заказов с двумя товарами.

WITH orders(order_id,status) AS (
  VALUES (1,'delivered'),(2,'delivered'),(3,'paid'),
         (4,'cancelled'),(5,'delivered'),(6,'delivered')
), order_items(order_id,product_id) AS (
  VALUES (1,11),(1,12),(1,12),(1,13),
         (2,11),(2,12),
         (3,11),(3,12),(3,14),
         (4,11),(4,14),
         (5,11),(5,11),
         (6,15),(6,16)
),
paid_lines AS (
  SELECT DISTINCT i.order_id,i.product_id
  FROM order_items i JOIN orders o ON o.order_id=i.order_id
  WHERE o.status IN ('paid','delivered')
)
SELECT a.product_id AS product_a,b.product_id AS product_b,
       COUNT(*) AS orders_together
FROM paid_lines a
JOIN paid_lines b ON a.order_id=b.order_id AND a.product_id<b.product_id
GROUP BY a.product_id,b.product_id
ORDER BY orders_together DESC,product_a,product_b;
Число оплаченных или доставленных заказов с каждой парой
product_aproduct_borders_together
11123
11131
11141
12131
12141
15161

Пара 11–12 встречается в заказах №1, №2 и №3 — ровно 3 раза на уровне корзин. Отменённый №4 не участвует в 11–14. Один и тот же пользователь мог сделать несколько заказов: это сознательно другая единица измерения, чем уникальные покупатели.

Нулевая пара: дубли одного товара не образуют сочетание

В заказе №5 два раза записан только товар №11. Соединение не должно выдать пару 11–11; условие строгого неравенства исключает её даже без дедупликации, но предварительный DISTINCT всё равно полезен для остальных заказов.

WITH order_items(order_id,product_id) AS (VALUES (5,11),(5,11)),
unique_lines AS (SELECT DISTINCT order_id,product_id FROM order_items)
SELECT COUNT(*) AS pair_orders
FROM unique_lines a JOIN unique_lines b
  ON a.order_id=b.order_id AND a.product_id<b.product_id;

Результат — одна строка pair_orders = 0. Это не «ноль проданных единиц»: товар покупали, но никакой пары разных товаров в этой корзине нет.

Три разных знаменателя: support и confidence

Частота пары сама по себе не доказывает, что товары стоит рекомендовать вместе. В наших данных подходящих корзин пять: №1, №2, №3, №5 и №6. Товар №11 встречался в четырёх из них, №12 — в трёх. Поэтому support пары 11–12 = 3/5, а направленная confidence 11 → 12 = 3/4. В обратную сторону она будет 3/3.

WITH orders(order_id,status) AS (
  VALUES (1,'delivered'),(2,'delivered'),(3,'paid'),
         (4,'cancelled'),(5,'delivered'),(6,'delivered')
), order_items(order_id,product_id) AS (
  VALUES (1,11),(1,12),(1,12),(1,13),
         (2,11),(2,12),
         (3,11),(3,12),(3,14),
         (4,11),(4,14),
         (5,11),(5,11),
         (6,15),(6,16)
),
valid_orders AS (SELECT order_id FROM orders WHERE status IN ('paid','delivered')),
valid_lines AS (
  SELECT DISTINCT i.order_id,i.product_id FROM order_items i
  JOIN valid_orders o ON o.order_id=i.order_id
), together AS (
  SELECT a.order_id FROM valid_lines a JOIN valid_lines b
    ON a.order_id=b.order_id AND a.product_id=11 AND b.product_id=12
), counts AS (
  SELECT (SELECT COUNT(*) FROM together) AS both_n,
         (SELECT COUNT(*) FROM valid_orders) AS baskets_n,
         (SELECT COUNT(*) FROM valid_lines WHERE product_id=11) AS a_n,
         (SELECT COUNT(*) FROM valid_lines WHERE product_id=12) AS b_n
)
SELECT both_n,baskets_n,a_n,b_n,
       ROUND(1.0*both_n/NULLIF(baskets_n,0),2) AS support,
       ROUND(1.0*both_n/NULLIF(a_n,0),2) AS conf_11_to_12,
       ROUND(1.0*both_n/NULLIF(b_n,0),2) AS conf_12_to_11
FROM counts;
Доли совместных покупок, проверенные в SQLite
both_nbaskets_na_nb_nsupportconf_11_to_12conf_12_to_11
35430.600.751.00

По этим шести учебным заказам нельзя заключать, что товар №11 заставляет покупать №12: совместное появление — корреляция, не причинность. На реальном ассортименте ещё понадобятся достаточная выборка, сезонность, доступность товаров и сравнение с их отдельной частотой. При больших корзинах количество возможных пар растёт как k × (k − 1) / 2; ограничивайте размер и период данных осмысленно.

Упражнение: проверьте лишнее сочетание

Добавьте в заказ №2 вторую строку товара №12 и предположите результаты обоих первых запросов. Какие пары вырастут в «ошибочной» таблице, а какие останутся прежними после DISTINCT? Попробуйте в бесплатной SQLite-песочнице; для тренировки соединений подойдёт P052: товар в позиции заказа.

Что читать дальше: размножение строк после JOIN · COUNT DISTINCT и зерно данных · ABC-анализ товаров, а не пар.

Официальная документация: COUNT, SUM, DISTINCT в SQLite; WITH и CTE.


Если нужна структурированная практика на MySQL 8 — отдельный курс на Stepik. Примеры этой статьи рассчитаны на SQLite и доступны бесплатно.