我想使用平均值计算库存成本,但我有些困惑...
考虑一个简单的交易表格`tr`:(ids为自增,负volume表示卖出交易)
考虑一个简单的交易表格`tr`:(ids为自增,负volume表示卖出交易)
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
现在我想知道每次交易后的总成交量和总成本。难点在于正确处理卖出操作,因为卖出总是按照这一点的平均成本价值计算的(也就是说,卖出价格实际上对此不相关),所以交易顺序在此处确实很重要。
最理想的结果应该是这样的:
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 很容易,但是计算总成本就不同了。我已经尝试了窗口函数,但除非我漏掉了某些非常明显(或者非常聪明)的东西,否则我认为仅使用窗口函数无法完成...
任何帮助都将不胜感激。:)
更新:
这是我最终使用的代码,结合了两个答案(数据模型比我上面简化的例子复杂一些,但你明白我的意思):
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
) 时,维度必须与整个模型子句相同的方式进行分区 (这也是为什么使用iteration_number可能会有麻烦的原因) - 只要在规则上指定正确的执行顺序(
编辑: 自动顺序可以自动完成此操作),就不需要进行迭代。order by rn
自动顺序可能在这里并不必要,但它不会有害。