일반적인 방법의 ‘connect by start with’ 에서 보기 어려운
중복가지가 발생하는 경우의 누적환산량을 구하는 방법입니다.
/* 1. BOM테이블 */
CREATE TABLE BOM (
PartNo VARCHAR2(2), /* PartNo */
PartNoM VARCHAR2(10), /* 부모PARTNO */
Qnty NUMBER(10) NOT NULL, /* 단위당소요량 */
CONSTRAINT pk_g_BOM PRIMARY KEY (PartNo, PartNoM));
/* 2. DATA */
INSERT INTO BOM VALUES (‘X’,’*’,1);
INSERT INTO BOM VALUES (‘A’,’X’,1);
INSERT INTO BOM VALUES (‘B’,’X’,2);
INSERT INTO BOM VALUES (‘C’,’B’,3);
INSERT INTO BOM VALUES (‘E’,’C’,3);
INSERT INTO BOM VALUES (‘F’,’C’,4);
INSERT INTO BOM VALUES (‘I’,’C’,5);
INSERT INTO BOM VALUES (‘D’,’B’,4);
INSERT INTO BOM VALUES (‘H’,’D’,2);
INSERT INTO BOM VALUES (‘C’,’J’,6);
INSERT INTO BOM VALUES (‘J’,’D’,4);
INSERT INTO BOM VALUES (‘K’,’D’,3);
/* 3. 설명*/
이렇게되면
BOM 이라는 테이블에 아래와와같은 자료가 들어 있습니다.
SELECT * FROM BOM;
PA PARTNOM QNTY
— ———- ———-
X * 1
A X 1
B X 2
C B 3
E C 3
F C 4
I C 5
D B 4
H D 2
C J 6
J D 4
K D 3
12 rows selected.
/* 4.원하는 결과 */
FUNCTION을 쓰지않은 onE SQL 로 다음과 같은 결과를 얻어내고 싶답니다.
—- ————- — —– ———-
1 1 A 1 1
2 1 B 2 2
3 ..2 C 3 6
4 ….3 E 3 18
5 ….3 F 4 24
6 ….3 I 5 30
7 ..2 D 4 8
8 ….3 H 2 16
9 ….3 J 4 32
10 ……4 C 6 192
11 ……..5 E 3 576
12 ……..5 F 4 768
13 ……..5 I 5 960
14 ….3 K 3 24
14 rows selected.
/* 5.결과설명 */
—- ————- — —– ———-
1 1 A 1 1 최상위 이므로 자신의 수량
2 1 B 2 2 최상위 이므로 자신의 수량
3 ..2 C 3 6 자신의수량*자신의부모B의수량 = 3*2 = 6
4 ….3 E 3 18 자신의수량*자신의부모C의수량*C의부모B의수량 = 3*3*2 = 18
5 ….3 F 4 24 자신의수량*자신의부모C의수량*C의부모B의수량 = 4*3*2 = 24
6 ….3 I 5 30 자신의수량*자신의부모C의수량*C의부모B의수량 = 5*3*2 = 30
7 ..2 D 4 8 자신의수량*자신의부모B의수량 = 4*2 = 8
8 ….3 H 2 16 자신의수량*자신의부모D의수량*D의부모B의수량 = 2*4*2 = 16
9 ….3 J 4 32 자신의수량*자신의부모D의수량*D의부모B의수량 = 4*4*2 = 32
10 ……4 C 6 192 자신의수량*자신의부모J의수량*J의부모D의수량*D의부모B의수량 = 6*4*4*2 = 192
11 ……..5 E 3 576 자신의수량*자신의부모C의수량*C의부모J의수량*J의부모D의수량*D의부모B의수량 = 3*6*4*4*2 = 576
12 ……..5 F 4 768 자신의수량*자신의부모C의수량*C의부모J의수량*J의부모D의수량*D의부모B의수량 = 4*6*4*4*2 = 768
13 ……..5 I 5 960 자신의수량*자신의부모C의수량*C의부모J의수량*J의부모…3 K 3 24 자신의수량*자신의부모D의수량*D의부모B의수량 = 3*4*2 = 24
14 rows selected.
/* 6.가정*/
실제 테이블에 존재하는 record는 12건인데 순전개를 통하여 전개를 해보면 다음과 같이 14건의 자료가 나옵니다.
SELECT LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
(order siblings by 컬럼명)
LEVELNO LVNO PA QNTY
—————- —— — —–
1 1 A 1
1 1 B 2
..2 2 C 3
….3 3 E 3
….3 3 F 4
….3 3 I 5
..2 2 D 4
….3 3 H 2
….3 3 J 4
……4 4 C 6
……..5 5 E 3
……..5 5 F 4
……..5 5 I 5
….3 3 K 3
14 rows selected.
이유는 자료를 자세히 살펴보면 아시겠지만
자동차 부품의 중간부품에 쓰이는 부품이 또다른 중간부품에서도 쓰이듯이 C 로 시작되는 가지가 두군데에서 쓰이고 있습니다.
이럴경우는 C로 시작하여 역전개를 하면 중복가지가 발생합니다
SELECT *
FROM BOM
START WITH PARTNO=’C’
CONNECT BY PRIOR PARTNOM=PARTNO
PA PARTNOM QNTY
— ———- ———-
C B 3
B X 2
X * 1
C J 6
J D 4
D B 4
B X 2
X * 1
8 rows selected.
중복가지가 발생하지 않는다면 간단하게 역전개 결과를 이용해 나오는
결과값의 곱 만으로 환산수량을 구할 수 있습니다.
예를들어 최초의 자료중
INSERT INTO BOM VALUES (‘C’,’J’,6);
가 없다고 가정해 봅시다.
언급한 한 레코드를 제외하고 QUERY 를 던지면 다음과 같은 결과가 나옵니다.
SELECT * FROM BOM;
PA PARTNOM QNTY
— ———- ———-
X * 1
A X 1
B X 2
C B 3
E C 3
F C 4
I C 5
D B 4
H D 2
J D 4
K D 3
11 rows selected.
이경우 순전개를 하면 다음과 같이 됩니다.
NO LEVELNO PA QNTY
—- ——– — ———-
1 1 A 1
2 1 B 2
3 ..2 C 3
4 ….3 E 3
5 ….3 F 4
6 ….3 I 5
7 ..2 D 4
8 ….3 H 2
9 ….3 J 4
10 ….3 K 3
10 rows selected.
위와 같은 경우의 환산수량을 구하는 방법은 다음과 같이 간단합니다.
SELECT A.LEVELNO,
A.PARTNO,
A.QNTY,
(SELECT EXP(SUM(LN(B.QNTY)))
FROM BOM B
START WITH B.PARTNO = A.PARTNO
CONNECT BY PRIOR PARTNOM=PARTNO
) QTY
FROM (SELECT LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM) A ;
결과
LEVELNO PA QNTY QTY
——– — —– —–
1 A 1 1
1 B 2 2
..2 C 3 6
….3 E 3 18
….3 F 4 24
….3 I 5 30
..2 D 4 8
….3 H 2 16
….3 J 4 32
….3 K 3 24
10 rows selected
즉 특정한 부품 하나에 대해 역전개를 해 나가더라도 중복 가지가 발생할 염려가 없으므로
단순한 QUERY 만으로도 각 부품의 환산 산출량을 구하는데 아무런 지장이 없습니다.
하지만
INSERT INTO BOM VALUES (‘C’,’J’,6);
를 통하여 중복가지가 발생한다면
위와 같이 간단한 문장으로 해결할 길이 없습니다.
이럴경우는 접근방식을 근본적으로 다시 생각해봐야합니다.
/* 7.문제풀이*/
현재 하고자하는방식은 어떤방식을 써서라도 함수를 사용하지않고
하나의 SQL 에서 중복가지 문제를 해결하고 누적환산 산출량을 구하는 겁니다.
단계1.
중점적으로 생각해 볼것은 어떤식으로 중복가지를 피해서 자신만의 상위 부품들을 찾아낼것인가 입니다.
특정부품을 기준으로 생각해볼때 자신의 상위부품은 어떤 공통적인 특징이 있습니다.
첫째 레벨이 자신보다 높다.
즉 특정부품을 기준으로 볼때 레벨의 숫자가 자신보다 낮은경우만이 자신의 부모레벨이 될 후보입니다.
둘째 전개가 제대로 이루어졌다면 자신보다 항상 상단에서 전개가 이루어진다 입니다.
이렇게 두가지 조건으로는 완벽하지 않지만 일차적인 필터링을 할 수 있습니다.
이렇게 일차적인 필터링을 위해 필요한 쿼리에는 전개 결과와 함께 자신의 ROWNUM 이 포함되어야 합니다.
SQL과 결과는 다음과 같습니다.
SQL
SELECT NO,PARTNO,
LEN
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN
FROM (
SELECT LEVEL LEV,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
결과
NO PA LEN
———- — ———-
1 A 1
2 B 1
3 C 2
4 E 3
5 F 3
6 I 3
7 D 2
8 H 3
9 J 3
10 C 4
11 E 5
12 F 5
13 I 5
14 K 3
14 rows selected.
단계2.
두번째 단계는 위의 결과를 이용해서 자신보다 LEVEL(즉 LEN) 이 작으면서
동시에 자신보다 먼저 전개가 이루어진(즉 NO 가 자신보다 작은) 자료만을 뽑아내는 일입니다.
그러기 위해선 위의 결과를 이용해 부등호조인을 동일한 결과에 걸어줘야 할겁니다.
SQL과 결과는 다음과 같습니다.
SQL
SELECT A.NO,A.PARTNO,A.LEN,
B.NO,B.PARTNO,B.LEN
FROM (SELECT NO,PARTNO,
LEN
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN
FROM (
SELECT LEVEL LEV,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) A,
(SELECT NO,PARTNO,
LEN,QNTY
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN,QNTY
FROM (
SELECT LEVEL LEV,PARTNO,QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) B
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
결과
NO PA LEN NO PA LEN
———- — ———- ———- — ———-
1 A 1
2 B 1
3 C 2 1 A 1
3 C 2 2 B 1
4 E 3 1 A 1
4 E 3 2 B 1
4 E 3 3 C 2
5 F 3 1 A 1
5 F 3 2 B 1
5 F 3 3 C 2
6 I 3 1 A 1
6 I 3 2 B 1
6 I 3 3 C 2
7 D 2 1 A 1
7 D 2 2 B 1
8 H 3 1 A 1
8 H 3 2 B 1
8 H 3 3 C 2
8 H 3 7 D 2
9 J 3 1 A 1
9 J 3 2 B 1
9 J 3 3 C 2
9 J 3 7 D 2
10 C 4 1 A 1
10 C 4 2 B 1
10 C 4 3 C 2
10 C 4 4 E 3
10 C 4 5 F 3
10 C 4 6 I 3
10 C 4 7 D 2
10 C 4 8 H 3
10 C 4 9 J 3
11 E 5 1 A 1
11 E 5 2 B 1
11 E 5 3 C 2
11 E 5 4 E 3
11 E 5 5 F 3
11 E 5 6 I 3
11 E 5 7 D 2
11 E 5 8 H 3
11 E 5 9 J 3
11 E 5 10 C 4
12 F 5 1 A 1
12 F 5 2 B 1
12 F 5 3 C 2
12 F 5 4 E 3
12 F 5 5 F 3
12 F 5 6 I 3
12 F 5 7 D 2
12 F 5 8 H 3
12 F 5 9 J 3
12 F 5 10 C 4
13 I 5 1 A 1
13 I 5 2 B 1
13 I 5 3 C 2
13 I 5 4 E 3
13 I 5 5 F 3
13 I 5 6 I 3
13 I 5 7 D 2
13 I 5 8 H 3
13 I 5 9 J 3
13 I 5 10 C 4
14 K 3 1 A 1
14 K 3 2 B 1
14 K 3 3 C 2
14 K 3 7 D 2
66 rows selected.
벌써 SQL 이 길어진듯한 느낌이지만 살펴보면 간단합니다.
단계1에서 나온 SQL 을 두번써서 서로 NON-EQUI 조인을
위에서 언급한대로 걸어준겁니다.
조건이
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
이렇게 되어 있는게 전부입니다.
OUTER 기호가 붙은 이유는 설명안해도 아시겠지만
A테이블을 기준으로 잡고 있기 때문입니다.
조인의 특성상 OUTER 가 아닌경우는 조인이 걸리는 값이 양쪽 데이타셋에 모두 존재해야하기때문에
최상위인 A,B 두개의 PART 가 빠집니다.
이를 막기위해 OUTER 조인이 이용되었습니다.
중요한건 여기까지의 결과를 분석하는겁니다.
결과가 많아진 이유는 14개의 각 순 전개된 PART 마다
자신의 직계 상위가 될 수 있는 후보들과 조인이 이루어졌기 때문입니다.
이 결과가 직계 상위 후보들이 맞는다면 단순히 A.NO 별로 GROUP BY 만 해주면 됩니다.
하지만 위의 결과를 보면 알 수 있듯이
여기서 한단계 더 필터링을 해줘야 합니다.
어떻게 우리가 원하듯 중복자료를 제외한 직계 자료만을 가져올 수 있나?
문제가 되는 C 를 놓고 봅시다.
위의 결과중 A.NO 가 10 인 9개 ROW를 자세히 살펴봅시다.
NO PA LEN NO PA LEN
———- — ———- ———- — ———-
10 C 4 1 A 1
10 C 4 2 B 1
10 C 4 3 C 2
10 C 4 4 E 3
10 C 4 5 F 3
10 C 4 6 I 3
10 C 4 7 D 2
10 C 4 8 H 3
10 C 4 9 J 3
최초 순전개 자료도 놓고 함께 비교해 봅니다
LEVELNO LVNO PA QNTY
—————- —— — —–
1 1 A 1
1 1 B 2
..2 2 C 3
….3 3 E 3
….3 3 F 4
….3 3 I 5
..2 2 D 4
….3 3 H 2
….3 3 J 4
……4 4 C 6
……..5 5 E 3
……..5 5 F 4
……..5 5 I 5
….3 3 K 3
최초 순전개 자료의 10번째에 위치하는
LEVELNO LVNO PA QNTY
—————- —— — —–
……4 4 C 6
이 자료와 관계가 있는 결과를 살피고 있는 중입니다.
이자료의 직계 상위 PART 는 순전개 자료를 보면 한눈에 알수 있듯이
J,D,B 입니다.
J,D,B 에 해당하는 건을 위의 9개 ROW에서 집중적으로 살펴보세요.
뭔가 다른 자료와 차이가 있을 겁니다.
뭐가 다를까요?.
세 자료가 모두 B.LEN 이 같은 자료들중에서 B.NO 가 가장 큰 자료들입니다.
즉 J의 경우는 B.LEN 이 3인
NO PA LEN NO PA LEN
———- — ———- ———- — ———-
10 C 4 4 E 3
10 C 4 5 F 3
10 C 4 6 I 3
10 C 4 8 H 3
10 C 4 9 J 3
이 다섯건중에 B.NO 가 9로 가장 값이 큽니다.
D 와 B도 마찬가지 입니다.
단계3.
여기까지 생각을 정리 했다면 이제 남은건 같은 A.NO 를 가진 ROW들을 대상으로
동일 B.LEN 을 가진것끼리 GROUP 을 지어 B.NO 가 최대값에 해당하는 자료만 걸러내면 됩니다.
걸러낸 결과가 우리가 원하던 특정부품을 기준으로 직계상위 부품이 되는겁니다.
단계2의 QUERY 를 약간 수정해 봅시다.
바뀌는 부분은 GROUP BY 를 위하여 SELECT 절이 수정되고 GROUP BY 가 추가되는 수준입니다.
SQL
SELECT A.NO,A.PARTNO,A.LEN,B.LEN,
MAX(LTRIM(TO_CHAR(B.NO,’0000000′))||LTRIM(TO_CHAR(B.QNTY,’000000000′))||B.PARTNO) TEM
FROM (SELECT NO,PARTNO,PART,
LEN
FROM (SELECT ROWNUM NO,PARTNO,PART,
LENGTHB(PART) -1 LEN
FROM (
SELECT LPAD(PARTNO, DECODE(LEVEL,1,1,LEVEL),’ ‘) AS PART,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) A,
(SELECT NO,PARTNO,PART,
LEN,QNTY
FROM (SELECT ROWNUM NO,PARTNO,PART,
LENGTHB(PART) -1 LEN,QNTY
FROM (
SELECT LPAD(PARTNO, DECODE(LEVEL,1,1,LEVEL),’ ‘) AS PART,PARTNO,QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) B
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
GROUP BY A.NO,A.PARTNO,A.LEN,B.LEN;
결과
NO PA LEN LEN TEM
———- — ———- ———- ——————–
1 A 0
2 B 0
3 C 1 0 0000002000000002B
4 E 2 0 0000002000000002B
4 E 2 1 0000003000000003C
5 F 2 0 0000002000000002B
5 F 2 1 0000003000000003C
6 I 2 0 0000002000000002B
6 I 2 1 0000003000000003C
7 D 1 0 0000002000000002B
8 H 2 0 0000002000000002B
8 H 2 1 0000007000000004D
9 J 2 0 0000002000000002B
9 J 2 1 0000007000000004D
10 C 3 0 0000002000000002B
10 C 3 1 0000007000000004D
10 C 3 2 0000009000000004J
11 E 4 0 0000002000000002B
11 E 4 1 0000007000000004D
11 E 4 2 0000009000000004J
11 E 4 3 0000010000000006C
12 F 4 0 0000002000000002B
12 F 4 1 0000007000000004D
12 F 4 2 0000009000000004J
12 F 4 3 0000010000000006C
13 I 4 0 0000002000000002B
13 I 4 1 0000007000000004D
13 I 4 2 0000009000000004J
13 I 4 3 0000010000000006C
14 K 2 0 0000002000000002B
14 K 2 1 0000007000000004D
31 rows selected.
66건의 자료가 31건으로 줄어들었습니다.
각 NO 별로 살펴보세요.
마지막 TEM 컬럼은 B.NO 가 MAX 인건을 찾을때
해당 레코드의 수량과 상위 PART 를 함께 묶어서 붙여놓은겁니다.
나중에 수량을 이용하기 위해 저지른 짓입니다.
단계4.
이제 남은 일은 최초 순전개 자료와 조인을 거는 일입니다.
이때 조인조건은 NO 가 동일한 EQUI-JOIN 입니다.
순전개 SQL 및 결과
SQL
SELECT ROWNUM NO,
LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
결과
NO LEVELNO LVNO PA QNTY
—– ———– —— — ——-
1 1 1 A 1
2 1 1 B 2
3 ..2 2 C 3
4 ….3 3 E 3
5 ….3 3 F 4
6 ….3 3 I 5
7 ..2 2 D 4
8 ….3 3 H 2
9 ….3 3 J 4
10 ……4 4 C 6
11 ……..5 5 E 3
12 ……..5 5 F 4
13 ……..5 5 I 5
14 ….3 3 K 3
14 rows selected.
최종 SQL 및 결과
SQL
SELECT B.NO,
B.LEVELNO,
B.LVNO,
B.PARTNO,
B.QNTY,
B.QNTY*EXP(SUM(LN(NVL(TO_NUMBER(SUBSTRB(A.TEM,8,9)),1))))
FROM (SELECT A.NO,A.PARTNO,A.LEN,A.PARTNO,B.LEN,
MAX(LTRIM(TO_CHAR(B.NO,’0000000′))||LTRIM(TO_CHAR(B.QNTY,’000000000′))||B.PARTNO) TEM
FROM (SELECT NO,PARTNO,
LEN
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN
FROM (
SELECT LEVEL LEV,PARTNO
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) A,
(SELECT NO,PARTNO,
LEN,QNTY
FROM (SELECT ROWNUM NO,PARTNO,
LEV LEN,QNTY
FROM (
SELECT LEVEL LEV,PARTNO,QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
)
)
) B
WHERE B.NO(+) < A.NO
AND B.LEN(+) < A.LEN
GROUP BY A.NO,A.PARTNO,A.LEN,A.PARTNO,B.LEN
) A,
(
SELECT ROWNUM NO,
LPAD(LEVEL, DECODE(LEVEL,1,1,(LEVEL*2)-1),’.’) AS LEVELNO,
LEVEL AS LVNO,
PARTNO,
QNTY
FROM BOM
START WITH PARTNOM=’X’
CONNECT BY PRIOR PARTNO=PARTNOM
) B
WHERE B.NO=A.NO
GROUP BY B.NO,
B.LEVELNO,
B.LVNO,
B.PARTNO,
B.QNTY
결과
NO LEVELNO LVNO PA QNTY B.QNTY*EXP
—– ———- —— — —— ———-
1 1 1 A 1 1
2 1 1 B 2 2
3 ..2 2 C 3 6
4 ….3 3 E 3 18
5 ….3 3 F 4 24
6 ….3 3 I 5 30
7 ..2 2 D 4 8
8 ….3 3 H 2 16
9 ….3 3 J 4 32
10 ……4 4 C 6 192
11 ……..5 5 E 3 576
12 ……..5 5 F 4 768
13 ……..5 5 I 5 960
14 ….3 3 K 3 24
14 rows selected
최종결과의 SELECT 중
B.QNTY*EXP(SUM(LN(NVL(TO_NUMBER(SUBSTRB(A.TEM,8,9)),1))))
설명
이부분은 자신의PART 수량과 자신의 상위PART 수량들간의 곱셈을 하기 위한 부분입니다.
자신의 상위에 해당하는 PART 만 찾아놓은 상태이므로 최종적으로 자신의 수량을 곱해줍니다.