在一个相当简单的数据库(Firebird 3.0)中,我有一个库存表,其中包含与产品相关的字段:
购买
QTY PRICE VALUE Date
10 10 100 1.12.2018
25 9 225 5.12.2018
30 8 240 12.12.2018
50 7 350 14.12.2018
100 6 600 22.12.2018
总计(数量、价格、价值):215、40、1515
在这里,售出数量是 150,我需要对"购买"中最早的记录求和(值),直到sum(QTY) = SOLD QTY
。
这可以使用 Firebird 3 支持的窗口聚合轻松解决:
select *
,case when cumulative_qty <= sold_qty then qty
when sold_qty-(cumulative_qty - qty) < 0 then 0
else sold_qty-(cumulative_qty - qty)
end as qty_sold
,case when cumulative_qty <= sold_qty then qty
when sold_qty-(cumulative_qty - qty) < 0 then 0
else sold_qty-(cumulative_qty - qty)
end * price as value_sold
from
(
select *
,150 as sold_qty
-- cumulative sum of quantity in stock (FIFO)
,sum(qty) over (order by date rows unbounded preceding) as cumulative_qty
from tab
) as dt
;
现在您可以计算总和:
select
sum(case when cumulative_qty <= sold_qty then qty
when sold_qty-(cumulative_qty - qty) < 0 then 0
else sold_qty-(cumulative_qty - qty)
end) as qty_sold
,sum(case when cumulative_qty <= sold_qty then qty
when sold_qty-(cumulative_qty - qty) < 0 then 0
else sold_qty-(cumulative_qty - qty)
end * price) as value_sold
from
(
select *
,150 as sold_qty
-- cumulative sum of quantity in stock (FIFO)
,sum(qty) over (order by date rows unbounded preceding) as cumulative_qty
from tab
) as dt
;
参见 db<>fiddle(使用 Postgres,但 Firebird 语法应该相同)