데이터베이스 질문과 답변
며칠 고민하다가 질문 드립니다.
필요량
GROUP ITEM REQ
-----------------------------
GRP_001 A 20
GRP_001 B 3
GRP_002 A 20
GRP_002 C 3
GRP_003 A 30
GRP_003 B 5
GRP_004 A 30
GRP_004 B 5
GRP_004 B 5
보유량
ITEM QTY
-----------------------------
A 100
B 50
C 1
이런 구조의 테이블이 있고,
1. 그룹별 필요량을 보유량에서 누적 차감한다.
2. 그룹별 하나라도 보유량 부족시 차감하지 않는다. <- 이 조건 때문에 너무 어렵습니다.
결과
GROUP ITEM QTY REQ REMAIN CHECK
------------------------------------------------------
GRP_001 A 100 20 80 Y
GRP_001 B 50 3 47 Y
GRP_002 A 80 20 60 Y
GRP_002 C 1 3 -2 N <- 여기서 차감 불가이므로 ITEM A도 결과적으로는 차감하지 않음
GRP_003 A 80 30 50 Y <- 단순 순차 차감이라면 60-30 = 30이 나와야 하나, 위에서 차감 안했으므로 그대로 80-30 = 50이 됨
GRP_003 B 47 5 42 Y
GRP_004 A 50 30 20 Y
GRP_004 B 42 5 37 Y
여기 이미 있는 많은 답변들을 참고 했는데로 어렵네요. 이게 PL/SQL 없이 단순 쿼리로 가능할까요??
고견 여쭙습니다. 꾸벅~
WITH T_REQ ( GRP , ITEM , REQ ) AS (
SELECT 'GRP_001' , 'A' , 20 FROM DUAL UNION ALL
SELECT 'GRP_001' , 'B' , 3 FROM DUAL UNION ALL
SELECT 'GRP_002' , 'A' , 20 FROM DUAL UNION ALL
SELECT 'GRP_002' , 'C' , 3 FROM DUAL UNION ALL
SELECT 'GRP_003' , 'A' , 30 FROM DUAL UNION ALL
SELECT 'GRP_003' , 'B' , 5 FROM DUAL UNION ALL
SELECT 'GRP_004' , 'A' , 30 FROM DUAL UNION ALL
SELECT 'GRP_004' , 'B' , 5 FROM DUAL
) , T_STOCK ( ITEM, QTY ) AS (
SELECT 'A',100 FROM DUAL UNION ALL
SELECT 'B',50 FROM DUAL UNION ALL
SELECT 'C',1 FROM DUAL
)
SELECT GRP
, ITEM
, QTY1 + CASE WHEN V >= 0 THEN NVL(SUM(QTY2) OVER(PARTITION BY ITEM ORDER BY GRP , ITEM),0) ELSE 0 END QTY
, REQ
, REMAIN + CASE WHEN V >= 0 THEN NVL(SUM(QTY2) OVER(PARTITION BY ITEM ORDER BY GRP , ITEM),0) ELSE 0 END AS REMAIN
, CHK
FROM (SELECT GRP
, ITEM
, QTY - SUM_REQ + REQ QTY1
, REQ
, QTY - SUM_REQ REMAIN
, CASE WHEN MIN(QTY-SUM_REQ) OVER(PARTITION BY GRP) < 0 THEN REQ END QTY2
, MIN(QTY-SUM_REQ) OVER(PARTITION BY GRP) V
, CASE WHEN QTY - SUM_REQ < 0 THEN 'N' ELSE 'Y' END CHK
FROM (SELECT A.GRP , A.ITEM , B.QTY , A.REQ
, SUM(A.REQ) OVER(PARTITION BY A.ITEM ORDER BY A.GRP , A.ITEM) SUM_REQ
FROM T_REQ A
, T_STOCK B
WHERE A.ITEM = B.ITEM
)
)
ORDER BY GRP , ITEM
분석함수로는 풀기 어려울 듯 해요.
절차적으로 풀어야 하는 문제입니다.
(GRP_003, A, 30) 을 (GRP_003, A, 90) 으로 바꾸면 결과가 틀려요.
아.. 그러네요.. ㅜㅜ
WITH t1 AS
(
SELECT 'A' item, 100 qty FROM dual
UNION ALL SELECT 'B', 50 FROM dual
UNION ALL SELECT 'C', 1 FROM dual
)
, t2 AS
(
SELECT 'GRP_001' grp, 'A' item, 20 req FROM dual
UNION ALL SELECT 'GRP_001', 'B', 3 FROM dual
UNION ALL SELECT 'GRP_002', 'A', 20 FROM dual
UNION ALL SELECT 'GRP_002', 'C', 3 FROM dual
UNION ALL SELECT 'GRP_003', 'A', 30 FROM dual
UNION ALL SELECT 'GRP_003', 'B', 5 FROM dual
UNION ALL SELECT 'GRP_004', 'A', 30 FROM dual
UNION ALL SELECT 'GRP_004', 'B', 5 FROM dual
UNION ALL SELECT 'GRP_004', 'B', 5 FROM dual
)
, t3 AS
(
SELECT *
FROM t1
PIVOT (MIN(qty) FOR item IN ('A' q1, 'B' q2, 'C' q3))
)
, t4 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY grp) rn
, grp
, NVL(r1, 0) r1
, NVL(r2, 0) r2
, NVL(r3, 0) r3
FROM t2
PIVOT (MIN(req) FOR item IN ('A' r1, 'B' r2, 'C' r3))
)
, t5(rn, grp, q1, q2, q3, r1, r2, r3, m1, m2, m3, chk) AS
(
SELECT b.rn, b.grp
, q1, q2, q3
, r1, r2, r3
, CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN q1 - r1 ELSE q1 END m1
, CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN q2 - r2 ELSE q2 END m2
, CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN q3 - r3 ELSE q3 END m3
, CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN 'Y' ELSE 'N' END chk
FROM t3 a
, t4 b
WHERE b.rn = 1
UNION ALL
SELECT b.rn, b.grp
, a.m1 q1, a.m2 q2, a.m3 q3
, b.r1, b.r2, b.r3
, CASE WHEN a.m1>=b.r1 AND a.m2>=b.r2 AND a.m3>=b.r3 THEN a.m1-b.r1 ELSE a.m1 END m1
, CASE WHEN a.m1>=b.r1 AND a.m2>=b.r2 AND a.m3>=b.r3 THEN a.m2-b.r2 ELSE a.m2 END m2
, CASE WHEN a.m1>=b.r1 AND a.m2>=b.r2 AND a.m3>=b.r3 THEN a.m3-b.r3 ELSE a.m3 END m3
, CASE WHEN a.m1>=b.r1 AND a.m2>=b.r2 AND a.m3>=b.r3 THEN 'Y' ELSE 'N' END chk
FROM t5 a
, t4 b
WHERE b.rn = a.rn + 1
)
SELECT *
FROM t5
UNPIVOT ((qty, req, rmn) FOR item IN ( (q1, r1, m1) AS 'A'
, (q2, r2, m2) AS 'B'
, (q3, r3, m3) AS 'C'
) )
WHERE req > 0
;
마농님 저는 처음에 MODEL 절 생각했었는데, 혹시 MODEL 절로 풀 수도 있을까요?
헉... 두분 다 대단하십니다. 이게 되긴 되는군요;;
이해를 하려면 시간이 좀 걸릴 듯 합니다. ^^;
두분 모두 감사합니다. 복 받으세요~
MODEL 절도 절차적처리를 해주긴 하지만...
이번 문제에는 위에 적어드린 답변처럼 Recursive SQL(재귀쿼리) 이 적합해 보입니다.
한가지 아이템이라면 Model 절로 가능하겠지만, 여러 아이템에 대해서는 Model 절로는 힘들 듯 하네요.
3개 항목을 하나로 합쳐서 해보니 MODEL 절로 억지스럽게 되긴 되네요.
WITH t1 AS
(
SELECT 'A' item, 100 qty FROM dual
UNION ALL SELECT 'B', 50 FROM dual
UNION ALL SELECT 'C', 1 FROM dual
)
, t2 AS
(
SELECT 'GRP_001' grp, 'A' item, 20 req FROM dual
UNION ALL SELECT 'GRP_001', 'B', 3 FROM dual
UNION ALL SELECT 'GRP_002', 'A', 20 FROM dual
UNION ALL SELECT 'GRP_002', 'C', 3 FROM dual
UNION ALL SELECT 'GRP_003', 'A', 30 FROM dual
UNION ALL SELECT 'GRP_003', 'B', 5 FROM dual
UNION ALL SELECT 'GRP_004', 'A', 30 FROM dual
UNION ALL SELECT 'GRP_004', 'B', 5 FROM dual
UNION ALL SELECT 'GRP_004', 'B', 5 FROM dual
)
, t3 AS
(
SELECT *
FROM t1
PIVOT (MIN(qty) FOR item IN ('A' q1, 'B' q2, 'C' q3))
)
, t4 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY grp) rn
, grp
, NVL(r1, 0) r1
, NVL(r2, 0) r2
, NVL(r3, 0) r3
FROM t2
PIVOT (MIN(req) FOR item IN ('A' r1, 'B' r2, 'C' r3))
)
, t5 AS
(
SELECT rn, grp
, q1, q2, q3
, r1, r2, r3
, 0 m1, 0 m2, 0 m3
, CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN q1 - r1 ELSE q1 END ||','||
CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN q2 - r2 ELSE q2 END ||','||
CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN q3 - r3 ELSE q3 END m
, CASE WHEN q1 >= r1 AND q2 >= r2 AND q3 >= r3 THEN 'Y' ELSE 'N' END chk
FROM t4
LEFT OUTER JOIN t3
ON rn = 1
)
, t6 AS
(
SELECT *
FROM t5
MODEL
DIMENSION BY (rn)
MEASURES (grp, q1, q2, q3, r1, r2, r3, m1, m2, m3, m, chk)
RULES
( m[rn > 1] = CASE WHEN REGEXP_SUBSTR(m[cv()-1], '[^,]+', 1, 1) >= r1[cv()]
AND REGEXP_SUBSTR(m[cv()-1], '[^,]+', 1, 2) >= r2[cv()]
AND REGEXP_SUBSTR(m[cv()-1], '[^,]+', 1, 3) >= r3[cv()]
THEN (REGEXP_SUBSTR(m[cv()-1], '[^,]+', 1, 1) - r1[cv()]) ||','||
(REGEXP_SUBSTR(m[cv()-1], '[^,]+', 1, 2) - r2[cv()]) ||','||
(REGEXP_SUBSTR(m[cv()-1], '[^,]+', 1, 3) - r3[cv()])
ELSE m[cv()-1] END
, chk[rn > 1] = CASE WHEN m[cv()-1] = m[cv()] THEN 'N' ELSE 'Y' END
, m1[ANY] = REGEXP_SUBSTR(m[cv()], '[^,]+', 1, 1)
, m2[ANY] = REGEXP_SUBSTR(m[cv()], '[^,]+', 1, 2)
, m3[ANY] = REGEXP_SUBSTR(m[cv()], '[^,]+', 1, 3)
, q1[rn > 1] = m1[cv()-1]
, q2[rn > 1] = m2[cv()-1]
, q3[rn > 1] = m3[cv()-1]
)
)
SELECT *
FROM t6
UNPIVOT ((qty, req, rmn) FOR item IN ( (q1, r1, m1) AS 'A'
, (q2, r2, m2) AS 'B'
, (q3, r3, m3) AS 'C'
) )
WHERE req > 0
;
) 버튼을 클릭하여 작성 하시면 됩니다.