제품 수량을 창고별로 차감하는 로직을 만들어보고싶습니다.
수량 차감에 대해서 창고 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 |
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.제품 ; |
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.제품 ; |
원질문도 그렇고 수정 질문도 그렇고 억지로 만들어 드릴 수 는 있는데.
테이블이 정규화가 안되어 있어서 데이터도 이상하고 쿼리도 이상합니다.
테이블을 정규화 한 상태에서 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.제품 ; |