누적차감에 대한 부분이 궁금합니다.
예를 들어 A ITEM 150 현재고 남은수량 50 일경우
현재고 | 수량 | 날짜 | |
A | 150 | 50 | 20180911 |
B | 200 | 90 | 20170101 |
B | 200 | 120 | 20180701 |
이 DATA를 기준으로 아래의 차감수량이 나왔으면 합니다.
아이템별 현재고에서 수량을 날짜기준으로 누적으로 빼주는 형식으로요.. 어떻게 해야할까요?
B의 경우 200-90을 빼고 차감에 110이 나왔는데.. 110에서 수량 120을 빼주어서 -10이 나오는 형태요
현재고 | 수량 | 날짜 | 차감 | |
A | 150 | 50 | 20180911 | 100 |
B | 200 | 90 | 20170101 | 110 |
B | 200 | 120 | 20180701 | -10 |
1 2 3 4 5 6 7 8 | WITH t AS ( SELECT 'A' AS item, 150 AS onhand_qty, 50 AS out_qty, '20180911' AS dt, 1 seq FROM dual UNION ALL SELECT 'B' AS item, 200 AS onhand_qty, 90 AS out_qty, '20170101' AS dt, 2 seq FROM dual UNION ALL SELECT 'B' AS item, 200 AS onhand_qty, 120 AS out_qty, '20180701' AS dt, 3 seq FROM dual UNION ALL SELECT 'A' AS item, 170 AS onhand_qty, 40 AS out_qty, '20180912' AS dt, 4 seq FROM dual ) SELECT item, onhand_qty,out_qty, dt,onhand_qty - SUM (out_qty) OVER(PARTITION BY item ORDER BY dt, seq ) conv_qty FROM t ORDER BY seq |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | WITH t AS ( SELECT 'A' AS item, 1000 AS onhand_qty, 50 AS out_qty, 1 seq FROM dual UNION ALL SELECT 'A' AS item, 1000 AS onhand_qty, 20 AS out_qty, 2 seq FROM dual UNION ALL SELECT 'A' AS item, 1000 AS onhand_qty, 90 AS out_qty, 3 seq FROM dual UNION ALL SELECT 'A' AS item, 1000 AS onhand_qty, 120 AS out_qty, 4 seq FROM dual UNION ALL SELECT 'B' AS item, 500 AS onhand_qty, 300 AS out_qty, 5 seq FROM dual UNION ALL SELECT 'B' AS item, 500 AS onhand_qty, 280 AS out_qty, 6 seq FROM dual UNION ALL SELECT 'B' AS item, 500 AS onhand_qty, 120 AS out_qty, 7 seq FROM dual ) SELECT item, onhand_qty,out_qty, onhand_qty - SUM (out_qty) OVER(PARTITION BY item ORDER BY ITEM, seq ) conv_qty FROM t ORDER BY item |
이렇게 일경우 결과값이 이렇게인데.. 이중 B 의 conv_qty 가 0보다 작을경우
음수가 되기전까지만 차감되게 하고 싶습니다.
ITEM | onhand_qty | out_qty | conv_qty |
A | 1000 | 50 | 950 |
A | 1000 | 20 | 930 |
A | 1000 | 90 | 840 |
A | 1000 | 120 | 720 |
B | 500 | 300 | 200 |
B | 500 | 280 | -80 |
B | 500 | 120 |
-200 |
이렇게요... 혹시 이것도 가능할까요?
ITEM | onhand_qty | out_qty | conv_qty |
A | 1000 | 50 | 950 |
A | 1000 | 20 | 930 |
A | 1000 | 90 | 840 |
A | 1000 | 120 | 720 |
B | 500 | 300 | 200 |
B | 500 | 280 | 200 |
B | 500 | 120 | 0 |
1 2 3 4 5 6 7 8 9 10 11 | select item, onhand_qty, out_qty, seq , decode(s1, 1, val, greatest(r1, s1)) as conv_qty from ( select item, onhand_qty, out_qty, seq , val , decode(sign(val), 1, 1, 0) as s1 , lag(val, 1)over(partition by item order by seq) as r1 from ( select item, onhand_qty,out_qty, seq , onhand_qty - sum (out_qty) over(partition by item order by seq ) val from t ) ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | WITH t AS ( SELECT 'A' item, 1000 onhand_qty, 50 out_qty, '20180901' out_dt FROM dual UNION ALL SELECT 'A' , 1000, 20, '20180902' FROM dual UNION ALL SELECT 'A' , 1000, 90, '20180903' FROM dual UNION ALL SELECT 'A' , 1000, 120, '20180904' FROM dual UNION ALL SELECT 'B' , 500, 300, '20180901' FROM dual UNION ALL SELECT 'B' , 500, 280, '20180902' FROM dual UNION ALL SELECT 'B' , 500, 120, '20180903' FROM dual ) SELECT item, onhand_qty, out_qty, out_dt , GREATEST(onhand_qty - sum_qty, 0) conv_qty FROM ( SELECT item, onhand_qty, out_qty, out_dt , SUM (out_qty) OVER(PARTITION BY item ORDER BY out_dt) sum_qty FROM t ) WHERE onhand_qty > sum_qty - out_qty ; |