Коэффициент вариации продаж по артикулам за 12 месяцев
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date)) AS last_month,
date_trunc('month', max(date)) - INTERVAL '11 months' AS first_month
FROM sales
),
months AS (
SELECT unnest(generate_series(first_month, last_month, INTERVAL '1 month'))::DATE AS month
FROM bounds
),
skus AS (
SELECT sku, any_value(product) AS product
FROM sales
WHERE date >= (SELECT first_month FROM bounds)
GROUP BY sku
),
monthly AS (
SELECT
s.sku,
m.month,
COALESCE(SUM(sales.units), 0) AS units
FROM skus s
CROSS JOIN months m
LEFT JOIN sales ON sales.sku = s.sku
AND date_trunc('month', sales.date) = m.month
GROUP BY s.sku, m.month
),
stats AS (
SELECT
s.sku,
s.product,
AVG(m.units) AS avg_month_units,
STDDEV_SAMP(m.units) AS stddev_units,
CASE WHEN AVG(m.units) = 0 THEN NULL ELSE STDDEV_SAMP(m.units) / AVG(m.units) END AS cv
FROM skus s
JOIN monthly m ON m.sku = s.sku
GROUP BY s.sku, s.product
)
SELECT
sku,
product,
round(avg_month_units, 2) AS avg_month_units,
round(cv, 3) AS cv,
CASE WHEN cv <= 0.10 THEN 'X' WHEN cv <= 0.25 THEN 'Y' ELSE 'Z' END AS xyz_group
FROM stats
WHERE avg_month_units > 0
ORDER BY avg_month_units DESC
LIMIT 30