데이터베이스 질문과 답변
가용재고 - SUM(CASE
WHEN (안전재고 = 0) AND (대상재고 = 0) AND (판매량 = 0) THEN
0
ELSE
CASE
WHEN 안전재고 - 대상재고 + 판매량 > 0 THEN
안전재고 - 대상재고 + 판매량
ELSE
0
END
END) OVER(PARTITION BY ITEMPK1, ITEMPK2, ITEMPK3 ORDER BY ROWNUM) 순차차감
가용재고 - SUM(CASE
WHEN (안전재고 = 0) AND (대상재고 = 0) AND (판매량 = 0) THEN
0
ELSE
CASE
WHEN (안전재고 - 대상재고 + 판매량 > 0) AND
(가용재고 - 안전재고 - 대상재고 + 판매량) >= 0 THEN
안전재고 - 대상재고 + 판매량
ELSE
0
END
END) OVER(PARTITION BY ITEMPK1, ITEMPK2, ITEMPK3 ORDER BY ROWNUM) 실제차감
첨부 이미지는 쿼리의 결과자료입니다.
*지시량: 안전재고 - 대상재고 + 판매량
자동 배분 프로그램의 배분 로직 중 일부입니다.
순차차감 계산식의 문제는 ORDER BY ROWNUM(내부적인 배분 우선순위로 정렬됨)에 따라서
가용재고보다 지시량이 오버되버리면 후순위로 가능한 나머지 배분도 전체취소가 되버리는 단점이 발생했습니다.
그래서 CASE문에 조건을 추가로 줘서 실제차감 컬럼을 만들었지만
단순하게 가용재고 - 지시량이 마이너스일 때의 보정은 되었지만, 여전히 남은 문제가
만약 첨부 이미지의 세 번째 행의 지시량이 4일 경우 실제차감이 마이너스가 되면서
나머지 가용재고 한해서 지시가 가능한 후순위(지시량 1 ~ 3)들도 이전 실제차감이 마이너스였기에 취소가 되어버립니다.
(* 로직 상 실제차감이 0 이상이어야만 최종적으로 배분내려짐)
결과적으로 전체 지시량은 1개(두 번째 행)이면서 가용재고는 3으로 남게 되버립니다.
(가용재고 - 안전재고 - 대상재고 + 판매량) >= 0 이 아닌
(실제차감- 안전재고 - 대상재고 + 판매량) >= 0 이러한 의미를 가진 쿼리로 변경하려면 어떻게 해야하나요?
LAG를 사용해야하나요? 실제차감문 안에다가 실제차감 쿼리를 복사 붙여넣기하여 조건을 추가하려고 해도
윈도우 함수를 여기에 사용할 수 없습니다 라는 에러가 나오더라구요,
이 부분을 쉽게 해결할 수 있는 방법이 떠오르지 않습니다.
감사합니다.
미완성 쿼리와 결과로 질문하지 마시고.
원본 자료와 원하는 결과자료로 질문해 주세요.
그리고, 내용이 너무 어렵습니다. 쉽게 설명해 주세요.
각 항목의 의미와 관계를 설명해 주세요.
추가로. 의심가는 부분.
(가용재고 - 안전재고 - 대상재고 + 판매량) 이거 맞나요?
(가용재고 - (안전재고 - 대상재고 + 판매량)) 이게 맞을 듯 한데요?
WITH W_배분대상 AS
(SELECT 'A매장' 매장, 7 판매량, 11 대상재고, 32 안전재고, 4 가용재고, 4 원하는결과차감, 'N' 원하는배분결과 FROM DUAL
UNION ALL
SELECT 'B매장' 매장, 0 판매량, 1 대상재고, 2 안전재고, 4 가용재고, 3 원하는결과차감, 'Y' 원하는배분결과 FROM DUAL
UNION ALL
SELECT 'C매장' 매장, 2 판매량, 0 대상재고, 2 안전재고, 4 가용재고, 3 원하는결과차감, 'N' 원하는배분결과 FROM DUAL
UNION ALL
SELECT 'D매장' 매장, 0 판매량, 1 대상재고, 2 안전재고, 4 가용재고, 2 원하는결과차감, 'Y' 원하는배분결과 FROM DUAL
UNION ALL
SELECT 'E매장' 매장, 0 판매량, 0 대상재고, 2 안전재고, 4 가용재고, 0 원하는결과차감, 'Y' 원하는배분결과 FROM DUAL
),
W_배분 AS
(SELECT A.*
, CASE WHEN (안전재고 = 0) AND (대상재고 = 0) AND (판매량 = 0) THEN 0 ELSE CASE WHEN 안전재고 - 대상재고 + 판매량 > 0 THEN 안전재고 - 대상재고 + 판매량 ELSE 0 END
END 지시량
, 가용재고 - SUM(CASE WHEN (안전재고 = 0) AND (대상재고 = 0) AND (판매량 = 0) THEN 0 ELSE CASE WHEN (안전재고 - 대상재고 + 판매량 > 0) THEN 안전재고 - 대상재고 + 판매량 ELSE 0 END
END ) OVER (PARTITION BY NULL ORDER BY ROWNUM) 순차차감
, 가용재고 - SUM(CASE WHEN (안전재고 = 0) AND (대상재고 = 0) AND (판매량 = 0) THEN 0 ELSE CASE WHEN (안전재고 - 대상재고 + 판매량 > 0) AND (가용재고 - (안전재고 - 대상재고 + 판매량) >= 0) THEN 안전재고 - 대상재고 + 판매량 ELSE 0 END
END ) OVER (PARTITION BY NULL ORDER BY ROWNUM) 실제차감
FROM W_배분대상 A)
SELECT A.*, DECODE(LAG(실제차감) OVER (PARTITION BY NULL ORDER BY ROWNUM) - 실제차감, 0, 'N', DECODE(가용재고 - 실제차감, 0, 'N', DECODE(SIGN(A.실제차감), -1, 'N', DECODE(SIGN(지시량), 1, 'Y', 'N')))) 배분결과 FROM W_배분 A
말씀하신 수식이 옳습니다.
하려고 하는 것이
원하는결과차감과 원하는배분결과입니다.
감사합니다.
항목들의 의미와 항목들 간의 관계에 대한 설명은 없나요?
댓글의 쿼리식은 5개의 매장, 하나의 아이템이라고 가정하여 올렸습니다.
- 배분: 물류 -> 매장으로 산출된 지시량만큼 아이템 이동. 배분결과가 Y만 이동.
- 안전재고: 배분 시 보장되어야 하는 등록된 매장안전재고량값.
- 가용재고: 아이템의 물류가용재고, 가용재고에 한해서 배분이 가능.
- 지시량: 안전재고 - 대상재고 + 판매량.
- 판매량: 특정 매출기간동안의 매장 판매량, 지시량 산출에 영향. 지시량에 더해 이 만큼 판매되어도 안전재고를 보장하도록 하는 보정값 의미.
- 대상재고: 배분되어질 매장의 현재 재고.
- 순차차감: 배분의 우선순위* 에 따른 남은 가용재고량.
- 실제차감: 가용재고보다 큰 지시량을 지시량 0으로 산출하여 순차차감 보정.
- 배분결과: 지시량이 1 이상, 실제차감이 0 이상이어야 배분 실행.
* 배분의 우선순위: UNION ALL 상단 -> 하단 순서가 배분의 우선순위라고 가정합니다.
원하는결과차감: (가용재고 - (안전재고 - 대상재고 + 판매량) >= 0) 조건 보정이 아닌
(이전 실제차감값 - (안전재고 - 대상재고 + 판매량) >= 0) 으로 실제차감을 보정.
(이슈: C매장 실제차감 -1이 되어 D매장, E매장 배분결과가 'N'이 된 것을 보정.)
(보정사유: D매장, E매장은 배분되고 남은 가용재고에 한해서 배분이 가능한 지시량.)
감사합니다.
WITH W_배분대상 AS
(
SELECT 1 pk1, 1 pk2, 1 pk3, 1 배분우선순위
, 7 판매량, 11 대상재고, 32 안전재고, 4 가용재고 FROM dual
UNION ALL SELECT 1, 1, 1, 2, 0, 1, 2, 4 FROM dual
UNION ALL SELECT 1, 1, 1, 3, 2, 0, 2, 4 FROM dual
UNION ALL SELECT 1, 1, 1, 4, 0, 1, 2, 4 FROM dual
UNION ALL SELECT 1, 1, 1, 5, 0, 0, 2, 4 FROM dual
UNION ALL SELECT 1, 1, 1, 6, 0, 0, 0, 4 FROM dual
)
, t1 AS
(
SELECT pk1, pk2, pk3
, ROW_NUMBER() OVER(PARTITION BY pk1, pk2, pk3 ORDER BY 배분우선순위) rn
, 판매량, 대상재고, 안전재고, 가용재고
, 판매량 - 대상재고 + 안전재고 지시량
FROM W_배분대상
)
, t2( pk1, pk2, pk3, rn
, 판매량, 대상재고, 안전재고, 가용재고
, 지시량, 실제차감, 잔여재고, 배분여부) AS
(
SELECT pk1, pk2, pk3, rn
, 판매량, 대상재고, 안전재고, 가용재고
, 지시량
, CASE WHEN 가용재고 >= 지시량 THEN 지시량 ELSE 0 END 실제차감
, 가용재고
- CASE WHEN 가용재고 >= 지시량 THEN 지시량 ELSE 0 END 잔여재고
, CASE WHEN 가용재고 >= 지시량 THEN 'Y' ELSE 'N' END 배분여부
FROM t1
WHERE rn = 1
UNION ALL
SELECT b.pk1, b.pk2, b.pk3, b.rn
, b.판매량, b.대상재고, b.안전재고, b.가용재고
, b.지시량
, CASE WHEN a.잔여재고 >= b.지시량 THEN b.지시량 ELSE 0 END 실제차감
, a.잔여재고
- CASE WHEN a.잔여재고 >= b.지시량 THEN b.지시량 ELSE 0 END 잔여재고
, CASE WHEN a.잔여재고 >= b.지시량 THEN 'Y' ELSE 'N' END 배분여부
FROM t2 a
, t1 b
WHERE b.pk1 = a.pk1
AND b.pk2 = a.pk2
AND b.pk3 = a.pk3
AND b.rn = a.rn + 1
)
SELECT *
FROM t2
ORDER BY pk1, pk2, pk3, rn
;
) 버튼을 클릭하여 작성 하시면 됩니다.