SQL中的库存平均成本计算

Question

SQL中的库存平均成本计算

11

我想使用平均值计算库存成本，但我有些困惑...

考虑一个简单的交易表格`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~~ 编辑: 自动顺序可以自动完成此操作)，就不需要进行迭代。
~~自动顺序可能在这里并不必要，但它不会有害。~~

- Martin

1

不是答案，但你使用的任何财务模型都存在缺陷。那种方式不能计算手头库存的单位成本。 - RichardTheKiwi

@Chris：抱歉，应该是105。我已经修复了。 - Martin

@Richard：你能详细说明一下吗？ - Martin

2个回答

6

Richard的模型子句查询存在问题。它在没有UNTIL子句的情况下进行了1000次迭代。在四次迭代后，已经获得了最终结果。接下来的996次迭代会消耗CPU资源，但不会做任何事情。

在这里，您可以看到使用当前数据集进行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.

由于使用了自动顺序，每次迭代都会尝试调整所有6行，因此只需要4次迭代而不是6次。

如果您使用与行数相同的迭代次数，并且每个迭代仅调整一行，则性能将更高。您还可以跳过子查询，然后最终查询变为：

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.

希望这可以帮到你。

问候， Rob。

编辑一些证据支持我的说法：

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

- Rob van Wijk

+1，优秀的工作，将运行时间减半。当代码被优化时，我很喜欢它 :) - RichardTheKiwi

我已经添加了我的解决方案到问题中，我认为这可以在不需要任何迭代的情况下完成。+1 说明更细节的内容。 - Martin

好的，将其应用于您的样本结果集，并将结果与Richard和我的查询进行比较，您会发现您的查询产生了不同的结果：您需要迭代。 - Rob van Wijk

请查看我的更新问题。rules automatic order 可以实现魔法。由于对 unit_costs[cv()-1] 的引用（第一个代码块中的第24行），顺序执行无法正常工作。 - Martin

以上的查询和存储过程，是否兼容Oracle 8i？ - Muhammad

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- RichardTheKiwi · Accepted Answer

你可以使用MODEL子句进行递归计算。创建示例表并插入数据。

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，实现了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

输出

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子句的好教程。

http://www.sqlsnippets.com/en/topic-11663.html

以上数据的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