반응형
SQL의 인벤토리 평균 비용 계산
평균 값을 사용하여 재고 비용을 계산하려고 하는데, 여기서 꼼짝할 수가 없습니다.
단순 트랜잭션 테이블을 고려합니다.tr(ids는 자동 증가이며 음의 볼륨은 판매 거래를 나타냅니다)
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 (...)반면에 총 비용.저는 창문 기능을 가지고 놀았지만, 제가 완전히 명백한 (또는 매우 영리한) 것을 놓치고 있지 않는 한, 창문 기능만으로는 할 수 없다고 생각합니다.
어떤 도움이라도 주시면 감사하겠습니다 :)
업데이트:
이것이 제가 마지막으로 사용한 코드입니다. 두 가지 답변의 조합입니다. (데이터 모델은 위의 단순화된 예보다 조금 더 복잡하지만 아이디어는 이해할 수 있습니다.)
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), 차원은 전체 모델 절과 동일한 방식으로 분할되어야 합니다(이 때문에 반복n_n숫자를 사용하는 것이 까다로울 수 있습니다). - 규칙에 올바른 실행 순서를 지정하는 한 여기서는 반복이 필요하지 않습니다.
order by rnAutomatic order자동으로 이 작업을 수행합니다. -
여기서는 자동 주문이 필요 없을 수도 있지만, 아플 수는 없습니다.
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 변경으로 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 절에 대한 좋은 튜토리얼이 있습니다.
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
리차드의 모델 조항 쿼리에 문제가 있습니다.TILL 조항 없이 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개 행을 모두 조정하려고 하므로 6개가 아닌 4개의 반복이 필요합니다.
행 수만큼 반복을 사용하고 각 반복이 행 수를 하나만 조정하는 경우 성능이 훨씬 향상됩니다.하위 쿼리를 건너뛰면 최종 쿼리가 다음과 같이 됩니다.
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
언급URL : https://stackoverflow.com/questions/5396827/inventory-average-cost-calculation-in-sql
반응형
'programing' 카테고리의 다른 글
| 점선 테두리 스트로크 길이 및 스트로크 간 거리 제어 (0) | 2023.09.01 |
|---|---|
| Git를 사용하여 로컬과 원격 간의 변경 사항을 찾는 방법 (0) | 2023.09.01 |
| -fno-stack-protector의 용도는 무엇입니까? (0) | 2023.09.01 |
| 새 DB가 훨씬 느림 (0) | 2023.09.01 |
| 스크롤로 AJAX 기능이 작동하지 않습니다. (0) | 2023.09.01 |