fifo stock calculation with window function

Поиск
Список
Период
Сортировка
От Dmitry Teslenko
Тема fifo stock calculation with window function
Дата
Msg-id CAPBNEqO0z2NptKQ8LtjGjgYu=LvgSFN=5gkT6Ca9O3HzPLAahA@mail.gmail.com
обсуждение исходный текст
Список pgsql-sql
Inspired by https://callmeranjeet.wordpress.com/2014/09/01/calculating-stock-with-fifo-method-in-sql/

Here's table which stores stock incomes and sales

CREATE TABLE operations (   id integer,   db_product_id integer,   kr_product_id integer,   qty numeric(20,3),
db_accountcharacter varying(255),   kr_account character varying(255),   mark integer
 
);

INSERT INTO operations VALUES (62310025, 96028, 0, 5.000, '001', '002', NULL);
INSERT INTO operations VALUES (62371515, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62385513, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62757794, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62757795, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62757796, 0, 96028, 1.000, '003', '001', NULL);
INSERT INTO operations VALUES (62910647, 96028, 0, 5.000, '001', '002', NULL);
INSERT INTO operations VALUES (62416041, 0, 96028, 1.000, '003', '001', 1);

and this sql queries incomes and shows what qty were before income
operation, what qty were added to stock, how many items from that
income where sold later, and what qty left:

select
(s.bal - s.qty)::float as before
,s.qty::float as in_
,((s.bal - s.qty) + s.qty - greatest(0, s.sum_))::float as out_
,greatest(0, s.sum_)::float as after
from
(
select
id
,coalesce(db_product_id, kr_product_id) as prd
,case when db_account = '001' then 1 else 0 end as dir
,case when db_account = '001' then qty else 0 - qty end as qty
,sum(case when db_account = '001' then qty else 0 - qty end)   over (order by case when db_account = '001' then 1 else
0end, id) as sum_
 
,sum(case when db_account = '001' then qty else 0 - qty end)   over (order by id) as bal
from operations
where   96028 in (db_product_id, kr_product_id)   and '001' in (db_account, kr_account)
order by dir, id
) s
where dir = 1
;

some sales are special and have mark=1 property. How can I map these
sales to particular income operation?



В списке pgsql-sql по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: pg_stat_acvtivity
Следующее
От: Suresh Raja
Дата:
Сообщение: commit inside a function failing