Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ABC анализ в sql  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2761
Добрый день!

Подскажите, пожалуйста, возможно ли сделать ABC-анализ запросом.
Краткое пояснение про ABC-анализ: одно из применений закона Парето 80/20.
Задаес две границы для определения зоны B. В данном примере (см. таблицу 1) это 80 и 95% от общего колличества. Весь крупняк по общей сумме меньший, чем 80% итога сносится в группу А.

Таблица1
NAMESUMABC_RAT|PROCPROC_SUM
Наименование 115 429 930А|16%16%
Наименование 213 497 652А|14%29%
Наименование 312 796 790А|13%42%
Наименование 412 209 909А|12%55%
Наименование 512 022 677А|12%67%
Наименование 611 529 588А|12%79%
Наименование 710 942 376В|11%90%
Наименование 87 166 437С|7%97%
Наименование 92 730 968С|3%100%
ИТОГО98 326 327X|100%200%
*поля PROC и PROC_SUM - вспомогательные, для наглядности.

Спасибо!
23 янв 12, 17:15    [11954272]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
declare @t table ( name char(1), val int )
insert into @t
values (1,	15429930),(2,	13497652),(3,	12796790),
(4,	12209909), (5,	12022677), (6,	11529588),
(7,	10942376), (8,	7166437), (9,	2730968)

select name, val
     , cast( val*1./a * 100 as decimal(10,2) ) [percent]
     , cast( b*1./a * 100 as decimal(10,2) ) [percent_sum], 
case 
when cast( b*1./a * 100 as decimal(10,2) )  between 0 and 80 then 'A'
when cast( b*1./a * 100 as decimal(10,2) )  > 95 then 'C'
else 'B' end [ABC]
from @t t
cross apply ( select sum(val) a from @t) t1
cross apply ( select sum(val) b from @t where val >= t.val ) t2
23 янв 12, 19:19    [11955564]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
declare @t table ( name char(1), val int )
insert into @t
values (1, 15429930), (2, 13497652), (3, 12796790)
     , (4, 12209909), (5, 12022677), (6, 11529588)
     , (7, 10942376), (8, 7166437), (9, 2730968);
select a.name, a.val, 100. * a.val / sum(a.val) over() as pct
     , 100. * sum(b.val) / sum(a.val) over() as run_pct
     , case
       when 100. * sum(b.val) / sum(a.val) over() > 95 then 'C'
       when 100. * sum(b.val) / sum(a.val) over() > 80 then 'B'
       else 'A' end as abc
from @t as a, @t as b
where b.val >= a.val
group by a.name, a.val, a.val;
23 янв 12, 21:26    [11956303]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2761
andrey odegov, спасибо большое!
24 янв 12, 09:41    [11957841]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
PlanB,
и на каких у вас это объёмах данных?
24 янв 12, 10:14    [11958050]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2761
Дедушка
PlanB,
и на каких у вас это объёмах данных?
не много, порядка 1000 строк
24 янв 12, 10:38    [11958243]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
ybuivovk
Member

Откуда:
Сообщений: 23
andrey odegov,
Здравствуйте Андрей!
У меня проблема запросом для ABC анализа.....не получается вывести накопительный %....я не знаю как уж его сделать...
вот пример кода:

declare @a int
declare @t table ( name int, sum_pokup int, Dolia int)

SELECT DISTINCT @a = sum(REWARD_TRNX_DETAIL.VOLUME_VALUE)
FROM
LOYALTY.REWARD_TRNX
LEFT OUTER JOIN LOYALTY.REWARD_TRNX_DETAIL
ON REWARD_TRNX_DETAIL.TRNX_ID = REWARD_TRNX.TRNX_ID
LEFT OUTER JOIN NODE.NODE
ON REWARD_TRNX.SYSTEM_NODE_ID = NODE.SYSTEM_NODE_ID

INSERT INTO @t
SELECT DISTINCT name = NODE.SYSTEM_NODE_ID --AS name
, sum_pokup = sum(REWARD_TRNX_DETAIL.VOLUME_VALUE) --AS sum_pokup
, Dolia = 100.* sum(REWARD_TRNX_DETAIL.VOLUME_VALUE) / @a --AS Dolia
FROM
LOYALTY.REWARD_TRNX
LEFT OUTER JOIN LOYALTY.REWARD_TRNX_DETAIL
ON REWARD_TRNX_DETAIL.TRNX_ID = REWARD_TRNX.TRNX_ID
LEFT OUTER JOIN NODE.NODE
ON REWARD_TRNX.SYSTEM_NODE_ID = NODE.SYSTEM_NODE_ID
WHERE
REWARD_TRNX.SYSTEM_NODE_ID IS NOT NULL
GROUP BY
NODE.SYSTEM_NODE_ID

SELECT DISTINCT b.name
, b.sum_pokup
, b.Dolia
-- , d.sum_pokup / b.sum_pokup AS Nakop_dolia ----вот эта сумма не правильно выводитсяююю

FROM
LOYALTY.REWARD_TRNX
LEFT OUTER JOIN LOYALTY.REWARD_TRNX_DETAIL
ON REWARD_TRNX_DETAIL.TRNX_ID = REWARD_TRNX.TRNX_ID
LEFT OUTER JOIN NODE.NODE
ON REWARD_TRNX.SYSTEM_NODE_ID = NODE.SYSTEM_NODE_ID
, @t AS b, @t AS d
WHERE
d.sum_pokup >= b.sum_pokup
AND REWARD_TRNX.SYSTEM_NODE_ID IS NOT NULL

GROUP BY
b.name
, b.sum_pokup
, b.Dolia
--, d.sum_pokup / b.sum_pokup ---накопительный процент

ORDER BY
b.sum_pokup DESC
8 май 12, 13:54    [12523404]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
ybuivovk
Member

Откуда:
Сообщений: 23
Если кто-то поможет, с меня причитается...честное слово!!!Я на все согласен, чтобы сделать эту хрень....
8 май 12, 13:55    [12523409]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
aleks2
Guest
ybuivovk

SELECT DISTINCT @a = sum(REWARD_TRNX_DETAIL.VOLUME_VALUE)
FROM
  LOYALTY.REWARD_TRNX
  LEFT OUTER JOIN LOYALTY.REWARD_TRNX_DETAIL
    ON REWARD_TRNX_DETAIL.TRNX_ID = REWARD_TRNX.TRNX_ID
  LEFT OUTER JOIN NODE.NODE
    ON REWARD_TRNX.SYSTEM_NODE_ID = NODE.SYSTEM_NODE_ID



Поведай нам сакральный смысл DISTINCT в энтом запросе.
8 май 12, 19:34    [12524648]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
ybuivovk
Member

Откуда:
Сообщений: 23
aleks2,
Да это я так...скопировал с конструктора....
Я уже сделал ABC анализ по аналогии

declare @t table ( name char(1), val int )
insert into @t
values (1, 15429930), (2, 13497652), (3, 12796790)
, (4, 12209909), (5, 12022677), (6, 11529588)
, (7, 10942376), (8, 7166437), (9, 2730968);
select a.name, a.val, 100. * a.val / sum(a.val) over() as pct
, 100. * sum(b.val) / sum(a.val) over() as run_pct
, case
when 100. * sum(b.val) / sum(a.val) over() > 95 then 'C'
when 100. * sum(b.val) / sum(a.val) over() > 80 then 'B'
else 'A' end as abc
from @t as a, @t as b
where b.val >= a.val
group by a.name, a.val, a.val;

но дело в том, что.... допустим есть 2 одинаковых числа, то накопительный % тобишь " 100. * sum(b.val) / sum(a.val) over() as run_pct " не правильно считает. Он не суммирует процент....:( Как исправить не подскажете?
10 май 12, 17:15    [12531782]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
sqlinbox
Guest
ybuivovk,

declare @t table ( name char(1), val int )
insert into @t
values
(1, 15429930),
(2, 13497652),
(3, 12796790),
(4, 12209909),
(5, 12209909),
--(5, 12022677),  сделал 2 одинаковых значения
(6, 11529588),
(7, 10942376),
(8,  7166437),
(9,  2730968);

-- ваш вариант

select
a.name,
a.val,
100. * a.val / sum(a.val) over() as pct,
100. * sum(b.val) / sum(a.val) over() as run_pct,
case
when 100. * sum(b.val ) / sum(a.val) over() > 95 then 'C'
when 100. * sum(b.val ) / sum(a.val) over() > 80 then 'B'
else 'A' end as abc
from @t as a
full outer join @t b on 2>1
where b.val >= a.val
group by a.name, a.val
ORDER BY a.name DESC

-- исправленный вариант

declare @t2 table ( name char(1), val int, sm  Int)
insert into @t2
select name, val , 0
from @t order by val desc, name
declare @sm int = 0
update @t2 set @sm = @sm + val, sm = @sm from @t2

select
a.name,
a.val,
100. * a.val / @sm as pct,
100. * a.sm / @sm as run_pct,
case
when 100. * a.sm / @sm > 95 then 'C'
when 100. * a.sm / @sm > 80 then 'B'
else 'A' end as abc
from @t2 as a
order by 100. * a.sm / @sm desc
10 май 12, 19:30    [12532378]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: ABC анализ в sql  [new]
Mike_Gl
Member

Откуда:
Сообщений: 40
Добрый !
Хочу апнуть темку, поднятую автором поста.
Для одного сплошного уровня, как в его примере, всё работает идеально.

Но в жизни необходимо считать АВС по группам, видам и т.п.
Бьюсь пару дней уже сам, но как-то не хватает объемного зрения и опыта, чтобы также в одном запросе решить эту задачку.
+ Исходный вариант от автора (уменьшил порядок чисел для наглядности):

declare @t table ( id int, val int )
	insert into @t
	values (1, 154), (2, 134), (3, 127)
		 , (4, 122), (5, 120), (6, 115)
		 , (7, 82), (8, 71), (9, 27);

	select 
		a.id, 
		a.val, 
		100. * a.val / sum(a.val) over() as pct
		 , 100. * sum(b.val) / sum(a.val) over() as run_pct
		 , case
		   when 100. * sum(b.val) / sum(a.val) over() > 95 then 'C'
		   when 100. * sum(b.val) / sum(a.val) over() > 80 then 'B'
		   else 'A' end as abc
	from @t as a, @t as b
	where b.val >= a.val
	group by a.id, a.val
	order by a.val desc


Давайте добавим в эту таблицу группы : PID = Parent ID, код родительской папки
Разбил все числа на две группы, по очереди, 1 / 2
и наша цель - посчитать АВС для каждой группы отдельно

declare @t table ( id int, PID int, val int )
insert into @t
values (1, 1, 154), (2, 2, 134), (3, 1, 127)
     , (4, 2, 122), (5, 1, 120), (6, 2, 115)
     , (7, 1, 82), (8, 2, 71), (9, 1, 27);


При попытке сделать аналогично первому решению - в одном запросе - получается какая-то ерунда.
Даже не хочу выкладывать свои попытки.
Вот и прошу уважаемых форумчан помочь и посоветовать, как лучше рассчитать АВС-категории с учетом разбивки на группы.

Есть мысль пойти через врем.таблицу, куда в цикле размещать одну группу , т.е. приводить к 1му варианту или через CTE ...
В идеале нужно применять этот механизм для 3х- 4х вложенных групп (например, Фабрика-Группа-Коллекция).
Т.е. нужно посчитать, какую категорию будет иметь товар в каждом уровне вложения.
3 мар 16, 13:15    [18891747]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
msLex
Member

Откуда:
Сообщений: 8204
Mike_Gl,

да вроде ничего сложного, просто добавить группировку по соответствующему полю

declare @t table ( id int, PID int, val int )
insert into @t
values (1, 1, 154), (2, 2, 134), (3, 1, 127)
     , (4, 2, 122), (5, 1, 120), (6, 2, 115)
     , (7, 1, 82), (8, 2, 71), (9, 1, 27);


select a.id, a.PID, a.val, 100. * a.val / sum(a.val) over(partition by a.PID) as pct
     , 100. * sum(b.val) / sum(a.val) over(partition by a.PID) as run_pct
     , case
       when 100. * sum(b.val) / sum(a.val) over(partition by a.PID) > 95 then 'C'
       when 100. * sum(b.val) / sum(a.val) over(partition by a.PID) > 80 then 'B'
       else 'A' end as abc
from @t as a
inner join @t as b on a.PID = b.PID
where b.val >= a.val
group by a.id, a.val, a.val, a.PID;
3 мар 16, 13:58    [18892068]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
Mike_Gl
Member

Откуда:
Сообщений: 40
msLex
Mike_Gl,

да вроде ничего сложного, просто добавить группировку по соответствующему полю


и правда работает
а я добавлял partition везде.. и в sum(b.val) тоже делал over(). Получается каша.
Спасибо большое!

А сам я что-то видимо не догоняю, не вижу, как оно должно работать :-(
3 мар 16, 15:39    [18892724]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
Mike_Gl
Member

Откуда:
Сообщений: 40
msLex
Mike_Gl,
 
....
group by a.id, a.val, a.val, a.PID;


А зачем здесь два раза a.val ?
3 мар 16, 15:41    [18892734]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Mike_Gl,

Зачем тут вообще JOIN

declare @t table ( id int, PID int, val int )
insert into @t
values (1, 1, 154), (2, 2, 134), (3, 1, 127)
     , (4, 2, 122), (5, 1, 120), (6, 2, 115)
     , (7, 1, 82), (8, 2, 71), (9, 1, 27);

SELECT A.* ,100. * pct / run_pct,
	case
	when 100. * pct / run_pct < 55 then 'C'
	when 100. * pct / run_pct < 85 then 'B'
	else 'A' end as abc
FROM 
(
	select id, PID, val, sum(a.val) over(partition by a.PID ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as pct
		 , sum(a.val) over(partition by a.PID) as run_pct
	from @t as a
) A
ORDER BY PID, val DESC 
3 мар 16, 16:27    [18893034]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
Mike_Gl
Member

Откуда:
Сообщений: 40
a_voronin
Mike_Gl,

Зачем тут вообще JOIN

	(select id, PID, val, sum(a.val) over(partition by a.PID ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as pct
) A
ORDER BY PID, val DESC 

Вау!
Такая конструкция для меня в новинку. Пошел читать мсдн.

А ответы получаются разные.
Идея примерно понятна, но надо разобраться, почему....
3 мар 16, 16:38    [18893093]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Mike_Gl,

Я проценты другие поставил , потому что все в C падало
3 мар 16, 16:41    [18893111]     Ответить | Цитировать Сообщить модератору
 Re: ABC анализ в sql  [new]
msLex
Member

Откуда:
Сообщений: 8204
Mike_Gl
msLex
Mike_Gl,
 
....
group by a.id, a.val, a.val, a.PID;


А зачем здесь два раза a.val ?

х/з, я взял исходный запрос и просто добавил группировку
3 мар 16, 17:26    [18893370]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить