데이터베이스 질문과 답변
안녕하세요.
초급 개발자 입니다.
쿼리 질문을 드립니다.
아래는 단순한 Sales Order 테이블 입니다.
아래는 Line 별 Item 별 일 Capa 입니다.
계획 시작 날짜는 7월 17일 입니다.
아래와 같은 결과 테이블을 얻고 싶은데 쿼리를 어떻게 작성해야 할지 모르겠네요..
루프문을 돌려서 단일 쿼리로 돌려서 해보려고 했는데
Line 이 50개 이고 Item 이 1000개 되면 5000 번 이상의 루프가 돌아야 하는 문제가 생길 것 같네요..
(거기에 날짜까지 조인 되면 어마어마할 것 같습니다..)
고수님들 쿼리 부탁드립니다. ㅜㅜ
CREATE TABLE CO_SO ( ITEM_CD NVARCHAR(50) , QTY INT , PRIORITY INT ) CREATE TABLE CO_LINE_MST ( LINE_CD NVARCHAR(50) , ITEM_CD NVARCHAR(50) , CAPA_QTY INT ) CREATE TABLE CO_RST ( LINE_CD NVARCHAR(50) , ITEM_CD NVARCHAR(50) , CAPA_QTY INT , CALD_DATE NVARCHAR(8) , SO_QTY INT , USE_CAPA_QTY INT , REMAIN_CAPA_QTY INT ) INSERT INTO CO_SO SELECT 'A', '5000', '1' FROM DUAL UNION ALL SELECT 'B', '2000', '2' FROM DUAL INSERT INTO CO_LINE_MST SELECT 'L1', 'A', '3000' FROM DUAL UNION ALL SELECT 'L1', 'B', '3000' FROM DUAL UNION ALL SELECT 'L2', 'A', '1000' FROM DUAL UNION ALL SELECT 'L2', 'B', '5000' FROM DUAL
WITH co_so AS
(
SELECT 'A' item_cd, 5000 qty, 1 priority FROM dual
UNION ALL SELECT 'B', 2000, 2 FROM dual
)
, co_line_mst AS
(
SELECT 'L1' line_cd, 'A' item_cd, 3000 capa_qty FROM dual
UNION ALL SELECT 'L1', 'B', 3000 FROM dual
UNION ALL SELECT 'L2', 'A', 1000 FROM dual
UNION ALL SELECT 'L2', 'B', 5000 FROM dual
)
, co_rst(line_cd, item_cd, capa_qty, lv, so_qty, so_capa, use_capa_qty, remain_capa_qty, priority) AS
(
SELECT b.line_cd
, a.item_cd
, b.capa_qty
, 1 lv
, a.qty so_qty
, LEAST(a.qty, b.capa_qty) so_capa
, LEAST(a.qty, b.capa_qty) use_capa_qty
, b.capa_qty - LEAST(a.qty, b.capa_qty) remain_capa_qty
, a.priority
FROM co_so a
, co_line_mst b
WHERE a.item_cd = b.item_cd
AND a.priority = 1
AND b.line_cd = 'L1'
UNION ALL
SELECT b.line_cd
, a.item_cd
, b.capa_qty
, c.lv + CASE WHEN c.line_cd = 'L2' AND b.line_cd = 'L1' THEN 1 ELSE 0 END lv
, a.qty so_qty
, DECODE(c.item_cd, a.item_cd, LEAST(a.qty - c.so_capa, b.capa_qty) + c.so_capa
, LEAST(a.qty, b.capa_qty)
) so_capa
, DECODE(c.item_cd, a.item_cd, LEAST(a.qty - c.so_capa, b.capa_qty)
, LEAST(a.qty, b.capa_qty)
) use_capa_qty
, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty)
- DECODE(c.item_cd, a.item_cd, LEAST(a.qty - c.so_capa, b.capa_qty)
, LEAST(a.qty, b.capa_qty)
) remain_capa_qty
, a.priority
FROM co_so a
, co_line_mst b
, co_rst c
WHERE a.item_cd = b.item_cd
AND a.priority = c.priority + DECODE(c.so_capa, c.so_qty, 1, 0)
AND b.line_cd = CASE WHEN c.remain_capa_qty = 0 AND c.line_cd = 'L2' THEN 'L1'
WHEN c.remain_capa_qty = 0 AND c.line_cd != 'L2'
THEN 'L' || (SUBSTR(c.line_cd, 2) + 1)
ELSE c.line_cd END
)
SELECT line_cd
, item_cd
, capa_qty
, TO_CHAR(TO_DATE('20170717', 'yyyymmdd') + lv - 1, 'yyyymmdd') cald_date
, so_qty
, use_capa_qty
, remain_capa_qty
FROM co_rst
;
마농님 진심으로 감사드립니다!
쿼리 분석 후, 댓글 다시 달께요 ~ ^^
오류가 좀 있네요. 다시 정비하겠습니다.
WITH co_so AS
(
SELECT 'A' item_cd, 5000 qty, 1 priority FROM dual
UNION ALL SELECT 'B', 2000, 2 FROM dual
)
, co_line_mst AS
(
SELECT 'L1' line_cd, 'A' item_cd, 3000 capa_qty FROM dual
UNION ALL SELECT 'L1', 'B', 3000 FROM dual
UNION ALL SELECT 'L2', 'A', 1000 FROM dual
UNION ALL SELECT 'L2', 'B', 5000 FROM dual
)
, co_rst(line_cd, item_cd, capa_qty, lv, so_qty, so_capa, use_capa_qty, remain_capa_qty, priority) AS
(
SELECT b.line_cd
, a.item_cd
, b.capa_qty
, 1 lv
, a.qty so_qty
, LEAST(a.qty, b.capa_qty) so_capa
, LEAST(a.qty, b.capa_qty) use_capa_qty
, b.capa_qty - LEAST(a.qty, b.capa_qty) remain_capa_qty
, a.priority
FROM co_so a
, co_line_mst b
WHERE a.item_cd = b.item_cd
AND a.priority = 1
AND b.line_cd = 'L1'
UNION ALL
SELECT b.line_cd
, a.item_cd
, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty) capa_qty
, c.lv + CASE WHEN c.line_cd = 'L2' AND b.line_cd = 'L1' THEN 1 ELSE 0 END lv
, a.qty so_qty
, DECODE(c.item_cd, a.item_cd
, LEAST(a.qty - c.so_capa, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty)) + c.so_capa
, LEAST(a.qty, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty))
) so_capa
, DECODE(c.item_cd, a.item_cd
, LEAST(a.qty - c.so_capa, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty))
, LEAST(a.qty, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty))
) use_capa_qty
, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty)
- DECODE(c.item_cd, a.item_cd
, LEAST(a.qty - c.so_capa, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty))
, LEAST(a.qty, DECODE(c.line_cd, b.line_cd, c.remain_capa_qty, b.capa_qty))
) remain_capa_qty
, a.priority
FROM co_so a
, co_line_mst b
, co_rst c
WHERE a.item_cd = b.item_cd
AND a.priority = c.priority + DECODE(c.so_capa, c.so_qty, 1, 0)
AND b.line_cd = CASE WHEN c.remain_capa_qty = 0 AND c.line_cd = 'L2' THEN 'L1'
WHEN c.remain_capa_qty = 0 AND c.line_cd != 'L2'
THEN 'L' || (SUBSTR(c.line_cd, 2) + 1)
ELSE c.line_cd END
)
SELECT line_cd
, item_cd
, capa_qty
, TO_CHAR(TO_DATE('20170717', 'yyyymmdd') + lv - 1, 'yyyymmdd') cald_date
, so_qty
, use_capa_qty
, remain_capa_qty
, so_capa
FROM co_rst
;
네! 감사드립니다!
마농님 답변 감사드립니다.
쿼리를 마농님 것을 확인을 해보니 제가 짜기엔 아직 무리가 있는 것으로 판단되는군요
역시 엑셀에 있는 것을 쿼리로 변환하는 것은 상당히 어려운 작업인 것 같습니다.
주요 키워드 좀 설명 부탁드려도 될까요..?
WITH 문을 이용한 재귀쿼리입니다.
재귀쿼리의 형태는 UNION ALL 로 나뉜 두개의 쿼리로 구분되구요.
처음 쿼리는 계층의 시작값을 가져오는 쿼리입니다.
(a.priority = 1 AND b.line_cd = 'L1') 조건으로 계층전개를 시작합니다.
UNION 아래쪽 쿼리에서는 유니온 위쪽결과를 다시 사용합니다.
FROM 절의 co_rst c 부분이 이에 해당되며 이것 때문에 재귀쿼리라고 합니다.
즉 루트를 시작으로 UNION 에서 그 다음 조건에 맞는 행을 찾고
이렇게 찾은 행이 부모가 되어 다시 또 자식을 찾는 계층구조입니다.
자식(다음행)을 찾는 조건은..
필요수량(so_capa)를 모두 채우면 다음 아이템(priority+1)을 찾고
남은잔고(remain_capa_qty)가 없으면 다음 라인으로 넘어가는 조건입니다.
최종 라인에 도달한 경우(L2)에는 다시 첫 라인(L1)으로 돌아갑니다.
대략적인 설명 드렸습니다.
마농님 친절한 설명 감사드립니다.
제가 어렵게 생각하는 부분은 재귀 쿼리의 사용뿐은 아니고,
LEAST(A.SO_QTY - C.SO_CAPA_QTY, B.CAPA_QTY) + C.SO_CAPA_QTY
이러한 각각의 케이스에 대한 식들인데요.
저는 단순하게 루프문을 돌려서 그 해당 결과값을 가지고 계속 계산을 하려 하였는데
마농님이 짜주신 쿼리는 이러한 식들에 의거하여 재귀쿼리로 한큐에 나오게 하는 발상(?),
과정 등이 궁금합니다..
답변 기다릴께요!
생각하신 "단순하게 루프 돌려가며 해당값을 찾아내는 과정" 맞습니다.
해당 값을 찾기 위해 이전값과 현재값을 비교해 가면서 추출해야 하겠지요.
재귀쿼리를 통해 루프를 돌리는 거구요.
DECODE 나 CASE, LEAST 등은 IF 문을 간략화한 함수일 뿐입니다.
이전값과 현재값을 어떤 식으로 가져와서 비교하고 활용할 것인지 밑그림을 그리고
해당 로직을 시행착오를 거치면서 계속 보완해 나가면서 완성하는 것입니다.
첫번째 올린 쿼리도 시행착오를 거치는 과정 중에 하나지요.
결국은 식에 대한 사고의 과정이 필요하다고 보입니다.
경험이 많이 있지 않아서 이런 것 이겠죠..
저 쿼리는 단순히 조인만 하던 제가 하기엔
컬럼 별 비교가 꽤 많이 있는 것 같습니다.
의외로 커서나 While 없이 재귀 쿼리로 처리할 수 있는
결과 구조가 많이 있는 것 같군요.
답변 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.