时间戳

首页 > 解决方案 > FIFO逻辑的实现

sql - FIFO逻辑的实现

问题描述

在一个相当简单的数据库（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

在这里，SOLD QTY 是 150，我需要对“PURCHASE”中最早的记录求和（值），直到sum(QTY) = SOLD QTY.

标签： sqlfirebird

解决方案

这可以使用 Firebird 3 支持的Windowed Aggregates轻松解决：

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 语法应该相同）

推荐阅读