Inventory Average Cost Calculation in SQL

Question

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)

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...

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 rn edit: Automatic order does this automatically)
• Automatic order is probably not necessary here, but it cant hurt.

Answer 1

You can use the MODEL clause to do this recursive calculation

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 (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

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

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

---

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

Answer 2

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.

Here you can see that the query is done processing after 4 iterations with the current data set:

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.

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