데이터베이스 질문과 답변
제품 수량을 창고별로 차감하는 로직을 만들어보고싶습니다.
수량 차감에 대해서 창고 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 |
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]
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]
안녕하세요 마농님 이것저것 해보다가 아래와 비슷한 구조를 고민하고있는데요
만약 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을 작성해야 이상하지 않습니다.
지금 제시해 주신 데이터는 뭔가 이상합니다.
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]
질문 올리고 혼자 해결하다가 답변주신 방법과 동일하게 해결하였습니다. 무언가 해결하는건 정말 재밌네요..
샘플 테이블 잘 정리해서 질문드리겠습니다 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.