ABC-анализ

Часто при анализе данных возникает потребность в выделении наиболее важных элементов из большого объёма данных. В этом может помочь ABC анализ. Основная идея этого вида анализа заключается в разделении данных на категории по величине содержащихся в них значений. Наибольшие значения относятся к категории A. Наименьшие значения относятся к категории C. Промежуточные значения к категории B.

Решение для ABC-анализа

Шаг 1

Сначала нужно определить долю каждого значения в сумме всех значений. Для этого нужно найти сумму всех значений и разделить на нее каждое значение.

Пример в Excel:

	A	B	C	D	E
1	Переменная	Значение
2	Всего
3	A1	11
4	B2	22
5	C3	33
6	D4	44
7	E5	55
8	F6	66
9	G7	77

Решение в Excel

//Ячейка B2
=СУММ(B:B)

//Ячейки C3... C9
=B3/B$2

Результат:

	A	B	C	D	E
1	Переменная	Значение	Доля
2	Всего	308	1
3	A1	11	0,0357
4	B2	22	0,0714
5	C3	33	0,1071
6	D4	44	0,1429
7	E5	55	0,1786
8	F6	66	0,2143
9	G7	77	0,25

Решение в PostgreSQL

SELECT *,
       CAST(value AS FLOAT) / SUM(value) OVER () AS fraction
FROM abc;

Результат:

	variable	value	fraction
0	A1	11	0.0357
1	B2	22	0.0714
2	C3	33	0.1071
3	D4	44	0.1429
4	E5	55	0.1786
5	F6	66	0.2143
6	G7	77	0.25

Шаг 2

Далее требуется отсортировать все значения по убыванию и отдельно рассчитать для каждого значения долю нарастающим итогом. Это делается путем суммирования доли текущего значения со всеми предшествующими.

Решение в Excel

//D3... D9
=C3+D2

Результат:

	A	B	C	D	E
1	Переменная	Значение	Доля	Нараст. итогом
2	Всего	308 ▼
3	G7	77	0,25	0,25
4	F6	66	0,2143	0,4643
5	E5	55	0,1786	0,6429
6	D4	44	0,1429	0,7858
7	C3	33	0,1071	0,8929
8	B2	22	0,0714	0,9643
9	A1	11	0,0357	1

Решение в PostgreSQL

WITH abc_step_1 AS (SELECT *,
                           CAST(value AS FLOAT) / SUM(value) OVER () AS fraction
                    FROM abc)

SELECT *,
       COALESCE(SUM(fraction) OVER (ORDER BY fraction
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
                0) AS cumulative
FROM abc_step_1
ORDER BY value DESC;

Результат:

	variable	value	fraction	cumulative
0	G7	77	0.25	0.25
1	F6	66	0.2143	0.4643
2	E5	55	0.1786	0.6429
3	D4	44	0.1429	0.7858
4	C3	33	0.1071	0.8929
5	B2	22	0.0714	0.9643
6	A1	11	0.0357	1

Шаг 3

На следующем шаге нужно присвоить каждому значению категорию. К категории A следует отнести значения с долей с нарастающим итогом до 0,5 или 50%. К категории B отнести значения с долей нарастающим итогом до 0,75 или 75%. Оставшиеся значения следует отнести к категории C.

Решение в Excel

//E3... E9
=ЕСЛИ(D3<0,5;"A";ЕСЛИ(D3<0,75;"B";"C"))

Результат:

	A	B	C	D	E
1	Переменная	Значение	Доля	Нараст. итогом	ABC
2	Всего	308 ▼
3	G7	77	0,25	0,25	A
4	F6	66	0,2143	0,4643	A
5	E5	55	0,1786	0,6429	B
6	D4	44	0,1429	0,7858	C
7	C3	33	0,1071	0,8929	C
8	B2	22	0,0714	0,9643	C
9	A1	11	0,0357	1	C

Пример ABC-анализа в файле Excel.

Решение в PostgreSQL

WITH abc_step_1 AS (SELECT *,
                           CAST(value AS FLOAT) / SUM(value) OVER () AS fraction
                    FROM abc),

abc_step_2 AS (SELECT *,
                      COALESCE(SUM(fraction) OVER (ORDER BY fraction
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
                               0) AS cumulative
               FROM abc_step_1
               ORDER BY value DESC)

SELECT *,
       CASE
           WHEN cumulative < .5 THEN 'A'
           WHEN cumulative < .75 THEN 'B'
           ELSE 'C'
       END AS abc
FROM abc_step_2;

Результат:

	variable	value	fraction	cumulative	abc
0	G7	77	0.25	0.25	A
1	F6	66	0.2143	0.4643	A
2	E5	55	0.1786	0.6429	B
3	D4	44	0.1429	0.7858	C
4	C3	33	0.1071	0.8929	C
5	B2	22	0.0714	0.9643	C
6	A1	11	0.0357	1	C

abc анализ практика