ABC анализ на SQL с применением оконных функций.

Давно было интересно реализовать на SQL разбиение множества товаров на группы ABC.

Создадим таблицу для примера.

CREATE TABLE test.abc_data
(
  product_name text,
  sum_sale numeric(14,2)
);

insert into test.abc_data
values  (1, 15429930), (2, 13497652), (3, 12796790), (4, 12209909), (5, 12209909), (6, 11529588), (7, 10942376), (8,  7166437), (9,  2730968);

ABC - скрипт на PostgreSQL через оконные функции.

select t.product_name,
       t.persent,
       t.sum_prod/t.sum_sale as present_prod,
       t.ranks,
       case 
          when t.sum_prod/t.sum_sale < 0.8 then 'A'
          when t.sum_prod/t.sum_sale between 0.8 and 0.95 then 'B'
 	  else 'C' 
       end as product_type
from
     (
        SELECT product_name,
               sum_sale/sum(sum_sale) over() as persent,
               sum(sum_sale) over(order by sum_sale DESC, product_name) as sum_prod,
               rank() over(order by sum_sale DESC, product_name) as ranks,
               sum(sum_sale) over() as sum_sale
        FROM  test.abc_data
     ) as t

 

Объясним работу SQL скрипта:

1. В запросе вычисляется процент от общей суммы для каждой строки test.test_abc_data.sum_sale/sum(test.test_abc_data.sum_sale) over() без разбиения на разделы, то есть по всем полям.
2. Использование в запросе функции rank() over(order by test.test_abc_data.sum_sale DESC, product_name), которая нумерует строки в зависимости от: sum_sale - по убыванию и product_name - по возрастанию.
3. Накопление суммы идет с нарастающим итогом в sum(test.test_abc_data.sum_sale) over(order by test.test_abc_data.sum_sale DESC, product_name) as sum_prod. Объяснение over(...) аналогично пункту 2.
4. Общая сумма рассчитывается через sum(test.test_abc_data.sum_sale) over() as sum_sale.

Результат работы скрипта.

Ссылки на материалы:

1. Пример 1. ABC и XYZ анализ, модифицированная BCG матрица и их расчет (видео)
2. Оконные функции http://postgresql.ru.net/manual/tutorial-window.html
3. Выражения, возвращающие одиночное значение http://postgresql.ru.net/manual/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
4. Реализация в Microsoft SQL Server  http://www.sql.ru/forum/912079/abc-analiz-v-sql

• Вконтакте ()
• Google+
• Facebook ( )