Расчет средней стоимости ресурсов в SQL
Я хочу вычислить затраты на инвентаризацию с использованием среднего значения, и я немного застрял здесь...
Рассмотрим простую таблицу транзакций 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 легко с sum(volume) over (...), совокупными затратами, с другой стороны. Я играл с функциями окна, но, если мне не хватает чего-то совершенно очевидного (или очень умного), я не думаю, что это можно сделать только с помощью оконных функций...
Любая помощь будет оценена по достоинству.:)
UPDATE:
Это код, который я наконец использовал, комбинация обоих ответов (модель данных немного сложнее, чем мой упрощенный пример выше, но вы получаете идею):
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 edit: Automatic order делает это автоматически)
-
Автоматический порядок, вероятно, здесь не нужен, но он не может повредить.
Ответы
Ответ 1
Вы можете использовать предложение 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);
Запрос ( 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
Выход
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
Лист 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
Ответ 2
Возникла проблема с запросом предложения модели Ричарда. Он выполняет 1000 итераций без предложения UNTIL. После четырех итераций конечный результат уже достигнут. Следующие 996 итераций потребляют мощность процессора, но ничего не делают.
Здесь вы можете видеть, что запрос выполняется обработкой после 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.
Ему нужны 4 итерации, а не 6, потому что используется автоматический порядок, и каждая итерация пытается настроить все 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.
Надеюсь, что это поможет.
С уважением,
Роб.
ИЗМЕНИТЬ
Некоторые доказательства для резервного копирования моего требования:
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
