req_gty | prdt_date | wh_code | wh_qty |
100 | 20201001 | a | 70 |
100 | 20201001 | c | 20 |
100 | 20201001 | b | 31 |
100 | 20201001 | d | 15 |
100 | 20201003 | a | 1 |
창고에 있는 자재를 요청수량에 최대한 근접하게 출고시키기 위한 쿼리를 만들어야 하는데 너무 어렵네요.
1. 요청수량이 100개이므로 100개 이하로 출고되어야 합니다.
2. 출고순서는 생산일자,요청수량에 가장 근접한 재고수량순이며 그래도 같은 데이터가 있으면 창고코드순입니다. 요청수량에 모자란다고 먼저 생산된 자재가 남아 있는데도 불구하고 나중에 생산된 자재를 출고할수 없습니다.
3. 데이터를 보면 생산일자가 최우선이므로 20201001 데이터가 먼저 출고되어야 하고 그 데이터중 요청수량 100개에 가장 근접한 a창고 70개가 1순위로 출고 되어야 합니다.
4. 이제 남은 요청 수량은 100-70=30개 입니다. 20021001 일자중 b창고 재고수량은 31개이므로 이걸 출고하게 되면 요청수량을 초과하게 됩니다.
5. 따라서 요청수량에 가장 근접한 c창고의 20개를 2순위로 출고하면 요청수량 100개에 가장 근접한 출고를 할수 있습니다.
6. 원하는 결과값은 아래와 같습니다.
req_qty | prdt_date | wh_code | wh_qty |
100 | 20201001 | a | 70 |
100 | 20201001 | c | 20 |
쿼리로 원하는 결과값을 뽑을수 있을까요? 고수님들의 조언 부탁드립니다.
WITH t AS ( SELECT '20201001' prdt_date, 'a' wh_code, 70 wh_qty FROM dual UNION ALL SELECT '20201001', 'c', 20 FROM dual UNION ALL SELECT '20201001', 'b', 31 FROM dual UNION ALL SELECT '20201001', 'd', 15 FROM dual UNION ALL SELECT '20201003', 'a', 1 FROM dual ) , t1 AS ( SELECT req_qty, prdt_date, wh_code, wh_qty , LPAD(ROW_NUMBER() OVER(ORDER BY prdt_date, wh_qty DESC, wh_code), 2, '0') rn , DENSE_RANK() OVER(ORDER BY prdt_date) dr , COUNT(*) OVER(ORDER BY prdt_date) cnt FROM t , (SELECT 105 req_qty FROM dual) -- 요청수량 변경해 가면서 테스트 WHERE wh_qty <= req_qty ) , t2(req_qty, prdt_date, wh_code, wh_qty, dr, rn, cnt, lv, out_qty, rns) AS ( SELECT req_qty, prdt_date, wh_code, wh_qty , dr, rn, cnt , 1 lv , wh_qty out_qty , rn rns FROM t1 WHERE dr = 1 UNION ALL SELECT c.req_qty, c.prdt_date, c.wh_code, c.wh_qty , c.dr, c.rn, c.cnt , p.lv + 1 lv , p.out_qty + c.wh_qty out_qty , p.rns ||'-'|| c.rn rns FROM t2 p , t1 c WHERE c.dr = p.dr + DECODE(p.lv, p.cnt, 1, 0) AND c.rn > p.rn AND c.wh_qty + p.out_qty <= p.req_qty ) SELECT a.req_qty, a.out_qty , b.prdt_date, b.wh_code, b.wh_qty FROM (SELECT req_qty, out_qty, rns , ROW_NUMBER() OVER(ORDER BY out_qty DESC, rns) rn1 FROM t2 ) a , t1 b WHERE a.rn1 = 1 AND INSTR(a.rns, b.rn) > 0 ;
제가 답변이 늦었군요.
제글 읽어 주셔서 너무 감사드립니다.
1. 재고수량이 많는 순서대로 출력?
요청수량이 105개 라면 70 + 31 이렇게 2건이 출고되어야 합니다.
요청수량에 가장 근사치인 101개가 출고 되어야 합니다.
2. 요청수량 정보는?
별도로 입력되는 정보는 아니구요 다른 테이블에 있는 정보입니다만 with절을 쉽게 표현하기 위해서 샘플을 그렇게 만들었습니다.
좀 더 구체적으로 데이터를 만들면 아래와 같습니다.
mat_code | req_gty | prdt_date | wh_code | wh_qty |
mat1 | 100 | 20201001 | a | 70 |
mat1 | 100 | 20201001 | c | 20 |
mat1 | 100 | 20201001 | b | 31 |
mat1 | 100 | 20201001 | d | 15 |
mat1 | 100 | 20201003 | a | 1 |
mat2 | 200 | 20201020 | a | 400 |
mat2 | 200 | 20201020 | d | 1000 |
mat2 | 200 | 20201029 | c | 2000 |
제품코드별로 요청수량이 있습니다.
사실은 위 글에서 조건이 하나 더 추가 되어야 합니다만 너무 복잡해질것 같아서 설명은 안드렸는데요.
예외사항이 있는데 mat2의 경우처럼
생산일이 가장 빠른 데이터를 찾아보니 요청수량보다 재고수량이 더 큰 경우 밖에 없다면 그중에서도 가장 근접한 데이터 1건을 보여줘야 합니다.
요청수량을 over했지만 표현은 해준다는 의미로 색을 빨강색으로 표현해 달라는 요구사항입니다.
이 경우 추가 필드(qty_over_yn)를 구성해서 'Y'값으로 구분하면 될것 같습니다.
최종 결과값은 아래와 같습니다. 부탁드립니다 ^^
mat_code | req_qty | prdt_date | wh_code | wh_qty | qty_over_yn |
mat1 | 100 | 20201001 | a | 70 | N |
mat1 | 100 | 20201001 | c | 20 | N |
mat2 | 200 | 20201020 | a | 400 | Y |
아... 기대했던 답변이 아니네요. 요청수량 105개 일때
- 70 + 20 + 15 = 105개를 기대했는데.
- 70 + 31 = 101개 네요.
그렇다면 만약 60개를 요청한다면?
- 재고량이 더 적은 것을 먼저 출고하지 못한다는 동일기준을 적용한다면?
- 70 이 버티고 있어서 60은 출고가 불가능 하겠네요.
- 70 때문에 출고 못하는 것보다는 31 + 20 = 51 개를 출고하는게 타당하지 않나요?
- 위에 105개도 마찬가지로 101개 출고보다는 105개 출고가 타당해 보이는데요.
- 답변 쿼리도 이런 취지로 답변 드렸습니다.
그리고 모순이 있는게
- mat2 의 경우에도 200 을 요청했는데 가장큰 2000 이 아닌 가장 작은 400 을 보여준다는 것은
- 수량이 적은 것을 먼저 출고할 수 있다는 것으로 볼 수 있을 듯 하네요.
- 만약 mat2 의 요청수량이 500 이라면 결과가 어떻게 나와야 할까요?
- 가장큰 2000 이 우선이므로 출고 못해야 할까요? 400 을 출고해야 할까요?
- 400 을 출고해야 한다면? mat1 의 105개도 101개가 아닌 105개를 출고하는게 타당해 보입니다.
죄송합니다. 제가 설명이 부족했습니다.
다시 정리해서 말씀드리겠습니다.
아래에 샘플 데이터가 있습니다.
mat_code | req_gty | prdt_date | wh_code | wh_qty |
mat1 | 100 | 20201001 | a | 70 |
mat1 | 100 | 20201001 | c | 20 |
mat1 | 100 | 20201001 | b | 31 |
mat1 | 100 | 20201001 | d | 15 |
mat1 | 100 | 20201003 | a | 1 |
mat2 | 200 | 20201020 | a | 300 |
mat2 | 200 | 20201020 | b | 99 |
mat2 | 200 | 20201020 | c | 1000 |
mat2 | 200 | 20201029 | d | 2000 |
mat3 | 200 | 20201020 | a | 300 |
mat3 | 200 | 20201020 | b | 101 |
mat3 | 200 | 20201020 | c | 100 |
요청사항은 아래와 같습니다.
창고에 있는 자재를 요청수량에 최대한 근접하게 출고시키기 위한 쿼리가 필요합니다.
출고순서는 생산일자,요청수량에 가장 근접한 재고수량순이며 그래도 같은 데이터가 있으면 창고코드순으로 합니다.
요청수량 <= 재고수량 이 될때까지 보여주는게 기준입니다.
- mat1
1.요청수량이 100개일때 100개의 (+)(-)건을 모두 찾아 가장 근사치 값을 우선 적용합니다. 샘플데이터를 보면 a창고에 70개짜리가 있으므로 우선 적용합니다.
2. 남은 100-70=30개를 가지고 역시 가장 (+)(-)근사치 값을 찾습니다.
샘플데이터의 경우 b창고의 31개에 해당합니다.
- mat2
1. a창고 300개 와 b창고 99개 중
요청수량 200개에 가장 (+)(-) 근사치인 a창고 300개를 우선 적용합니다.
요청수량인 200개를 넘었으므로 더 이상 보여주지 않습니다.
- mat3
1. a창고 300개 와 b창고 101개 중
요청수량 200개에 가장 (+)(-)근사치인 b창고 101개를 우선 적용합니다.
요청수량이 200-101 = 99개 남았으므로 a창고의 300개보다는 c창고의 100개가 (+)(-)근사치에 가까우므로 c창고의 100개를 두번째로 적용합니다.
c창고까지 적용하고 나니 요청수량을 넘었으므로 더 이상 보여주지 않습니다.
이 로직을 토대로 나온 결과값입니다.
mat_code |
req_gty | prdt_date | wh_code | wh_qty | wh_qty_sum | qty_over_yn |
mat1 | 100 | 20201001 | a | 70 | 70 | N |
mat1 | 100 | 20201001 | b | 31 | 101 | Y |
mat2 | 200 | 20201020 | a | 300 | 300 | Y |
mat3 | 200 | 20201020 | b | 101 | 101 | N |
mat3 | 200 | 20201020 | c | 100 | 201 | Y |
바쁘시겠지만 답변 부탁드립니다 ^^
왠지, 요구사항이 계속 바뀔 것 같은 불안감이....
WITH t1 AS ( SELECT 'mat1' mat_code, 100 req_qty FROM dual UNION ALL SELECT 'mat2', 200 FROM dual UNION ALL SELECT 'mat3', 200 FROM dual ) , t2 AS ( SELECT 'mat1' mat_code, '20201001' prdt_date, 'a' wh_code, 70 wh_qty FROM dual UNION ALL SELECT 'mat1', '20201001', 'c', 20 FROM dual UNION ALL SELECT 'mat1', '20201001', 'b', 31 FROM dual UNION ALL SELECT 'mat1', '20201001', 'd', 15 FROM dual UNION ALL SELECT 'mat1', '20201003', 'a', 1 FROM dual UNION ALL SELECT 'mat2', '20201020', 'a', 300 FROM dual UNION ALL SELECT 'mat2', '20201020', 'b', 99 FROM dual UNION ALL SELECT 'mat2', '20201020', 'c', 1000 FROM dual UNION ALL SELECT 'mat2', '20201029', 'd', 2000 FROM dual UNION ALL SELECT 'mat3', '20201020', 'a', 300 FROM dual UNION ALL SELECT 'mat3', '20201020', 'b', 101 FROM dual UNION ALL SELECT 'mat3', '20201020', 'c', 100 FROM dual ) , t3 AS ( SELECT a.mat_code, a.req_qty , b.prdt_date, b.wh_code, b.wh_qty , LPAD(ROW_NUMBER() OVER(PARTITION BY a.mat_code ORDER BY b.prdt_date, wh_code), 2, '0') rn , DENSE_RANK() OVER(PARTITION BY a.mat_code ORDER BY b.prdt_date) dr , COUNT(*) OVER(PARTITION BY a.mat_code ORDER BY b.prdt_date) cnt FROM t1 a , t2 b WHERE a.mat_code = b.mat_code ) , t4(mat_code, req_qty, prdt_date, wh_code, wh_qty, qty_over_yn, rn, dr, cnt, lv, out_qty, rns) AS ( SELECT a.mat_code, a.req_qty , a.prdt_date, a.wh_code, a.wh_qty , CASE WHEN 0 + a.wh_qty <= a.req_qty THEN 'N' ELSE 'Y' END qty_over_yn , a.rn, a.dr, a.cnt , 1 lv , 0 + a.wh_qty out_qty , a.rn rns FROM t3 a LEFT OUTER JOIN t3 b ON a.dr = b.dr AND a.mat_code = b.mat_code AND a.rn != b.rn AND ( (ABS(a.req_qty - a.wh_qty) = ABS(b.req_qty - b.wh_qty) AND a.wh_code > b.wh_code) OR (ABS(a.req_qty - a.wh_qty) > ABS(b.req_qty - b.wh_qty)) ) WHERE a.dr = 1 AND b.dr IS NULL UNION ALL SELECT p.mat_code, p.req_qty , a.prdt_date, a.wh_code, a.wh_qty , CASE WHEN p.out_qty + a.wh_qty <= p.req_qty THEN 'N' ELSE 'Y' END qty_over_yn , a.rn, a.dr, a.cnt , p.lv + 1 lv , p.out_qty + a.wh_qty out_qty , p.rns ||'-'|| a.rn rns FROM t4 p INNER JOIN t3 a ON a.dr = p.dr + DECODE(p.lv, p.cnt, 1, 0) AND a.mat_code = p.mat_code AND INSTR(p.rns, a.rn) = 0 LEFT OUTER JOIN t3 b ON a.dr = b.dr AND a.mat_code = b.mat_code AND a.rn != b.rn AND INSTR(p.rns, b.rn) = 0 AND ( (ABS(p.req_qty - p.out_qty - a.wh_qty) = ABS(p.req_qty - p.out_qty - b.wh_qty) AND a.wh_code > b.wh_code) OR (ABS(p.req_qty - p.out_qty - a.wh_qty) > ABS(p.req_qty - p.out_qty - b.wh_qty)) ) WHERE p.out_qty < p.req_qty AND b.dr IS NULL ) SELECT * FROM t4 ORDER BY mat_code, lv ;