데이터베이스 질문과 답변

컬럼 별로 순차적으로 누적 차감하는 방법을 한번 해보려고합니다.. 0 4 3,387

by 새싹 [Oracle 기초] [2023.11.09 22:27:50]

제품 수량을 창고별로 차감하는 로직을 만들어보고싶습니다.

수량 차감에 대해서 창고 D -> 창고 C-> 창고 B-> 창고 A 순서대로 차감하고,

0 밑으로 내려가지 않습니다.

랭크함수로 하는게 가능할까 하고 시도해보고있는데 막혀있습니다.. 도움 주시면 감사드리겠습니당

 

테이블 A

MONTH 제품 수량
202310 A 50
202310 B 150
202310 C 250

 

테이블 B

MONTH 제품 창고A 창고B 창고C 창고D
202310 A 100 100 100 100
202310 B 100 100 100 100
202310 C 100 100 100 100

 

결과 :

MONTH 제품 창고A 창고B 창고C 창고D
202310 A 100 100 100 50
202310 B 100 100 50 0
202310 C 100 50 0 0

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH t AS
(
          SELECT '202310' MONTH, 'A' 제품, '50' 수량 FROM dual
UNION ALL SELECT '202310' MONTH, 'B' 제품, '150' 수량 FROM dual
UNION ALL SELECT '202310' MONTH, 'C' 제품, '250' 수량 FROM dual
)
, S AS
(
          SELECT '202310' MONTH, 'A' 제품, '100' 창고A , '100' 창고B , '100' 창고C , '50' 창고D FROM DUAL
UNION ALL SELECT '202310' MONTH, 'B' 제품, '100' 창고A , '100' 창고B , '50' 창고C , '0' 창고D FROM DUAL
UNION ALL SELECT '202310' MONTH, 'C' 제품, '100' 창고A , '50' 창고B , '0' 창고C , '0' 창고D FROM DUAL
) 
SELECT * FROM S

 

by 마농 [2023.11.10 00:44:05]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH t AS
(
SELECT '202310' month, 'A' 제품, 50 수량 FROM dual
UNION ALL SELECT '202310', 'B', 150 FROM dual
UNION ALL SELECT '202310', 'C', 250 FROM dual
)
, s AS
(
SELECT '202310' month, 'A' 제품, 100 창고A, 100 창고B, 100 창고C, 100 창고D FROM dual
UNION ALL SELECT '202310', 'B', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202310', 'C', 100, 100,100, 100 FROM dual
)
SELECT a.month
     , a.제품
     , a.수량
     , LEAST(b.창고A, GREATEST(0, b.창고D + b.창고C + b.창고B + b.창고A - a.수량)) 창고a
     , LEAST(b.창고B, GREATEST(0, b.창고D + b.창고C + b.창고B           - a.수량)) 창고b
     , LEAST(b.창고C, GREATEST(0, b.창고D + b.창고C                     - a.수량)) 창고c
     , LEAST(b.창고D, GREATEST(0, b.창고D                               - a.수량)) 창고d
  FROM t a
     , s b
 WHERE a.month = b.month
   AND a.제품 = b.제품
;

 

by 새싹 [2023.11.13 14:51:50]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
안녕하세요 마농님 이것저것 해보다가 아래와 비슷한 구조를 고민하고있는데요
 
만약 202310월 과 202311월이 차감되면서,
 
202311월에는 202310월에 차감된것 까지 똑같이 적용하고싶다면
 
SQL 로직만으로도 구성이 가능한가요??
 
프로시저에서 FOR문 쓰는 방식으로 고민해봐야하나 생각이 들어서요
 
 
MONTH   제품  수량  창고A 창고B 창고C 창고D
202310  A   50  100 100 100 50
202310  B   150 100 100 50  0
202310  C   250 100 50  0   0
 
10월 차감 후 11월치 누적 차감
202311  A   50  100 100 100 0
202311  B   150 100 0   0      0
202311  C   250 0   0   0   0
 
 
 
WITH t AS
(
SELECT '202310' month, 'A' 제품, 50 수량 FROM dual
UNION ALL SELECT '202310', 'B', 150 FROM dual
UNION ALL SELECT '202310', 'C', 250 FROM dual
UNION ALL SELECT '202311' month, 'A' 제품, 50 수량 FROM dual
UNION ALL SELECT '202311', 'B', 150 FROM dual
UNION ALL SELECT '202311', 'C', 250 FROM dual
)
, s AS
(
SELECT '202310' month, 'A' 제품, 100 창고A, 100 창고B, 100 창고C, 100 창고D FROM dual
UNION ALL SELECT '202310', 'B', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202310', 'C', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202311' month, 'A' 제품, 100 창고A, 100 창고B, 100 창고C, 100 창고D FROM dual
UNION ALL SELECT '202311', 'B', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202311', 'C', 100, 100,100, 100 FROM dual
)
SELECT a.month
     , a.제품
     , a.수량
     , LEAST(b.창고A, GREATEST(0, b.창고D + b.창고C + b.창고B + b.창고A - a.수량)) 창고a
     , LEAST(b.창고B, GREATEST(0, b.창고D + b.창고C + b.창고B           - a.수량)) 창고b
     , LEAST(b.창고C, GREATEST(0, b.창고D + b.창고C                     - a.수량)) 창고c
     , LEAST(b.창고D, GREATEST(0, b.창고D                               - a.수량)) 창고d
  FROM t a
     , s b
 WHERE a.month = b.month
   AND a.제품 = b.제품
;

 

by 마농 [2023.11.13 15:59:55]

원질문도 그렇고 수정 질문도 그렇고 억지로 만들어 드릴 수 는 있는데.
테이블이 정규화가 안되어 있어서 데이터도 이상하고 쿼리도 이상합니다.
테이블을 정규화 한 상태에서 SQL을 작성해야 이상하지 않습니다.
지금 제시해 주신 데이터는 뭔가 이상합니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
WITH t AS
(
SELECT '202310' month, 'A' 제품, 50 수량 FROM dual
UNION ALL SELECT '202310', 'B', 150 FROM dual
UNION ALL SELECT '202310', 'C', 250 FROM dual
UNION ALL SELECT '202311', 'A',  50 FROM dual
UNION ALL SELECT '202311', 'B', 150 FROM dual
UNION ALL SELECT '202311', 'C', 250 FROM dual
)
, s AS
(
SELECT '202310' month, 'A' 제품, 100 창고A, 100 창고B, 100 창고C, 100 창고D FROM dual
UNION ALL SELECT '202310', 'B', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202310', 'C', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202311', 'A', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202311', 'B', 100, 100,100, 100 FROM dual
UNION ALL SELECT '202311', 'C', 100, 100,100, 100 FROM dual
)
SELECT a.month
     , a.제품
     , a.수량
     , LEAST(b.창고A, GREATEST(0, b.창고D + b.창고C + b.창고B + b.창고A - SUM(a.수량) OVER(PARTITION BY a.제품 ORDER BY a.month))) 창고a
     , LEAST(b.창고B, GREATEST(0, b.창고D + b.창고C + b.창고B           - SUM(a.수량) OVER(PARTITION BY a.제품 ORDER BY a.month))) 창고b
     , LEAST(b.창고C, GREATEST(0, b.창고D + b.창고C                     - SUM(a.수량) OVER(PARTITION BY a.제품 ORDER BY a.month))) 창고c
     , LEAST(b.창고D, GREATEST(0, b.창고D                               - SUM(a.수량) OVER(PARTITION BY a.제품 ORDER BY a.month))) 창고d
  FROM t a
     , s b
 WHERE a.month = b.month
   AND a.제품 = b.제품
   AND a.month IN ('202310', '202311')
 ORDER BY a.month, a.제품
;

 

by 새싹 [2023.11.13 17:19:39]

질문 올리고 혼자 해결하다가 답변주신 방법과 동일하게 해결하였습니다. 무언가 해결하는건 정말 재밌네요..

샘플 테이블 잘 정리해서 질문드리겠습니다 감사합니다

 

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