根据先进先出的定价来计算利润

Question

假设我有一些SKU的购买和销售数据：

po_id | sku | purchase_date    | price | qty
----------------------------------------------
    1 | 123 | 2013-01-01 12:25 | 20.15 |   5
    2 | 123 | 2013-05-01 15:45 | 17.50 |   3
    3 | 123 | 2013-05-02 12:00 | 15.00 |   1
    4 | 456 | 2013-06-10 16:00 | 60.00 |   7

sale_id | sku | sale_date        | price | qty
------------------------------------------------
      1 | 123 | 2013-01-15 11:00 | 30.00 |   1
      2 | 123 | 2013-01-20 14:00 | 28.00 |   3
      3 | 123 | 2013-05-10 15:00 | 25.00 |   2
      4 | 456 | 2013-06-11 12:00 | 80.00 |   1

假设按购买顺序出售，我如何通过SQL查找销售利润率？例如，sku 123的保证金是

30*1 + 28*3 + 25*2 - 20.15*5 - 17.50*1

其中有2件以17.50购买，还有1件以15.00购买尚未售出。

Answer 1

好问题。我采用的方法是计算总销售额。然后计算累计购买量，并将其与特殊逻辑结合起来以得出正确的结合算术：

select s.sku,
       (MarginPos - SUM(case when s.totalqty < p.cumeqty - p.qty then p.price * p.qty
                             when s.totalqty between p.cumeqty - p.qty and p.qty
                             then s.price * (s.totalqty - (p.cumeqty - p.qty))
                             else 0
                        end)
       ) as Margin
from (select s.sku, SUM(price*qty) as MarginPos, SUM(qty) as totalqty
      from sales s
     ) s left outer join
     (select p.*,
             (select SUM(p.qty) from purchase p2 where p2.sku = p.sku and p2.sale_id <= p.sale_id
             ) as cumeqty
      from purchase s
     )
     on s.sku = p.sku
group by s.sku, MarginPos

注意：我尚未测试此查询，因此它可能存在语法错误。