ABC-анализ
select set_config('custom.prev', '0', false);
select
h.num, h.items_id, h.sname, h.fprice, h.dola, h.suma_dola,
case
when h.suma_dola < 0.5 then 'A'
when h.suma_dola between 0.5 and 0.85 then 'B'
else 'C'
end ::char(1) rank
from
(
select
f.num num,
f.items_id,
f.sname,
f.fprice,
f.dola,
f.dola+current_setting('custom.prev')::FLOAT as suma_dola,
set_config('custom.prev', cast(f.dola+current_setting('custom.prev')::FLOAT as text), false)
from
(
-- id товара, название, общая выручка, Доля накопительного итога
select
row_number() over() num,
t.items_id,
t.sname,
t.fprice,
t.fprice/ sum(t.fprice) over () dola
from
(
-- id товара, название, сумма, (за указанный период + категорию)
select
s.items_id,
i.sname,
sum(s.price * s.icount) fprice
-- c.sname
from
sales as s,
items as i,
cats as c
where i.id = s.items_id
and i.cat_id = c.id
/*
and i.cat_id = 59
and s.ddate BETWEEN '01.01.2016' and '01.04.2016'
*/
group by s.items_id, i.sname
order by 3 desc) as t
) as f
) as h;