SQL库存平均成本计算 [英] Inventory Average Cost Calculation in SQL
问题描述
我想使用平均值计算库存成本，但是我有点卡在这里...
I want to compute inventory costs using average value, and I'm somewhat stuck here...
考虑一个简单的交易表tr :( id是自动递增的，负交易量表示卖出交易)
Consider a simple transaction table tr: (ids are autoincrement, negative volume indicates a sell transaction)
order_id | volume | price | type
1 | 1000 | 100 | B
2 | -500 | 110 | S
3 | 1500 | 80 | B
4 | -100 | 150 | S
5 | -600 | 110 | S
6 | 700 | 105 | B
现在，我想知道每次交易后的总数量和总费用.困难在于正确的销售.此时，销售总是以平均成本计价(即，此处的实际价格与销售价格无关)，因此此处的交易订单确实很重要.
Now I want to know the total volume and total costs after each transaction. The difficulty is getting the sells right. Sells are always valued at the average cost at this point (ie the sell price is actually not relevant here), so the transaction order does matter here.
最佳地，结果看起来像这样:
Optimally, the result would look like this:
order_id | volume | price | total_vol | total_costs | unit_costs
1 | 1000 | 100 | 1000 | 100000 | 100
2 | -500 | 110 | 500 | 50000 | 100
3 | 1500 | 80 | 2000 | 170000 | 85
4 | -100 | 150 | 1900 | 161500 | 85
5 | -600 | 110 | 1300 | 110500 | 85
6 | 700 | 105 | 2000 | 184000 | 92
现在，使用sum(volume) over (...)可以轻松实现total_vol，而总成本却很容易.我玩过窗口函数，但是除非我遗漏了完全显而易见(或非常聪明)的东西，否则我不认为仅靠窗口函数就可以做到这一点...
Now, total_vol is easy with a sum(volume) over (...), total costs on the other hand. I've played around with window functions, but unless I'm missing something totally obvious (or very clever), I don't think it can be done with window functions alone...
任何帮助将不胜感激. :)
Any help would be appreciated. :)
更新:
这是我最后使用的代码，是两个答案的组合(数据模型比上面的简化示例要复杂一些，但是您知道了):
This is the code I finally used, a combination of both answers (the data model is a bit more complex than my simplified example above, but you get the idea):
select ser_num
, tr_id
, tr_date
, action_typ
, volume
, price
, total_vol
, trunc(total_costs,0) total_costs
, trunc(unit_costs,4) unit_costs
from itt
model
partition by (ser_num)
dimension by (row_number() over (partition by ser_num order by tr_date, tr_id) rn)
measures (tr_id, tr_date, volume, price, action_typ, 0 total_vol, 0 total_costs, 0 unit_costs)
rules automatic order
( total_vol[ANY] order by rn
= nvl(total_vol[cv()-1],0) +
decode(action_typ[cv()], 'Buy', 1, 'Sell', -1) * volume[cv()]
, total_costs[ANY] order by rn
= case action_typ[cv()]
when 'Buy' then volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0)
when 'Sell' then total_vol[cv()] * nvl(unit_costs[cv()-1],price[cv()])
end
, unit_costs[ANY] order by rn
= decode(total_vol[cv()], 0, unit_costs[cv()-1],
total_costs[cv()] / total_vol[cv()])
)
order by ser_num, tr_date, tr_id
一些观察结果:
- 在使用分区和对上一个单元格(
cv()-1)的引用时，必须以与整个模型子句相同的方式对维度进行分区(这也是为什么使用eration_number可能会很棘手的原因) - 这里只要您在规则上指定正确的执行顺序就不需要迭代(
order by rnAutomatic order自动执行此操作) -
自动命令在这里可能不是必需的，但不会造成伤害.
- When using partitions and references to the previous cell (
cv()-1), the dimension has to be partitioned in the same way as the whole model clause (this is also why using iteration_number can be tricky) - No iteration is needed here as long as you specify the correct execution order on the rules (
order by rnAutomatic orderdoes this automatically) Automatic order is probably not necessary here, but it cant hurt.
推荐答案
您可以使用MODEL子句进行递归计算
You can use the MODEL clause to do this recursive calculation
创建示例表并插入数据
create table costs (order_id int, volume int, price numeric(16,4), type char(1));
insert into costs (order_id, volume, price) values (1,1000,100);
insert into costs (order_id, volume, price) values (2,-500,110);
insert into costs (order_id, volume, price) values (3,1500,80);
insert into costs (order_id, volume, price) values (4,-100,150);
insert into costs (order_id, volume, price) values (5,-600,110);
insert into costs (order_id, volume, price) values (6,700,105);
将rules iterate(1000)更改为rules automatic order的查询( EDITED 到0.01秒！)
The query (EDITED changing rules iterate(1000) to rules automatic order implements the MODEL clause as it is intended to function, i.e. top to bottom sequentially. It also took the query from 0.44s to 0.01s!)
select order_id, volume, price, total_vol, total_costs, unit_costs
from (select order_id, volume, price,
volume total_vol,
0.0 total_costs,
0.0 unit_costs,
row_number() over (order by order_id) rn
from costs order by order_id)
model
dimension by (order_id)
measures (volume, price, total_vol, total_costs, unit_costs)
rules automatic order -- iterate(1000)
( total_vol[any] = volume[cv()] + nvl(total_vol[cv()-1],0.0),
total_costs[any] =
case SIGN(volume[cv()])
when -1 then total_vol[cv()] * nvl(unit_costs[cv()-1],0.0)
else volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0.0)
end,
unit_costs[any] = total_costs[cv()] / total_vol[cv()]
)
order by order_id
输出
ORDER_ID VOLUME PRICE TOTAL_VOL TOTAL_COSTS UNIT_COSTS
1 1000 100 1000 100000 100
2 -500 110 500 50000 100
3 1500 80 2000 170000 85
4 -100 150 1900 161500 85
5 -600 110 1300 110500 85
6 700 105 2000 184000 92
该网站上有关于MODEL子句的很好的教程
This site has a good tutorial on the MODEL clause
上面数据的EXCEL表格如下所示，公式向下扩展了
The EXCEL sheet for the data above would look like this, with the formula extended downwards
A B C D E F
---------------------------------------------------------------------------
1| order_id volume price total_vol total_costs unit_costs
2| 0 0 0
3| 1 1000 100 =C4+E3 =IF(C4<0,G3*E4,F3+C4*D4) =F4/E4
4| 2 -500 110 =C5+E4 =IF(C5<0,G4*E5,F4+C5*D5) =F5/E5
5| 3 1500 80 =C6+E5 =IF(C6<0,G5*E6,F5+C6*D6) =F6/E6
6| 4 -100 150 =C7+E6 =IF(C7<0,G6*E7,F6+C7*D7) =F7/E7
7| 5 -600 110 =C8+E7 =IF(C8<0,G7*E8,F7+C8*D8) =F8/E8
8| 6 700 105 =C9+E8 =IF(C9<0,G8*E9,F8+C9*D9) =F9/E9
这篇关于SQL库存平均成本计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
