데이터베이스 질문과 답변
--데이터
WITH T1 AS--마스터
(
SELECT 'A' CODE, 'GA' GRP, 450 QTY, 300 AMT FROM DUAL UNION ALL
SELECT 'B' CODE, 'GA' GRP, 150 QTY, 120 AMT FROM DUAL UNION ALL
SELECT 'C' CODE, 'GA' GRP, 50 QTY, 50 AMT FROM DUAL UNION ALL
SELECT 'D' CODE, 'GB' GRP, 150 QTY, 150 AMT FROM DUAL UNION ALL
SELECT 'E' CODE, 'GB' GRP, 20 QTY, 30 AMT FROM DUAL
)
, T2 AS--용량산정 대상
(
SELECT 'B' CODE, 450 QTY FROM DUAL UNION ALL
SELECT 'A' CODE, 670 QTY FROM DUAL UNION ALL
SELECT 'E' CODE, 250 QTY FROM DUAL
)
원하는 결과
code cnt amt
-- T2 'B'의 결과
'A' 1 300
-- T2 'A'의 결과
'A' 1 300
'B' 1 120
'C' 2 100
-- T2 'E'의 결과
'D' 1 150
'E' 5 150
t1은 마스터 테이블이고
각 코드별(code)로 어떤 그룹(grp)인지 해당 코드의 가격(amt)은 얼마인지 정보가 있습니다.
이때
t2 기준으로 용량 산정을 해야하는데요.
용량 산정의 원리는
예를 들어 t2의 첫 번째 데이터
SELECT 'B' CODE, 450 QTY FROM DUAL
이 데이터의 경우 마스터 테이블을 보면 그룹이 'GA' 입니다.
'GA'에 해당하는 코드들(A,B,C)로 목표 용량 450을 만들어야 하는 것이죠.
다행히 'A' 코드가 450이 딱 맞으므로
'A' 1 300
결과가 도출되고요.
두 번째 데이터
SELECT 'A' CODE, 670 QTY FROM DUAL
이 데이터의 경우에는
그룹이 'GA'이고 목표 용량 670을 만들기 위해서는
450 + 450
450 + 150 + 150
450 + 150 + 50 + 50
...
..
.
50 * 14
등 다양한 조합이 있을 수 있습니다.
다만 용량 조합이 아래와 같을 때
450 + 150 + 50 + 50
총 금액이 520원으로 제일 저렴하므로
'A' 1 300
'B' 1 120
'C' 2 100
결과가 나옵니다.
다음으로
SELECT 'E' CODE, 250 QTY FROM DUAL
의 경우
150 + 150
또는
150 + 20 + 20 + 20 + 20 + 20
조합이 있는데 금액은 두 경우 모두 300으로 동일하지만
150 + 150의 경우 용량 50을 버리는 비효율이 있으므로
두번 째 조합
150 + 20 + 20 + 20 + 20 + 20
가 더 좋은 선택이 되어
'D' 1 150
'E' 5 150
라는 결과가 나오게 됩니다.
머리가 안 돌아가요 머리가 아파요ㅠㅠ
철푸덕 OTL
질문도 할줄 아시는지 몰랐습니다 ㅋㅋ
문제는 일단 잘봤습니다 ㅋㅋ
머리가 안 좋아서 조금 응용해야 하는 문제면 잘 못 풉니다ㅋㅋㅋ
WITH t1 AS
(
SELECT 'A' code, 'GA' grp, 450 qty, 300 amt FROM dual
UNION ALL SELECT 'B', 'GA', 150, 120 FROM dual
UNION ALL SELECT 'C', 'GA', 50, 50 FROM dual
UNION ALL SELECT 'D', 'GB', 150, 150 FROM dual
UNION ALL SELECT 'E', 'GB', 20, 30 FROM dual
)
, t2 AS
(
SELECT 'B' code, 450 qty FROM dual
UNION ALL SELECT 'A', 670 FROM dual
UNION ALL SELECT 'E', 250 FROM dual
)
, t3(p_code, p_qty, grp, lv, code, code_path, qty, amt) AS
(
SELECT a.code p_code
, a.qty p_qty
, b.grp
, 1 lv
, c.code
, CAST(c.code AS VARCHAR2(100)) code_path
, c.qty
, c.amt
FROM t2 a
, t1 b
, t1 c
WHERE a.code = b.code
AND b.grp = c.grp
UNION ALL
SELECT p.p_code
, p.p_qty
, p.grp
, lv + 1 lv
, c.code
, p.code_path || c.code code_path
, p.qty + c.qty qty
, p.amt + c.amt amt
FROM t3 p
, t1 c
WHERE p.grp = c.grp
AND p.code <= c.code
AND p.qty < p.p_qty
)
SELECT a.p_code
, a.p_qty
, a.qty qty_tot
, a.amt amt_tot
, b.code
, REGEXP_COUNT(a.code_path, b.code) cnt
, REGEXP_COUNT(a.code_path, b.code) * b.qty qty
, REGEXP_COUNT(a.code_path, b.code) * b.amt amt
FROM (SELECT p_code, p_qty, grp, lv, code, code_path, qty, amt
, ROW_NUMBER() OVER(PARTITION BY p_code ORDER BY amt, qty, lv) rn
FROM t3
WHERE qty >= p_qty
) a
, t1 b
WHERE a.rn = 1
AND INSTR(a.code_path, b.code) > 0
;
재귀쿼리로 깔짝대다가 영 머리가 안 돌아가서 질문드렸는데 생각보다 너무 빨리 푸셨네요-0-;
WITH T1 AS--마스터
(
SELECT 'A' CODE, 'GA' GRP, 450 QTY, 300 AMT FROM DUAL UNION ALL
SELECT 'B' CODE, 'GA' GRP, 150 QTY, 120 AMT FROM DUAL UNION ALL
SELECT 'C' CODE, 'GA' GRP, 50 QTY, 50 AMT FROM DUAL
)
, T2 AS--용량산정 대상
(
SELECT 'B' CODE, 450 QTY FROM DUAL UNION ALL
SELECT 'A' CODE, 670 QTY FROM DUAL
)
SELECT code, 'A' || ':' || SUBSTR(t, 1, 1), 'B' || ':' || SUBSTR(t, 2, 1), 'C' || ':' || SUBSTR(t, 3, 1)
FROM
(
SELECT T2.code, T4.t, ROW_NUMBER() OVER(PARTITION BY T2.code ORDER BY T4.amt) rn
FROM
T2
,
(
SELECT SUBSTR(t, 3, 1) * qty3 + SUBSTR(t, 2, 1) * qty2 + SUBSTR(t, 1, 1) * qty1 qty, SUBSTR(t, 3, 1) * amt3 + SUBSTR(t, 2, 1) * amt2 + SUBSTR(t, 1, 1) * amt1 amt, t3.t, a.*
FROM
(
SELECT TO_CHAR(LEVEL, 'fm000') t
FROM dual
CONNECT BY LEVEL < 1000
) T3
,
(
SELECT MIN(DECODE(rn, 1, code)) code1, MIN(DECODE(rn, 1, qty)) qty1, MIN(DECODE(rn, 1, amt)) amt1, MIN(DECODE(rn, 2, code)) code2, MIN(DECODE(rn, 2, qty)) qty2, MIN(DECODE(rn, 2, amt)) amt2, MIN(DECODE(rn, 3, code)) code3, MIN(DECODE(rn, 3, qty)) qty3, MIN(DECODE(rn, 3, amt)) amt3
FROM
(
SELECT T1.*, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY code) rn
FROM T1
)
GROUP BY grp
) a
) T4
WHERE T4.qty >= T2.qty
) a
WHERE rn = 1
GA 그룹일때만 고려해서 값이 그리크지 않은 경우로 갯수 뿌리는 것만 해봤는데
참.. 허접스럽네요 ㅋㅋ
살짝 안 맞는 부분이 있긴 합니다만 감사합니당^^
마농님 실제 마스터는 금액순 정렬이 아니라서 t3의
p.code <= c.code
이 조건은 빠지는 게 나아보이는데 맞나요?
해당 조건은 금액순 조건이 아니라 경우의 수를 줄여서 부하를 줄이고자 함이 목적입니다.
예를 들면 AAB, BAA, ABA 는 동일한 집합이므로
AAB 하나만 인정하고 BAA, ABA 는 버리기 위한 조건입니다.
아~ 그렇군요~ 왜 저 조건이 있어야 하는지 고민했습니다. 감사합니다~ 즐퇴근하세요~
휴~ 이제서야 완전 이해가 되었네요^^;; 원체 머리도 안 좋지만 오늘은 더더욱 안 돌아가네요ㅋㅋ 감사합니다~
) 버튼을 클릭하여 작성 하시면 됩니다.