SQL: Найти товары, которые покупали вместе

Решение

Это задача на анализ сочетаний товаров (market basket analysis) — критична для e-commerce для рекомендаций и кросс-селлинга.

Логика решения:

1. Соединяем order_items саму с собой — по order_id для нахождения товаров в одном заказе
2. Исключаем одинаковые товары — нам нужны только пары разных товаров
3. Избегаем дубликатов — условием product_id_1 < product_id_2
4. Считаем частоту — количество заказов с обеими товарами
5. Сортируем и ограничиваем — топ-10 по популярности

SQL запрос:

SELECT 
  oi1.product_id as product_id_1,
  oi2.product_id as product_id_2,
  COUNT(DISTINCT oi1.order_id) as times_bought_together
FROM order_items oi1
INNER JOIN order_items oi2 
  ON oi1.order_id = oi2.order_id 
  AND oi1.product_id < oi2.product_id
GROUP BY oi1.product_id, oi2.product_id
ORDER BY times_bought_together DESC
LIMIT 10;

Альтернативный подход (более явный):

WITH product_pairs AS (
  SELECT 
    LEAST(oi1.product_id, oi2.product_id) as product_id_1,
    GREATEST(oi1.product_id, oi2.product_id) as product_id_2,
    oi1.order_id
  FROM order_items oi1
  INNER JOIN order_items oi2 
    ON oi1.order_id = oi2.order_id 
    AND oi1.product_id != oi2.product_id
)
SELECT 
  product_id_1,
  product_id_2,
  COUNT(DISTINCT order_id) as times_bought_together
FROM product_pairs
GROUP BY product_id_1, product_id_2
ORDER BY times_bought_together DESC
LIMIT 10;

Вариант с дополнительной информацией:

WITH product_pairs AS (
  SELECT 
    oi1.product_id as product_id_1,
    oi2.product_id as product_id_2,
    oi1.order_id
  FROM order_items oi1
  INNER JOIN order_items oi2 
    ON oi1.order_id = oi2.order_id 
    AND oi1.product_id < oi2.product_id
),
pair_stats AS (
  SELECT 
    product_id_1,
    product_id_2,
    COUNT(DISTINCT order_id) as times_bought_together
  FROM product_pairs
  GROUP BY product_id_1, product_id_2
),
product_counts AS (
  SELECT 
    product_id,
    COUNT(DISTINCT order_id) as total_orders
  FROM order_items
  GROUP BY product_id
)
SELECT 
  ps.product_id_1,
  ps.product_id_2,
  ps.times_bought_together,
  pc1.total_orders as product_1_total_orders,
  pc2.total_orders as product_2_total_orders,
  ROUND(100.0 * ps.times_bought_together / LEAST(pc1.total_orders, pc2.total_orders), 2) as cooccurrence_rate
FROM pair_stats ps
LEFT JOIN product_counts pc1 ON ps.product_id_1 = pc1.product_id
LEFT JOIN product_counts pc2 ON ps.product_id_2 = pc2.product_id
ORDER BY times_bought_together DESC
LIMIT 10;

Ключевые моменты:

INNER JOIN на order_id — берём только те пары товаров, которые находились в одном заказе.

oi1.product_id < oi2.product_id — гарантирует, что пара (1, 2) не повторяется как (2, 1). Это исключает дубликаты.

COUNT(DISTINCT order_id) — считаем только уникальные заказы (даже если товары в разном количестве в одном заказе).

LEAST() и GREATEST() — альтернативный способ избежать дубликатов, работает в большинстве СУБД.

cooccurrence_rate — дополнительная метрика, показывающая, какой процент заказов товара A также содержат товар B.

Бизнес-применение:

• Рекомендации — когда пользователь смотрит товар, предложить часто покупаемые с ним товары
• Кросс-селлинг — при оформлении заказа подсказать "часто покупают вместе"
• Категоризация — если товары часто покупаются вместе, может быть смыслом их группировать
• Inventory Planning — товары, которые часто покупаются вместе, должны быть близко в магазине