ABC-анализ товаров в SQL: какие позиции дают основную сумму

SQL Studio / Разборы SQL / ABC-анализ товаров в SQL: какие позиции дают основную сумму

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

1. Сколько дал каждый товар

В учебной базе цена указана за единицу товара в копейках, а discount_kopecks — скидка на всю строку. Поэтому стоимость позиции равна quantity * unit_price_kopecks - discount_kopecks.

SELECT i.product_id,
       SUM(i.quantity * i.unit_price_kopecks - i.discount_kopecks) AS sales_kopecks
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
WHERE o.status = 'delivered'
GROUP BY i.product_id
ORDER BY sales_kopecks DESC, i.product_id;

В выборке семь товаров. Первые по сумме: товар 11 — 350 ₽, товар 19 — 300 ₽, товар 14 — 200 ₽. Общий итог — 1 250 ₽, как и сумма доставленных заказов в этом учебном наборе.

2. Накопленная доля через оконную сумму

Вычислим долю после каждой строки, чтобы увидеть, какое количество товаров формирует большую часть оборота.

WITH sold AS (
  SELECT i.product_id,
         SUM(i.quantity * i.unit_price_kopecks - i.discount_kopecks) AS sales_kopecks
  FROM order_items AS i
  JOIN orders AS o ON o.order_id = i.order_id
  WHERE o.status = 'delivered'
  GROUP BY i.product_id
),
ranked AS (
  SELECT product_id, sales_kopecks,
         SUM(sales_kopecks) OVER () AS total_kopecks,
         SUM(sales_kopecks) OVER (
           ORDER BY sales_kopecks DESC, product_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS cumulative_kopecks
  FROM sold
)
SELECT product_id, sales_kopecks,
       ROUND(100.0 * cumulative_kopecks / NULLIF(total_kopecks, 0), 2) AS cumulative_pct
FROM ranked
ORDER BY sales_kopecks DESC, product_id;

Дополнительный ключ product_id делает сортировку устойчивой при равных суммах. Если данных нет или общий итог равен нулю, деление вернёт NULL благодаря NULLIF; придуманные проценты показывать нельзя.

3. Разделить товары на A, B и C

Для иллюстрации выберем пороги: A — до 80% накопленной суммы включительно, B — свыше 80% до 95%, C — остальное. Это договорённость для примера, а не универсальный норматив.

WITH sold AS (
  SELECT i.product_id,
         SUM(i.quantity * i.unit_price_kopecks - i.discount_kopecks) AS sales_kopecks
  FROM order_items AS i
  JOIN orders AS o ON o.order_id = i.order_id
  WHERE o.status = 'delivered'
  GROUP BY i.product_id
),
ranked AS (
  SELECT product_id, sales_kopecks,
         1.0 * SUM(sales_kopecks) OVER (
           ORDER BY sales_kopecks DESC, product_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) / NULLIF(SUM(sales_kopecks) OVER (), 0) AS cum_share
  FROM sold
)
SELECT product_id,
       CASE WHEN cum_share <= 0.8 THEN 'A'
            WHEN cum_share <= 0.95 THEN 'B'
            ELSE 'C' END AS abc
FROM ranked
ORDER BY sales_kopecks DESC, product_id;

В этой выборке группа A — товары 11, 19, 14 и 12; B — товар 17; C — товары 18 и 15. При другой границе, отрицательных продажах или возвратах состав групп изменится. Для реального ассортимента отдельно решите, как показывать товары без продаж.

Практика

Потренируйте ранжирование товаров внутри категорий, затем выполните полный расчёт ABC в бесплатной песочнице.

Рядом по смыслу: накопительная сумма · возвраты и поступления · TOP-N в группе.

Документация по оконным суммам: SQLite Window Functions.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.