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.