Матрица ABC-XYZ по выручке
| Выручка, ₽ | Артикулов | Группа | Доля выручки |
|---|---|---|---|
| 0 ₽ | 0 | AX | 0 % |
| 194 877 150 ₽ | 7 | AY | 56,4 % |
| 64 798 471 ₽ | 3 | AZ | 18,8 % |
| 31 394 853 ₽ | 2 | BX | 9,1 % |
| 18 950 373 ₽ | 1 | BY | 5,5 % |
| 16 297 960 ₽ | 1 | BZ | 4,7 % |
| 11 337 180 ₽ | 1 | CX | 3,3 % |
| 0 ₽ | 0 | CY | 0 % |
| 7 998 966 ₽ | 2 | CZ | 2,3 % |
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date))::DATE AS last_month,
(date_trunc('month', max(date))::DATE - INTERVAL 11 MONTH)::DATE AS first_month
FROM sales
),
months AS (
SELECT unnest(
generate_series(first_month::TIMESTAMP, last_month::TIMESTAMP, INTERVAL 1 MONTH)
)::DATE AS month
FROM bounds
),
monthly_sales AS (
SELECT
date_trunc('month', date)::DATE AS month,
sku,
any_value(product) AS product,
any_value(category) AS category,
sum(revenue - returned_revenue) AS revenue
FROM sales
CROSS JOIN bounds
WHERE date >= first_month
AND date < (last_month + INTERVAL 1 MONTH)::DATE
GROUP BY 1, 2
),
skus AS (
SELECT
sku,
any_value(product) AS product,
any_value(category) AS category
FROM monthly_sales
GROUP BY sku
),
monthly_full AS (
SELECT
s.sku,
s.product,
s.category,
m.month,
coalesce(ms.revenue, 0) AS revenue
FROM skus s
CROSS JOIN months m
LEFT JOIN monthly_sales ms
ON ms.sku = s.sku
AND ms.month = m.month
),
sku_stats AS (
SELECT
sku,
any_value(product) AS product,
any_value(category) AS category,
sum(revenue) AS total_revenue,
avg(revenue) AS avg_monthly_revenue,
stddev_samp(revenue) AS std_monthly_revenue,
CASE
WHEN avg(revenue) = 0 THEN NULL
ELSE stddev_samp(revenue) / avg(revenue)
END AS cv
FROM monthly_full
GROUP BY sku
HAVING sum(revenue) > 0
),
abc_ranked AS (
SELECT
*,
sum(total_revenue) OVER (
ORDER BY total_revenue DESC, sku
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue,
sum(total_revenue) OVER () AS all_revenue
FROM sku_stats
),
abc_class AS (
SELECT
*,
CASE
WHEN cumulative_revenue / all_revenue <= 0.8 THEN 'A'
WHEN cumulative_revenue / all_revenue <= 0.95 THEN 'B'
ELSE 'C'
END AS abc,
CASE
WHEN cv IS NULL THEN 'Z'
WHEN cv <= 0.10 THEN 'X'
WHEN cv <= 0.25 THEN 'Y'
ELSE 'Z'
END AS xyz
FROM abc_ranked
),
matrix AS (
SELECT
g.abc,
g.xyz,
g.abc || g.xyz AS group_code,
count(a.sku) AS sku_count,
coalesce(sum(a.total_revenue), 0) AS revenue
FROM (
SELECT abc, xyz
FROM (VALUES ('A'), ('B'), ('C')) AS a(abc)
CROSS JOIN (VALUES ('X'), ('Y'), ('Z')) AS x(xyz)
) g
LEFT JOIN abc_class a
ON a.abc = g.abc
AND a.xyz = g.xyz
GROUP BY g.abc, g.xyz
)
SELECT
group_code,
sku_count,
round(revenue, 0) AS revenue,
round(revenue / NULLIF((SELECT sum(total_revenue) FROM abc_class), 0), 4) AS revenue_share
FROM matrix
ORDER BY group_code