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_a | product_b | row_pairs_wrong |
|---|---|---|
| 11 | 12 | 4 |
| 12 | 13 | 2 |
| 11 | 13 | 1 |
| 11 | 14 | 1 |
| 12 | 14 | 1 |
| 15 | 16 | 1 |
Товар №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_a | product_b | orders_together |
|---|---|---|
| 11 | 12 | 3 |
| 11 | 13 | 1 |
| 11 | 14 | 1 |
| 12 | 13 | 1 |
| 12 | 14 | 1 |
| 15 | 16 | 1 |
Пара 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;
| both_n | baskets_n | a_n | b_n | support | conf_11_to_12 | conf_12_to_11 |
|---|---|---|---|---|---|---|
| 3 | 5 | 4 | 3 | 0.60 | 0.75 | 1.00 |
По этим шести учебным заказам нельзя заключать, что товар №11 заставляет покупать №12: совместное появление — корреляция, не причинность. На реальном ассортименте ещё понадобятся достаточная выборка, сезонность, доступность товаров и сравнение с их отдельной частотой. При больших корзинах количество возможных пар растёт как k × (k − 1) / 2; ограничивайте размер и период данных осмысленно.
Упражнение: проверьте лишнее сочетание
Добавьте в заказ №2 вторую строку товара №12 и предположите результаты обоих первых запросов. Какие пары вырастут в «ошибочной» таблице, а какие останутся прежними после DISTINCT? Попробуйте в бесплатной SQLite-песочнице; для тренировки соединений подойдёт P052: товар в позиции заказа.
Официальная документация: COUNT, SUM, DISTINCT в SQLite; WITH и CTE.
Если нужна структурированная практика на MySQL 8 — отдельный курс на Stepik. Примеры этой статьи рассчитаны на SQLite и доступны бесплатно.