ABC-анализ в SQL и Python: формула, примеры, границы

Q: Как сделать ABC-анализ в SQL?

Свернуть продажи до выручки по товару через GROUP BY, затем оконной функцией SUM(revenue) OVER (ORDER BY revenue DESC) посчитать нарастающий итог, поделить его на SUM(revenue) OVER () для накопленной доли и присвоить категорию через CASE по границам 80% и 95%. Всё умещается в один запрос без подзапросов с самосоединением.

Коротко: ABC-анализ — это деление объектов (товаров, клиентов, поставщиков) на три группы по их вкладу в общий результат: A — около 80% выручки дают ~20% позиций, B — следующие ~15%, C — оставшиеся ~5%. Опирается он на принцип Парето: считаешь долю каждой позиции в выручке, сортируешь по убыванию, накапливаешь кумулятивную долю и режешь по границам. В SQL это делается оконными функциями за один запрос, в Python — через cumsum и pd.cut по отсортированному DataFrame.

Что такое ABC-анализ и зачем он аналитику

ABC-анализ отвечает на вопрос «на чём держится бизнес». Если у вас 5000 SKU, заниматься всеми одинаково невозможно: запасы, маркетинг, переговоры с поставщиками стоят денег и времени. ABC-анализ показывает, что обычно небольшая доля ассортимента приносит основную выручку — это группа A, к ней максимум внимания: не допускать out-of-stock, держать страховой запас, мониторить маржу. Группа C — длинный хвост, который суммарно даёт мало; его можно вести по упрощённым правилам или выводить из ассортимента.

Классические границы — 80/15/5 по накопленной доле выручки, но это не закон. Иногда удобнее 70/20/10, иногда границы вообще подбираются по форме кривой Парето в конкретных данных. Важно понимать механику, а не заучивать проценты.

Алгоритм всегда один и тот же:

Посчитать метрику вклада по каждой позиции (чаще всего выручку за период).
Отсортировать по убыванию.
Накопить кумулятивную сумму и поделить на общий итог — получить накопленную долю.
Присвоить категорию по границам накопленной доли.

ABC-анализ в SQL

Главный инструмент здесь — оконные функции. Нам нужны две вещи: общая выручка по всем товарам и нарастающий итог при сортировке по убыванию выручки. И то, и другое считается окном без необходимости в подзапросах с группировкой и джойнах самих к себе.

Допустим, есть таблица sales(order_id, product_id, amount). Сначала сворачиваем продажи до выручки по товару, потом одним проходом считаем доли и категории.

WITH product_revenue AS (
    SELECT
        product_id,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY product_id
),
ranked AS (
    SELECT
        product_id,
        revenue,
        -- нарастающий итог выручки при сортировке по убыванию
        SUM(revenue) OVER (
            ORDER BY revenue DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cum_revenue,
        -- общая выручка по всем товарам
        SUM(revenue) OVER () AS total_revenue
    FROM product_revenue
)
SELECT
    product_id,
    revenue,
    ROUND(100.0 * revenue / total_revenue, 2)       AS revenue_share_pct,
    ROUND(100.0 * cum_revenue / total_revenue, 2)    AS cum_share_pct,
    CASE
        WHEN 100.0 * cum_revenue / total_revenue <= 80  THEN 'A'
        WHEN 100.0 * cum_revenue / total_revenue <= 95  THEN 'B'
        ELSE 'C'
    END AS abc_category
FROM ranked
ORDER BY revenue DESC;

Что здесь происходит по шагам:

product_revenue сворачивает заказы до одной строки на товар с суммарной выручкой.
SUM(revenue) OVER (ORDER BY revenue DESC ...) даёт кумулятивную выручку: в первой строке — выручка самого крупного товара, дальше с каждой строкой накапливается.
SUM(revenue) OVER () без ORDER BY и без рамки возвращает общий итог в каждой строке — удобно для деления.
CASE сравнивает накопленную долю с границами: всё, что укладывается в первые 80% накопленной выручки, — это A, до 95% — B, остальное — C.

Тонкий момент с границей: товар, на котором накопленная доля «перешагивает» 80%, попадёт в A целиком, потому что условие проверяется по его собственной накопленной доле. Это стандартное поведение, и оно корректно — граница проходит по позиции, а не разрезает её.

Если нужно ABC по нескольким измерениям сразу (например, по каждому магазину отдельно), добавьте PARTITION BY store_id в обе оконные функции — итог и нарастающая сумма будут считаться внутри каждого магазина независимо. Отработать запрос вживую и поэкспериментировать с границами можно в SQL-тренажёре.

ABC-анализ в Python и pandas

В pandas логика та же, но кумулятивную долю удобнее считать после явной сортировки, а категории присваивать через pd.cut или np.select. Главное правило — сначала сортировка, потом cumsum, иначе нарастающий итог посчитается в случайном порядке и категории будут бессмысленны.

import pandas as pd
import numpy as np

# исходные продажи
sales = pd.read_csv("sales.csv")  # колонки: product_id, amount

# 1. выручка по товару
revenue = (
    sales.groupby("product_id", as_index=False)["amount"]
    .sum()
    .rename(columns={"amount": "revenue"})
)

# 2. сортировка по убыванию выручки — без неё cumsum не имеет смысла
revenue = revenue.sort_values("revenue", ascending=False).reset_index(drop=True)

# 3. накопленная доля выручки
revenue["cum_share"] = revenue["revenue"].cumsum() / revenue["revenue"].sum()

# 4. категории по границам накопленной доли
revenue["abc"] = pd.cut(
    revenue["cum_share"],
    bins=[0, 0.8, 0.95, 1.0],
    labels=["A", "B", "C"],
    include_lowest=True,
)

print(revenue.head())
print(revenue["abc"].value_counts())

pd.cut режет диапазон [0, 1] на три интервала: (0, 0.8] → A, (0.8, 0.95] → B, (0.95, 1.0] → C. include_lowest=True гарантирует, что первая строка с нулевой нижней границей не выпадет в NaN.

Тот же результат через np.select, когда нужна более гибкая логика или несколько условий:

conditions = [
    revenue["cum_share"] <= 0.80,
    revenue["cum_share"] <= 0.95,
]
revenue["abc"] = np.select(conditions, ["A", "B"], default="C")

np.select проверяет условия по порядку и берёт первое подходящее, поэтому отдельное условие для C не нужно — оно покрывается через default. Прогнать этот код и проверить на своих данных можно в Python-тренажёре.

ABC vs XYZ vs RFM

Эти три метода часто путают, хотя они отвечают на разные вопросы.

ABC ранжирует по вкладу в результат — обычно по выручке или прибыли. Отвечает на вопрос «что приносит деньги». Объект анализа — товары, реже клиенты или поставщики.
XYZ классифицирует по стабильности спроса через коэффициент вариации: X — ровный предсказуемый спрос, Y — спрос с сезонными колебаниями, Z — нерегулярный, плохо прогнозируемый. Отвечает на вопрос «насколько товар предсказуем» и нужен для управления запасами.
RFM сегментирует клиентов по поведению: Recency (давность последней покупки), Frequency (частота), Monetary (сумма трат). Отвечает на вопрос «кто наши лучшие клиенты и кто уходит». Подробнее — в разборе RFM-анализа.

На практике ABC и XYZ комбинируют в матрицу 3×3: товар AX (большой вклад, стабильный спрос) ведут по одной стратегии запасов, CZ (мелкий вклад, рваный спрос) — по другой. ABC показывает, что важно, XYZ — насколько это предсказуемо, RFM работает в другой плоскости — с клиентами, а не с ассортиментом.

Типичные ошибки

Границы 80/15/5 как догма. Это отправная точка, а не истина. У одних данных 80% выручки дают 5% товаров, у других — 35%. Полезно построить кривую Парето и смотреть, где она реально перегибается, и уже от формы кривой выбирать границы.

Забыли отсортировать перед cumsum. Самая частая техническая ошибка в pandas. cumsum идёт по текущему порядку строк, и без сортировки по убыванию накопленная доля считается от случайного порядка — категории получаются мусорными. В SQL за порядок отвечает ORDER BY внутри окна, поэтому там эта ошибка встречается реже, но если убрать ORDER BY из окна — нарастающий итог сломается так же.

ABC по количеству, а не по вкладу. Если ранжировать по числу проданных штук, наверх всплывут дешёвые ходовые товары, и группа A перестанет отражать выручку. ABC почти всегда считают по деньгам (выручка или маржа), а не по количеству, — иначе теряется весь смысл выделения «важного».

Не пересчитывают регулярно. ABC — это снимок на момент времени. Товар из A может уйти в C после смены сезона или окончания акции. Если категории зафиксировали год назад и больше не трогали, решения по запасам и закупкам принимаются по устаревшей картине. Пересчитывайте на скользящем окне (например, по последним 3–12 месяцам) и обновляйте по расписанию.

Частые вопросы

Что такое ABC-анализ простыми словами?

Это способ разделить товары (или клиентов) на три группы по важности. Группа A — немногие позиции, дающие основную часть выручки; группа B — среднее звено; группа C — длинный хвост, который суммарно вносит мало. Идея в том, чтобы тратить больше внимания на то, что реально приносит деньги.

Какие границы выбрать для A, B, C?

Стандартная отправная точка — 80% накопленной выручки для A, до 95% для B, остальное C. Но это не жёсткое правило: границы стоит подбирать под конкретные данные, глядя на кривую Парето и точки её перегиба. Для одних ассортиментов лучше работает 70/20/10, для других — свои значения.

Чем ABC-анализ отличается от RFM?

ABC ранжирует объекты по вкладу в выручку и чаще применяется к товарам. RFM сегментирует клиентов по их поведению — давности покупки, частоте и сумме трат. ABC отвечает на вопрос «что приносит деньги», RFM — «кто наши ценные и уходящие клиенты», поэтому они дополняют друг друга, а не заменяют.

Как сделать ABC-анализ в SQL?

Свернуть продажи до выручки по товару через GROUP BY, затем оконной функцией SUM(revenue) OVER (ORDER BY revenue DESC) посчитать нарастающий итог, поделить его на SUM(revenue) OVER () для накопленной доли и присвоить категорию через CASE по границам 80% и 95%. Всё умещается в один запрос без подзапросов с самосоединением.