ABC-анализ товаров в SQL: доля накопленной выручки

Категорийный менеджер хочет сосредоточить внимание на товарах, которые дают основную часть выручки. Простая сортировка по продажам покажет лидеров, но не ответит, где заканчивается группа A. Для этого нужна накопленная доля: идём от самого доходного SKU к менее доходным и смотрим, какую часть общей выручки уже покрыли.

В учебном наборе четыре товара дают 600, 250, 100 и 50 рублей. Общая сумма — 1000. После первого товара накоплено 60%, после второго — 85%, после третьего — 95%. На пороге 80% возникает решение: второй товар довёл долю с 60% до 85%. Если отнести его к B по конечной доле, группа A покроет лишь 60%. Мы явно выбираем другое правило: товар, перед которым доля ещё ниже 80%, завершает группу A.

Сначала упорядочить, затем считать

Оконная сумма считает накопление в порядке убывания выручки. При равной выручке добавляем sku, чтобы порядок и классы были воспроизводимыми. Выражение cumulative − revenue даёт долю до текущего товара; по ней и выбираем класс. Аналогично порог 95% разделяет B и C.

CREATE TABLE sales (sku TEXT, revenue INTEGER);
INSERT INTO sales VALUES ('A',600),('B',250),('C',100),('D',50);
WITH ranked AS (
  SELECT sku, revenue,
         SUM(revenue) OVER () AS total,
         SUM(revenue) OVER (
           ORDER BY revenue DESC, sku
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS cumulative
  FROM sales
), shares AS (
  SELECT sku, revenue, cumulative * 1.0 / total AS share,
         (cumulative - revenue) * 1.0 / total AS share_before
  FROM ranked
)
SELECT sku, revenue, ROUND(share, 2) AS cumulative_share,
       CASE WHEN share_before < 0.80 THEN 'A'
            WHEN share_before < 0.95 THEN 'B'
            ELSE 'C' END AS class
FROM shares ORDER BY revenue DESC, sku;

Результат: A с долей 0,60 попадает в класс A; B доводит долю до 0,85 и тоже остаётся в A. Товар C доводит её до 0,95 и попадает в B. Последний D получает C. Итого классы покрывают соответственно 85%, 10% и 5% выручки. Доля A превысила порог 80%, потому что пересекающий его товар целиком входит в класс.

Если товаров много, всегда проверьте, какая строка пересекает порог. Альтернативное правило — определять класс по доле после товара; оно даст другой состав группы A. Ни одно из двух правил не стоит скрывать в SQL без объяснения владельцу решения. Особенно заметна разница, когда один крупный товар сам превышает 80%.

Что считать выручкой товара

Исходная таблица уже содержит один итог на SKU. На реальных данных его сначала собирают из оплаченных позиций с согласованным учётом скидок и возвратов. Если взять цену каталога, а не фактическую сумму продаж, ABC будет классификацией прайс-листа. Если оставить несколько строк на SKU, оконная сумма распределит их как отдельные товары. Эти ошибки меняют не только числа, но и порядок.

В примере нет нулевой общей выручки. Для пустого периода или набора из одних нулей доля не определена; отчёт должен явно показать отсутствие продаж, а не делить на ноль. Класс тоже не стоит присваивать, пока у товаров нет вклада, по которому их можно упорядочить.

Окно продаж тоже меняет результат. Товар с большим разовым заказом за год может попасть в A, но не продаваться в текущем месяце. Если ABC нужен для решения о пополнении склада, сравните несколько периодов и проверьте сезонность; если для годового обзора ассортимента, годовая выручка уместна. Сам SQL остаётся тем же после подготовки входной таблицы на нужный период.

Равные суммы заслуживают отдельного решения. Вторичный порядок sku делает результат стабильным, но два товара с одинаковым вкладом могут оказаться по разные стороны порога. Если это неприемлемо, сначала сгруппируйте товары по равной выручке, присвойте всей группе общий класс, затем верните товары. Размер класса A может стать больше, зато одинаковые по правилу товары не будут разделены случайным алфавитным порядком.

ABC не говорит о маржинальности. Товар может давать большую выручку и маленькую прибыль. Когда решение связано с прибылью, замените входной показатель на согласованную валовую маржу и честно назовите классификацию иначе. Смешивать выручку в числителе и прибыль в бизнес-выводе нельзя: лидер по продажам не обязательно лидер по вкладу в результат.

Расчёт предполагает неотрицательную выручку каждого SKU. Если после возвратов у товара получилась отрицательная чистая сумма, накопленная доля перестанет монотонно расти и пороги потеряют обычный смысл. Такие SKU покажите отдельно или договоритесь о другой базе классификации, например о валовых продажах с отдельным отчётом по возвратам. Просто пропустить отрицательное значение в общей сумме нельзя: знаменатель тогда изменится.

В курсе «Продвинутый SQL» оконные расчёты, предварительная агрегация и сверка результатов доводятся до витрины интернет-магазина. Вы пишете SQL в браузере и проверяете решение на разных наборах данных. Откройте программу, чтобы собирать такие отчёты из исходных заказов, а не только из готовой таблицы продаж.

Если исходная скидка дана на весь заказ, сначала разберите её распределение по товарам; тогда выручка SKU будет иметь понятный смысл.