Если вы когда-нибудь задумывались, почему в Магните или Пятерочке пельмени лежат рядом с майонезом, а хлеб с молоком? Все, что происходит в крупных сетевых магазинах происходит не просто так :-)

Анализ покупательской корзины (market basket analysis) отвечает на простой вопрос: какие товары люди покупают вместе? Зная это, можно:

  • располагать связанные товары рядом, это как раз пример выше, он же кросс-мерчандайзинг;

  • собирать комплекты и акции: купи пельмени и получи майонез в подарок;

  • рекомендовать товары в мобильном приложении (при добавлении в корзину пельменей у покупателя появляется рекомендация: с этим товаром также покупают майонез).

В этой статье мы разберем одну из типичных задач вида «Анализ покупательской корзины» на учебных данных, с кодом SQL, со всей необходимой математикой и с примером выводов. Задача не боевая, а лишь учебный пример.

1. Исходные данные

Представим, что у нас есть интернет-магазин продуктов и данные хранятся в двух таблицах:

Таблица transactions (справочник со всеми чеками):

transaction_id

date

customer_id

1001

2026-02-01

501

1002

2026-02-01

502

1003

2026-02-02

501

Таблица transaction_items (состав всех чеков из справочника):

transaction_id

product_id

product_name

quantity

price

1001

101

Хлеб Бородинский

1

45.00

1001

102

Молоко Простоквашино

2

89.00

1001

103

Пельмени Цезарь

1

120.00

1002

101

Хлеб Бородинский

1

45.00

1002

104

Колбаса Докторская

1

180.00

1003

102

Молоко Простоквашино

1

89.00

1003

103

Пельмени Цезарь

2

120.00

1003

105

Майонез Слобода

1

70.00

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

2. Постановка задачи

Категорийный менеджер просит ответить на вопросы:

  1. Какие пары товаров чаще всего встречаются в одном чеке?

  2. Для каждого товара, что еще кладут в корзину вместе с ним?

  3. Какие сочетания товаров приносят больше всего выручки?

Будем решать задачу на чистом SQL.

3. Простая статистика: частотность пар

Для начала найдем все пары товаров, которые встречаются в одном чеке.

3.1. Джойн таблицы на саму себя (self-join) для поиска пар

Ключевая идея: соединяем таблицу transaction_items саму с собой по transaction_id, чтобы для каждого чека получить все возможные комбинации товаров внутри него .

SELECT 
    ti1.transaction_id,
    ti1.product_id AS product_a,
    ti1.product_name AS product_a_name,
    ti2.product_id AS product_b,
    ti2.product_name AS product_b_name
FROM transaction_items ti1
JOIN transaction_items ti2 
    ON ti1.transaction_id = ti2.transaction_id
    AND ti1.product_id < ti2.product_id  -- убираем дубли и самосоединения
WHERE ti1.transaction_id IN (1001, 1002, 1003)  -- для примера
ORDER BY ti1.transaction_id;

Результат:

transaction_id

product_a

product_a_name

product_b

product_b_name

1001

101

Хлеб Бородинский

102

Молоко Простоквашино

1001

101

Хлеб Бородинский

103

Пельмени Цезарь

1001

102

Молоко Простоквашино

103

Пельмени Цезарь

1002

101

Хлеб Бородинский

104

Колбаса Докторская

1003

102

Молоко Простоквашино

103

Пельмени Цезарь

1003

102

Молоко Простоквашино

105

Майонез Слобода

1003

103

Пельмени Цезарь

105

Майонез Слобода

Что мы сделали:

Ограничение ti1.product_id < ti2.product_id гарантирует, что пара (хлеб, молоко) попадет в результат только один раз, а не дважды как (хлеб, молоко) и (молоко, хлеб).

Условие WHERE ti1.transaction_id IN (1001, 1002, 1003) - выбираем три чека для примера.

В чеке 1001 с тремя товарами получилось 3 пары (формула сочетаний: C(3,2) = 3).

3.2. Подсчет частоты пар

Теперь сгруппируем и посчитаем, сколько раз каждая пара встретилась:

SELECT 
    ti1.product_id AS product_a_id,
    MAX(ti1.product_name) AS product_a_name,  -- берем максимум, он все равно один
    ti2.product_id AS product_b_id,
    MAX(ti2.product_name) AS product_b_name,
    COUNT(*) AS times_together -- считаем строки 
FROM transaction_items ti1
JOIN transaction_items ti2 
    ON ti1.transaction_id = ti2.transaction_id
    AND ti1.product_id < ti2.product_id
GROUP BY ti1.product_id, ti2.product_id
ORDER BY times_together DESC;

Результат:

product_a_id

product_a_name

product_b_id

product_b_name

times_together

102

Молоко Простоквашино

103

Пельмени Цезарь

2

101

Хлеб Бородинский

102

Молоко Простоквашино

1

101

Хлеб Бородинский

103

Пельмени Цезарь

1

101

Хлеб Бородинский

104

Колбаса Докторская

1

102

Молоко Простоквашино

105

Майонез Слобода

1

103

Пельмени Цезарь

105

Майонез Слобода

1

Инсайт: пельмени и молоко встречаются вместе в 2 чеках из 3, а хлеб с молоком - только в одном, на таком малом наборе выводы делать рано, но думаю, что базовая логика понятна.

4. Метрики качества ассоциативных правил

Простая частота встречаемости – это не единственная метрика, в анализе корзины используют три ключевых показателя, рассмотрим каждый из них.

4.1. Поддержка (Support)

Support – это доля транзакций, содержащих данный набор товаров. Для набора (X, Y):

Support(X, Y) = (число транзакций с X и Y) / (общее число транзакций)

На наших данных (3 транзакции):

Support(пельмени, молоко) = 2/3 = 0,67
Support(хлеб, молоко) = 1/3 = 0,33

4.2. Достоверность (Confidence)

Confidence – это условная вероятность: если купили X, то купят и Y .

Confidence(X → Y) = Support(X, Y) / Support(X)

Сначала нужно посчитать частоту каждого товара отдельно:

SELECT 
    product_id,
    product_name,
    COUNT(DISTINCT transaction_id) AS transaction_count
FROM transaction_items
GROUP BY product_id, product_name;

Результат:

product_id

product_name

transaction_count

101

Хлеб Бородинский

2

102

Молоко Простоквашино

2

103

Пельмени Цезарь

2

104

Колбаса Докторская

1

105

Майонез Слобода

1

Теперь считаем Confidence для правила пельмени + молоко:

Support(пельмени, молоко) = 2
Support(пельмени) = 2
Confidence = 2/2 = 1,0 (100%)

То есть если купили пельмени, то в 100% случаев купили и молоко (на примере наших трех чеков).

4.3. Лифт (Lift)

Lift показывает, насколько вероятнее покупка Y при покупке X по сравнению со случайной покупкой Y.

Lift(X → Y) = Confidence(X → Y) / Support(Y)

Для правила пельмени + молоко:
Confidence = 1,0
Support(молоко) = 2/3 = 0,67
Lift = 1,0 / 0,67 = 1,5

Интерпретация метрики:
Lift = 1 — независимы (покупка X не влияет на покупку Y)
Lift > 1 — положительная связь (X увеличивает вероятность Y)
Lift < 1 — отрицательная связь (X уменьшает вероятность Y)

Lift = 1,5 означает, что при покупке пельменей вероятность купить молоко в 1,5 раза выше, чем просто случайная вероятность покупки молока.

4.4. Финальный запрос со всеми метриками

Соберем все метрики в одном запросе:

WITH 
product_freq AS (
  
-- частотность каждого товара
    SELECT 
        product_id,
        COUNT(DISTINCT transaction_id) AS product_freq
    FROM transaction_items
    GROUP BY product_id
),
total_transactions AS (
    
-- общее число чеков
    SELECT COUNT(DISTINCT transaction_id) AS total FROM transaction_items
),
pair_counts AS (
  
-- частотность пар
    SELECT 
        ti1.product_id AS a_id,
        ti2.product_id AS b_id,
        COUNT(*) AS pair_freq
    FROM transaction_items ti1
    JOIN transaction_items ti2 
        ON ti1.transaction_id = ti2.transaction_id
        AND ti1.product_id < ti2.product_id
    GROUP BY ti1.product_id, ti2.product_id
)

SELECT 
    p1.product_name AS product_a,
    p2.product_name AS product_b,
    pc.pair_freq,
    ROUND(100.0 * pc.pair_freq / tt.total, 2) AS support_percent,
    ROUND(100.0 * pc.pair_freq / pf_a.product_freq, 2) AS confidence_percent,
    ROUND((pc.pair_freq * tt.total) / (pf_a.product_freq * pf_b.product_freq), 2) AS lift
FROM pair_counts pc
CROSS JOIN total_transactions tt
JOIN products p1 ON pc.a_id = p1.product_id
JOIN products p2 ON pc.b_id = p2.product_id
JOIN product_freq pf_a ON pc.a_id = pf_a.product_id
JOIN product_freq pf_b ON pc.b_id = pf_b.product_id
ORDER BY lift DESC;

5. Продвинутый анализ: когда больше двух товаров

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

-- тройки товаров
SELECT 
    ti1.product_id AS a_id,
    ti2.product_id AS b_id,
    ti3.product_id AS c_id,
    COUNT(*) AS triple_freq
FROM transaction_items ti1
JOIN transaction_items ti2 
    ON ti1.transaction_id = ti2.transaction_id AND ti1.product_id < ti2.product_id
JOIN transaction_items ti3 
    ON ti1.transaction_id = ti3.transaction_id 
    AND ti2.product_id < ti3.product_id
GROUP BY ti1.product_id, ti2.product_id, ti3.product_id;

Условия ti1.product_id < ti2.product_id AND ti2.product_id < ti3.product_id также, как в предыдущем примере с парами обеспечивают уникальность комбинации (избегаем перестановок).

6. Анализ с учетом выручки

Менеджеру важно не только что с чем, но и сколько денег нам все это приносит, поэтому добавим стоимость.

6.1. Ищем товары с максимальной выручкой в парах

WITH pair_revenue AS (
    SELECT 
        ti1.transaction_id,
        ti1.product_id AS a_id,
        ti2.product_id AS b_id,
        ti1.price * ti1.quantity + ti2.price * ti2.quantity AS pair_sum
    FROM transaction_items ti1
    JOIN transaction_items ti2 
        ON ti1.transaction_id = ti2.transaction_id
        AND ti1.product_id < ti2.product_id
)
SELECT 
    p1.product_name AS product_a,
    p2.product_name AS product_b,
    COUNT(*) AS times_together,
    SUM(pr.pair_sum) AS total_revenue_from_pairs,
    AVG(pr.pair_sum) AS avg_check_with_this_pair
FROM pair_revenue pr
JOIN products p1 ON pr.a_id = p1.product_id
JOIN products p2 ON pr.b_id = p2.product_id
GROUP BY p1.product_name, p2.product_name
ORDER BY total_revenue_from_pairs DESC;

6.2. Считаем долю кросс-продаж

Для каждого товара посчитаем, какую долю выручки он нам приносит в сочетании с другими товарами:

WITH product_total AS (
    SELECT 
        product_id,
        SUM(price * quantity) AS total_revenue
    FROM transaction_items
    GROUP BY product_id
),

pair_revenue_for_product AS (
    SELECT 
        ti1.product_id AS product_id,
        SUM(ti1.price * ti1.quantity + ti2.price * ti2.quantity) AS revenue_in_pairs
    FROM transaction_items ti1
    JOIN transaction_items ti2 
        ON ti1.transaction_id = ti2.transaction_id
        AND ti1.product_id < ti2.product_id
    GROUP BY ti1.product_id
)

SELECT 
    p.product_name,
    pt.total_revenue,
    COALESCE(prfp.revenue_in_pairs, 0) AS revenue_in_pairs,
    ROUND(100.0 * COALESCE(prfp.revenue_in_pairs, 0) / pt.total_revenue, 2) AS cross_sell_share_percent
FROM products p
JOIN product_total pt ON p.product_id = pt.product_id
LEFT JOIN pair_revenue_for_product prfp ON p.product_id = prfp.product_id
ORDER BY cross_sell_share_percent DESC;

7. Полный пайплайн задачи

Теперь на больших синтетических данных (20 чеков) посмотрим, как это работает в реальности.

Пример данных (транзакции):

Транзакции с 1 по 20, где:

Чек 1: молоко, хлеб, пельмени
Чек 2: молоко, хлеб, масло
Чек 3: молоко, пельмени, майонез, кетчуп
... и так далее

Результат анализа (топ-5 пар по лифту):

Товар А

Товар В

Вместе

Поддержка, %

Достоверность, %

Лифт

Пельмени

Майонез

8

40,0

80,0

2,67

Пельмени

Кетчуп

7

35,0

70,0

2,33

Хлеб

Масло

5

25,0

50,0

2,00

Молоко

Хлеб

6

30,0

42,9

1,43

Молоко

Пельмени

7

35,0

50,0

1,25

Пример выводов по данным:

  1. Пельмени и майонез – у нас самая сильная связка, лифт 2,67 означает, что при покупке пельменей вероятность купить майонез почти в 3 раза выше случайной, их нужно размещать рядом, это хорошая возможность для кросс-продажи.

  2. Пельмени и кетчуп – это тоже достаточно сильная связка (лифт у пары 2,33), значит рядом с пельменями нам нужна зона соусов.

  3. Хлеб и масло - казалось бы, классическая пара продуктов, но лифт ниже 2,0, поэтому возможно, масло покупают и просто так и наша интуиция нас подводит.

  4. Молоко и хлеб – пара встречаются достаточно часто, но лифт близок к 1, что говорит нам о том, что зависимость слабая, значит это просто популярные товары, которые покупают независимо.

Пример рекомендация для менеджера:
Создать пельменный островок: пельмени (заморозка) + рядом или над холодильником стеллаж с майонезом, кетчупом, соусами, это увеличит кросс-продажи и средний чек на 1 клиента.

8. Еще один полезный запрос

Для каждого товара покажем топ-3 сопутствующих товара:

WITH pairs_with_rank AS (
--подзапрос с рейтингом
    SELECT 
        a_id,
        b_id,
        pair_freq,
        ROW_NUMBER() OVER(PARTITION BY a_id ORDER BY pair_freq DESC) AS rn
    FROM pair_counts
)

--итог с ограничением на топ 3
SELECT 
    p1.product_name AS product,
    p2.product_name AS frequently_bought_with,
    pair_freq
FROM pairs_with_rank pwr
JOIN products p1 ON pwr.a_id = p1.product_id
JOIN products p2 ON pwr.b_id = p2.product_id
WHERE rn <= 3
ORDER BY product, rn;

Вместо заключения

Мы разобрали один из множества вариантов анализа покупательской корзины, который можно провести на чистом SQL. Но важно помнить, что результат аналитики – это не просто цифры и это не просто понимание, что покупают наши клиенты, это возможность увеличения среднего чека, повышение эффек��ивности выкладки товаров и возможности для персонализации рекомендаций клиентам.

📚Статья с примерами задач из аналитики ассортимента https://habr.com/ru/articles/1005262/

✔️Больше про будни и задачи аналитика данных в моем тг 🌸Таня и Данные📊