oracle SQL中的库存平均成本计算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5396827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inventory Average Cost Calculation in SQL
提问by Martin
I want to compute inventory costs using average value, and I'm somewhat stuck here...
我想使用平均值计算库存成本，但我有点卡在这里...
Consider a simple transaction table tr: (ids are autoincrement, negative volume indicates a sell transaction)
考虑一个简单的交易表tr：（ID 是自动递增的，负交易量表示卖出交易）
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
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...
现在，total_vol 很容易，sum(volume) over (...)另一方面，总成本为 。我玩过窗口函数，但除非我遗漏了一些完全明显（或非常聪明）的东西，否则我认为它不能单独使用窗口函数来完成......
Any help would be appreciated. :)
任何帮助，将不胜感激。:)
UPDATE:
更新：
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
Some observations:
一些观察：
- 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.
- 当使用分区和对前一个单元格 (
cv()-1) 的引用时，维度必须以与整个模型子句相同的方式进行分区（这也是使用迭代编号可能会很棘手的原因） - 只要您在规则上指定正确的执行顺序，这里就不需要迭代（编辑：自动执行此操作）
order by rnAutomatic order 这里可能不需要自动排序，但它不会受到伤害。
回答by RichardTheKiwi
You can use the MODEL clause to do this recursive calculation
您可以使用 MODEL 子句进行此递归计算
Create sample table and insert data
创建示例表并插入数据
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);
The query (EDITEDchanging rules iterate(1000)to rules automatic orderimplements 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!)
查询（EDITED更改rules iterate(1000)为rules automatic order实现 MODEL 子句，因为它旨在发挥作用，即按顺序从上到下。它还使查询从 0.44 秒变为 0.01 秒！）
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
Output
输出
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
This site has a good tutorial on the MODEL clause
这个站点有一个关于 MODEL 子句的很好的教程
上面数据的EXCEL表看起来像这样，公式向下延伸
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
回答by Rob van Wijk
There is a problem with Richard's model clause query. It is doing 1000 iterations without an UNTIL clause. After four iterations the end result is achieved already. The next 996 iterations consume CPU power, but do nothing.
Richard 的模型子句查询有问题。它在没有 UNTIL 子句的情况下进行 1000 次迭代。经过四次迭代，最终结果已经实现。接下来的 996 次迭代会消耗 CPU 能力，但什么也不做。
Here you can see that the query is done processing after 4 iterations with the current data set:
在这里您可以看到查询在使用当前数据集进行 4 次迭代后完成处理：
SQL> select order_id
2 , volume
3 , price
4 , total_vol
5 , total_costs
6 , unit_costs
7 from ( select order_id
8 , volume
9 , price
10 , volume total_vol
11 , 0.0 total_costs
12 , 0.0 unit_costs
13 , row_number() over (order by order_id) rn
14 from costs
15 order by order_id
16 )
17 model
18 dimension by (order_id)
19 measures (volume, price, total_vol, total_costs, unit_costs)
20 rules iterate (4)
21 ( total_vol[any] = volume[cv()] + nvl(total_vol[cv()-1],0.0)
22 , total_costs[any]
23 = case SIGN(volume[cv()])
24 when -1 then total_vol[cv()] * nvl(unit_costs[cv()-1],0.0)
25 else volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0.0)
26 end
27 , unit_costs[any] = total_costs[cv()] / total_vol[cv()]
28 )
29 order by order_id
30 /
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
6 rows selected.
It needs 4 iterations and not 6, because automatic order is used, and each iteration tries to adjust all 6 rows.
它需要 4 次迭代而不是 6 次，因为使用了自动排序，并且每次迭代都会尝试调整所有 6 行。
You are far more performant if you use just as many iterations as there are rows and each iteration adjusts just one row. You can also skip the subquery and then the final query becomes:
如果您使用的迭代次数与行数一样多，并且每次迭代仅调整一行，则性能会更高。您也可以跳过子查询，然后最终查询变为：
SQL> select order_id
2 , volume
3 , price
4 , total_vol
5 , total_costs
6 , unit_costs
7 from costs
8 model
9 dimension by (row_number() over (order by order_id) rn)
10 measures (order_id, volume, price, type, 0 total_vol, 0 total_costs, 0 unit_costs)
11 rules iterate (1000) until (order_id[iteration_number+2] is null)
12 ( total_vol[iteration_number+1]
13 = nvl(total_vol[iteration_number],0) + volume[iteration_number+1]
14 , total_costs[iteration_number+1]
15 = case type[iteration_number+1]
16 when 'B' then volume[iteration_number+1] * price[iteration_number+1] + nvl(total_costs[iteration_number],0)
17 when 'S' then total_vol[iteration_number+1] * nvl(unit_costs[iteration_number],0)
18 end
19 , unit_costs[iteration_number+1]
20 = total_costs[iteration_number+1] / total_vol[iteration_number+1]
21 )
22 order by order_id
23 /
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
6 rows selected.
Hope this helps.
希望这可以帮助。
Regards,
Rob.
问候，
罗伯。
EDIT Some proof to backup my claim:
编辑一些证据来支持我的主张：
SQL> create procedure p1 (p_number_of_iterations in number)
2 is
3 begin
4 for x in 1 .. p_number_of_iterations
5 loop
6 for r in
7 ( select order_id
8 , volume
9 , price
10 , total_vol
11 , total_costs
12 , unit_costs
13 from ( select order_id
14 , volume
15 , price
16 , volume total_vol
17 , 0.0 total_costs
18 , 0.0 unit_costs
19 , row_number() over (order by order_id) rn
20 from costs
21 order by order_id
22 )
23 model
24 dimension by (order_id)
25 measures (volume, price, total_vol, total_costs, unit_costs)
26 rules iterate (4)
27 ( total_vol[any] = volume[cv()] + nvl(total_vol[cv()-1],0.0)
28 , total_costs[any]
29 = case SIGN(volume[cv()])
30 when -1 then total_vol[cv()] * nvl(unit_costs[cv()-1],0.0)
31 else volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0.0)
32 end
33 , unit_costs[any] = total_costs[cv()] / total_vol[cv()]
34 )
35 order by order_id
36 )
37 loop
38 null;
39 end loop;
40 end loop;
41 end p1;
42 /
Procedure created.
SQL> create procedure p2 (p_number_of_iterations in number)
2 is
3 begin
4 for x in 1 .. p_number_of_iterations
5 loop
6 for r in
7 ( select order_id
8 , volume
9 , price
10 , total_vol
11 , total_costs
12 , unit_costs
13 from costs
14 model
15 dimension by (row_number() over (order by order_id) rn)
16 measures (order_id, volume, price, type, 0 total_vol, 0 total_costs, 0 unit_costs)
17 rules iterate (1000) until (order_id[iteration_number+2] is null)
18 ( total_vol[iteration_number+1]
19 = nvl(total_vol[iteration_number],0) + volume[iteration_number+1]
20 , total_costs[iteration_number+1]
21 = case type[iteration_number+1]
22 when 'B' then volume[iteration_number+1] * price[iteration_number+1] + nvl(total_costs[iteration_number],0)
23 when 'S' then total_vol[iteration_number+1] * nvl(unit_costs[iteration_number],0)
24 end
25 , unit_costs[iteration_number+1]
26 = total_costs[iteration_number+1] / total_vol[iteration_number+1]
27 )
28 order by order_id
29 )
30 loop
31 null;
32 end loop;
33 end loop;
34 end p2;
35 /
Procedure created.
SQL> set timing on
SQL> exec p1(1000)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.32
SQL> exec p2(1000)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.45
SQL> exec p1(1000)
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.28
SQL> exec p2(1000)
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.43
