데이터베이스 질문과 답변
이것은 부품의 사용성(BOM Solving)에 대한 내용입니다.
하루에 처리하는 량이 많고 속도가 느리다 보니 계속 속도 개선에 대한 노력을 하고 있습니다.
사용성 처리전에 어떻게 하면 처리량을 줄여 속도를 개선할까 하면서 만들고 있습니다.
기본 정보는 Usage table과 Model varaint table 입니다.
Usage table :
No. Usage
1 AAEKZ OR AAED3
2 AAEKZ OR AAED3
3 AAEA7 AND KBE00
4 AAEA7 AND KBE00
5 AAEA7 AND KBE00
6 AAEKZ OR AAEB5 OR AAED2
7 (AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)
8 (AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)
9 (AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)
Model varaint table:
ModelVariant Feature Value
mv1 AAEKZ S
mv1 AAED3 O
mv1 AAEA7 O
mv1 KBE00 S
mv1 KBE01 S
mv1 KBE02 -
mv1 AAEB5 -
mv1 AAED2 S
mv2 AAEKZ S
mv2 AAED3 S
mv2 AAEA7 -
mv2 KBE00 -
mv2 KBE01 S
mv2 KBE02 -
mv2 AAEB5 -
mv2 AAED2 S
mv3 AAEKZ S
mv3 AAED3 S
mv3 AAEA7 S
mv3 KBE00 S
mv3 KBE01 S
mv3 KBE02 S
mv3 AAEB5 -
mv3 AAED2 S
원하는 결과물
No. Usage
1 S OR O
2 S OR S
3 O AND S
4 - AND -
5 S AND S
6 S OR - OR S
7 (S AND S) OR (- AND S) OR (O AND S)
8 (S AND -) OR (- AND -) OR (- AND -)
9 (S AND S) OR (- AND S) OR (S AND S)
봐 주셔서 감사합니다.
WITH usage AS
(
SELECT 1 no, 'AAEKZ OR AAED3' usage FROM dual
UNION ALL SELECT 2, 'AAEKZ OR AAED3' FROM dual
UNION ALL SELECT 3, 'AAEA7 AND KBE00' FROM dual
UNION ALL SELECT 4, 'AAEA7 AND KBE00' FROM dual
UNION ALL SELECT 5, 'AAEA7 AND KBE00' FROM dual
UNION ALL SELECT 6, 'AAEKZ OR AAEB5 OR AAED2' FROM dual
UNION ALL SELECT 7, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)' FROM dual
UNION ALL SELECT 8, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)' FROM dual
UNION ALL SELECT 9, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)' FROM dual
)
, model_varaint AS
(
SELECT 'mv1' modelVariant, 'AAEKZ' feature, 'S' value FROM dual
UNION ALL SELECT 'mv1', 'AAED3', 'O' FROM dual
UNION ALL SELECT 'mv1', 'AAEA7', 'O' FROM dual
UNION ALL SELECT 'mv1', 'KBE00', 'S' FROM dual
UNION ALL SELECT 'mv1', 'KBE01', 'S' FROM dual
UNION ALL SELECT 'mv1', 'KBE02', '-' FROM dual
UNION ALL SELECT 'mv1', 'AAEB5', '-' FROM dual
UNION ALL SELECT 'mv1', 'AAED2', 'S' FROM dual
UNION ALL SELECT 'mv2', 'AAEKZ', 'S' FROM dual
UNION ALL SELECT 'mv2', 'AAED3', 'S' FROM dual
UNION ALL SELECT 'mv2', 'AAEA7', '-' FROM dual
UNION ALL SELECT 'mv2', 'KBE00', '-' FROM dual
UNION ALL SELECT 'mv2', 'KBE01', 'S' FROM dual
UNION ALL SELECT 'mv2', 'KBE02', '-' FROM dual
UNION ALL SELECT 'mv2', 'AAEB5', '-' FROM dual
UNION ALL SELECT 'mv2', 'AAED2', 'S' FROM dual
UNION ALL SELECT 'mv3', 'AAEKZ', 'S' FROM dual
UNION ALL SELECT 'mv3', 'AAED3', 'S' FROM dual
UNION ALL SELECT 'mv3', 'AAEA7', 'S' FROM dual
UNION ALL SELECT 'mv3', 'KBE00', 'S' FROM dual
UNION ALL SELECT 'mv3', 'KBE01', 'S' FROM dual
UNION ALL SELECT 'mv3', 'KBE02', 'S' FROM dual
UNION ALL SELECT 'mv3', 'AAEB5', '-' FROM dual
UNION ALL SELECT 'mv3', 'AAED2', 'S' FROM dual
)
, tmp1 AS
(
SELECT no, usage
, modelvariant, feature, value
, COUNT(*) OVER(PARTITION BY no) cnt
, ROW_NUMBER() OVER(PARTITION BY no ORDER BY feature) rn
FROM (SELECT a.no, a.usage
, b.modelvariant, b.feature, b.value
, DENSE_RANK() OVER(PARTITION BY b.feature ORDER BY a.no) dr
FROM usage a
, model_varaint b
WHERE INSTR(a.usage, b.feature) > 0
)
WHERE modelvariant = 'mv' || (MOD(dr - 1, 3) + 1)
)
, tmp2(no, cnt, rn, usage) AS
(
SELECT no
, cnt
, rn
, REPLACE(usage, feature, value) usage
FROM tmp1
WHERE rn = 1
UNION ALL
SELECT a.no
, a.cnt
, b.rn
, REPLACE(a.usage, b.feature, b.value) usage
FROM tmp2 a
, tmp1 b
WHERE b.rn = a.rn + 1
AND b.no = a.no
)
SELECT no
, usage
FROM tmp2
WHERE rn = cnt
;
정말 감사합니다.
항상 도움에 감사하고 있습니다.
2,3년전 도움을 받았던 부분에 변화가 생겼는데 몇일간 고민해도 해결을 못하고 있어서 다시 요청드립니다.
바뀐 부분은 ModelVariant 가 의미 코드로 변경이 되었고 수량도 20개 이상으로 증가되었습니다.
Model varaint table:
ModelVariant Feature Value
MEST23DRW AAEKZ S
MEST23DRW AAED3 O
MEST23DRW AAEA7 O
MEST23DRW KBE00 S
MEST23DRW KBE01 S
MEST23DRW KBE02 -
MEST23DRW AAEB5 -
MEST23DRW AAED2 S
OKLIDDS21AD AAEKZ S
OKLIDDS21AD AAED3 S
OKLIDDS21AD AAEA7 -
OKLIDDS21AD KBE00 -
OKLIDDS21AD KBE01 S
OKLIDDS21AD KBE02 -
OKLIDDS21AD AAEB5 -
OKLIDDS21AD AAED2 S
DFR55EW AAEKZ S
DFR55EW AAED3 S
DFR55EW AAEA7 S
DFR55EW KBE00 S
DFR55EW KBE01 S
DFR55EW KBE02 S
DFR55EW AAEB5 -
DFR55EW AAED2 S
감사합니다.
WITH usage AS
(
SELECT 1 no, 'AAEKZ OR AAED3' usage FROM dual
UNION ALL SELECT 2, 'AAEKZ OR AAED3' FROM dual
UNION ALL SELECT 3, 'AAEA7 AND KBE00' FROM dual
UNION ALL SELECT 4, 'AAEA7 AND KBE00' FROM dual
UNION ALL SELECT 5, 'AAEA7 AND KBE00' FROM dual
UNION ALL SELECT 6, 'AAEKZ OR AAEB5 OR AAED2' FROM dual
UNION ALL SELECT 7, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)' FROM dual
UNION ALL SELECT 8, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)' FROM dual
UNION ALL SELECT 9, '(AAED2 AND KBE00) OR (AAEB5 AND KBE00) OR (AAEA7 AND KBE00)' FROM dual
)
, model_varaint AS
(
SELECT 'MEST23DRW' modelVariant, 'AAEKZ' feature, 'S' value FROM dual
UNION ALL SELECT 'MEST23DRW' , 'AAED3', 'O' FROM dual
UNION ALL SELECT 'MEST23DRW' , 'AAEA7', 'O' FROM dual
UNION ALL SELECT 'MEST23DRW' , 'KBE00', 'S' FROM dual
UNION ALL SELECT 'MEST23DRW' , 'KBE01', 'S' FROM dual
UNION ALL SELECT 'MEST23DRW' , 'KBE02', '-' FROM dual
UNION ALL SELECT 'MEST23DRW' , 'AAEB5', '-' FROM dual
UNION ALL SELECT 'MEST23DRW' , 'AAED2', 'S' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'AAEKZ', 'S' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'AAED3', 'S' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'AAEA7', '-' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'KBE00', '-' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'KBE01', 'S' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'KBE02', '-' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'AAEB5', '-' FROM dual
UNION ALL SELECT 'OKLIDDS21AD', 'AAED2', 'S' FROM dual
UNION ALL SELECT 'DFR55EW' , 'AAEKZ', 'S' FROM dual
UNION ALL SELECT 'DFR55EW' , 'AAED3', 'S' FROM dual
UNION ALL SELECT 'DFR55EW' , 'AAEA7', 'S' FROM dual
UNION ALL SELECT 'DFR55EW' , 'KBE00', 'S' FROM dual
UNION ALL SELECT 'DFR55EW' , 'KBE01', 'S' FROM dual
UNION ALL SELECT 'DFR55EW' , 'KBE02', 'S' FROM dual
UNION ALL SELECT 'DFR55EW' , 'AAEB5', '-' FROM dual
UNION ALL SELECT 'DFR55EW' , 'AAED2', 'S' FROM dual
)
, mv_code AS -- 추가
(
SELECT 1 mv_seq, 'MEST23DRW' modelVariant FROM dual
UNION ALL SELECT 2, 'OKLIDDS21AD' FROM dual
UNION ALL SELECT 3, 'DFR55EW' FROM dual
)
, tmp1 AS
(
SELECT no, usage
, modelvariant, feature, value
, COUNT(*) OVER(PARTITION BY no) cnt
, ROW_NUMBER() OVER(PARTITION BY no ORDER BY feature) rn
FROM (SELECT a.no, a.usage
, b.modelvariant, b.feature, b.value
, DENSE_RANK() OVER(PARTITION BY b.feature ORDER BY a.no) dr
, c.mv_seq -- 추가
, MAX(c.mv_seq) OVER() max_seq -- 추가
FROM usage a
, model_varaint b
, mv_code c -- 추가
WHERE INSTR(a.usage, b.feature) > 0
AND b.modelvariant = c.modelvariant -- 추가
)
WHERE mv_seq = (MOD(dr - 1, max_seq) + 1) -- 변경
)
, tmp2(no, cnt, rn, usage) AS
(
SELECT no
, cnt
, rn
, REPLACE(usage, feature, value) usage
FROM tmp1
WHERE rn = 1
UNION ALL
SELECT a.no
, a.cnt
, b.rn
, REPLACE(a.usage, b.feature, b.value) usage
FROM tmp2 a
, tmp1 b
WHERE b.rn = a.rn + 1
AND b.no = a.no
)
SELECT no
, usage
FROM tmp2
WHERE rn = cnt
;
정말 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.