Я хочу вычислить стоимость запасов, используя среднее значение, и я несколько застрял здесь ...
Рассмотрим простую таблицу транзакций tr
: (идентификаторы автоматически увеличиваются, отрицательный объем указывает на транзакцию продажи)
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
Теперь 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
Automatic order
делает это автоматически)