데이터베이스 질문과 답변

누적차감 알려주세요 1 4 949

by 느림보 [SQL Query] [2018.09.11 19:15:39]

누적차감에 대한 부분이 궁금합니다.

예를 들어 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

 

 

by 야신 [2018.09.12 07:23:09]
?
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

 

by 느림보 [2018.09.12 13:06:35]
?
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
by 소주쵝오 [2018.09.12 15:33:48]
?
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
           )
     );

 

by 마농 [2018.09.17 13:49:14]
?
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입