本文介绍了根据先进先出的定价来计算利润的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
说我有一些SKU的购买和销售数据:
Say I have purchase and sales data for some SKUs:
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的保证金是
How can I find the sales margin via SQL, assuming they are sold in the order they were purchased? E.g, the margin for sku 123 is
30*1 + 28*3 + 25*2 - 20.15*5 - 17.50*1
其中有2件以17.50的价格购买,有1件以15.00的价格购买未售.
with 2 purchased at 17.50 and 1 purchased at 15.00 left unsold.
推荐答案
好问题.我采用的方法是计算总销售额.然后计算累计购买量,并将其与特殊逻辑结合起来以得出正确的结合算术:
Good question. The approach that I'm taking is to calculate the total sales. Then calculate cumulative purchases, and combine them with special logic to get the right arithmetic for the combination:
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
注意:我尚未测试此查询,因此它可能存在语法错误.
Note: I haven't tested this query so it might have syntax errors.
这篇关于根据先进先出的定价来计算利润的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!