by 마농 계층구조 PRIOR CONNECT BY LEVEL START WITH 계층구조 쿼리 Recursive Query CONNECT_BY_PATH [2013.10.23]
지난 "오라클 계층구조 쿼리의 응용" 퀴즈에서는 계층구조 하위노드들의 누적합을 구하는 문제를 다루었었습니다.
이번 퀴즈로 배워보는 SQL 시간에는 지난 퀴즈보다 난이도를 높여 하위노드의 누적합에 대해 부호를 적용하여 합산하는 문제를 풀어보도록 하겠습니다.
지면 특성상 문제와 정답 그리고 해설이 같이 있습니다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한 후 정답과 해설을 참조하길 바랍니다.
공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.
문제
코드(cd)와 부모코드(pcd)를 가진 원본테이블(<표 1> 참조) test 가 있습니다. 이 테이블을 이용하여 계층구조의 쿼리를 작성하세요. 이때, 자기 자신을 포함한 하위 노드들의 누적합을 구해야 합니다.
누적합을 구할때는 최하위 노드로부터 주어진 부호를 적용시켜서 합산하고, 합산된 결과는 상위노드의 결과에 합산하면서 상위노드의 부호를 다시 적용시켜야 합니다. 계산방식은 아래 결과테이블<표 2>를 참조하세요.
- [표 1] 원본 테이블
[표 2] 결과 테이블
문제설명
코드와 부모코드 그리고 코드에 해당하는 금액으로 이루어진 테이블이 있습니다. 코드와 부모코드와의 관계를 이용해 계층구조 쿼리를 전개하고, 자식 코드의 금액을 부모코드의 값에 합산하여 보여주는 문제입니다.
단, 합산할 때 부호를 적용하여 합산해야 합니다. 하위 코드로부터 상위코드로 합산해 나가는 과정을 단계별로 상세하게 설명하겠습니다.
이해를 돕기 위해 계산방법 참조란에 번호를 붙여서 설명하겠습니다. 코드 1111 은 자신의 값 50에 - 부호를 적용하여 -50이 됩니다.
④ -(50) = -50
1110은 자신의 값 200에 자식인 1111의 값 -50을 더해 + 부호를 적용합니다.
③ +(200 - 50) = 150
1120은 자신의 값 100에 + 부호를 적용합니다.
⑤ +(100) = 100
이렇게 구한 1110의 값 ③ 150 에 1120의 값 ⑤ 100을 더한 후 이값을 상위코드인 1100의 값 100에 합산하여 - 부호를 적용합니다.
② -(100 + 150 + 100) = -350
1200은 자신의 값 150에 자식인 1210의 값 ⑦ 300을 더해 - 부호를 적용합니다.
⑥ -(150 + 300) = -450
이렇게 구한 1100의 값 -350 에 1200의 값 -450을 더한후 이값을 상위코드인 1000의 값 0에 합산하여 + 부호를 적용합니다.
1000 : +(0 - 350 - 450) = -800
① +(0 - 350 - 450) = -800
이렇게 해서 최종 가장 상위 코드에 해당하는 값은 ① -800 이 되었습니다.
다음 <리스트 1>을 이용하여 문제를 풀어보세요.
CREATE TABLE TEST
AS
(
SELECT 1000 cd, NULL pcd, 0 amt, '+' sign FROM dual
UNION ALL SELECT 1100, 1000, 100, '-' FROM dual
UNION ALL SELECT 1110, 1100, 200, '+' FROM dual
UNION ALL SELECT 1111, 1110, 50, '-' FROM dual
UNION ALL SELECT 1120, 1100, 100, '+' FROM dual
UNION ALL SELECT 1200, 1000, 150, '-' FROM dual
UNION ALL SELECT 1210, 1200, 300, '+' FROM dual
);
SELECT * FROM test;
CD PCD AMT SIG
----- ---------- ---------- ---
1000 0 +
1100 1000 100 -
1110 1100 200 +
1111 1110 50 -
1120 1100 100 +
1200 1000 150 -
1210 1200 300 +
7 rows selected.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트2] 정답 리스트
SELECT LPAD(cd, 2+LEVEL*2, '-') cd
, LEVEL lv
, amt
, sign
, (SELECT SUM(DECODE(
MOD(LENGTH(REPLACE(SYS_CONNECT_BY_PATH(sign,','),'-')),2)
,0,1,-1) * amt)
FROM test
START WITH cd = main.cd
CONNECT BY PRIOR cd = pcd
) sum_amt
FROM test main
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
;
CD LV AMT SIG SUM_AMT
-------------------- ---------- ---------- --- ----------
1000 1 0 + -800
--1100 2 100 - -350
----1110 3 200 + 150
------1111 4 50 - -50
----1120 3 100 + 100
--1200 2 150 - -450
----1210 3 300 + 300
어떤가요? 여러분이 만들어본 리스트와 같은가요?
틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
문제해설
이번 퀴즈는 지난 "오라클 계층구조 쿼리의 응용"퀴즈의 후속편으로 하위에서 상위로 누적합계를 구하는 문제에서 더 나아가 단계별로 부호까지 적용하여 누적합계를 구하는 문제입니다.
지난시간 배웠던 내용을 복습하고, 한걸음 더 나아갈 수 있도록 문제를 구성해 보았습니다. 우선 코드와 상위코드만으로 계층 구조를 전개하는 쿼리는 CONNECT BY 절을 이용하여 풀어보도록 하겠습니다.
- [리스트 3] 계층구조 전개 쿼리
SELECT LPAD(cd, 2+LEVEL*2, '-') cd
, LEVEL lv
, amt
, sign
FROM test
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
;
CD LV AMT SIG
-------------------- ---------- ---------- ---
1000 1 0 +
--1100 2 100 -
----1110 3 200 +
------1111 4 50 -
----1120 3 100 +
--1200 2 150 -
----1210 3 300 +
7 rows selected.
<리스트 3>의 계층구조 전개 쿼리 에 대해 설명하겠습니다.
START WITH 절은 계층구조의 시작점을 의미합니다. 코드와 부모코드로 이루어진 계층구조에서 최상위코드는 부모코드가 없는 코드입니다.
START WITH pcd IS NULL
CONNECT BY 절은 계층구조를 전개하기 위해 필요한 상위와 하위코드 간의 관계를 나타냅니다. PRIOR 는 컬럼의 앞에 붙어 해당 항목이 상위의 값임을 나타냅니다.
CONNECT BY PRIOR cd = pcd
계층구조에서 단계, 레벨을 나타내 주는 함수는 LEVEL입니다.
, LEVEL lv
계층구조의 레벨에 따라 들여쓰기를 하기 위해 LEVEL 과 함께 LPAD 함수를 이용해 보았습니다.
SELECT LPAD(cd, 2+LEVEL*2, '-') cd
이번에는 부호를 적용하지 않고, 하위노드의 값을 상위노드로 누적합산하는 방법을 알아보겠습니다.
- [리스트 4] 하위노드의 값을 상위노드로 누적합산
SELECT LPAD(cd, 2+LEVEL*2, '-') cd
, LEVEL lv
, amt
, sign
, (SELECT SUM(amt)
FROM test
START WITH cd = main.cd
CONNECT BY PRIOR cd = pcd
) sum_amt
FROM test main
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
;
CD LV AMT SIG SUM_AMT
-------------------- ---------- ---------- --- ----------
1000 1 0 + 900
--1100 2 100 - 450
----1110 3 200 + 250
------1111 4 50 - 50
----1120 3 100 + 100
--1200 2 150 - 450
----1210 3 300 + 300
계층구조의 최상위인 1000 의 하위항목에 대한 누적합계는 어떻게 구하면 될까요? 최상위이므로 하위 모든 행의 금액를 합산하면 됩니다.
2레벨의 1100 의 경우엔 자기 자신을 포함하여 3레벨의 1110 과 다음 4레벨의 1111 까지 총 3개 코드의 금액를 합산하면 됩니다. 이처럼 각 코드마다 자기 자신으로부터 시작하여 자신의 자식코드들의 금액를 합산하면 됩니다.
다르게 표현하면 각 코드마다 자기 자신의 코드로 시작하는 계층구조 쿼리를 다시 한번 돌린다고 생각하면 이해하기 쉬울 듯 합니다.
<리스트 4>의 쿼리에서 스칼라서브쿼리에 해당하는 부분만 이해를 돕기 위해 다르게 표현하면 다음과 같습니다.
SELECT SUM(amt) FROM test START WITH cd = main.cd -- 메인 쿼리 결과 각행의 코드 CONNECT BY PRIOR cd = pcd ;
메인 쿼리 결과 각행의 코드를 시작조건으로 각 행마다 다시 계층구조 전개하여 나온 결과 금액을 합산하는 스칼라서브쿼리를 작성했습니다.
금액을 단순 합산하는 방법은 지난시간에 배웠던 부분 복습한 것이구요. 이번에는 부호를 적용하여 합산해야 합니다. 어떻게 하면 될까요? 다음 <리스트 5>의 쿼리 결과를 보면서 설명해 보겠습니다.
- [리스트 5] 부호의 연결
SELECT LPAD(cd, 2+LEVEL*2, '-') cd
, LEVEL lv
, amt
, sign
, SYS_CONNECT_BY_PATH(sign,',') sign_path
FROM test main
START WITH cd = 1000
CONNECT BY PRIOR cd = pcd
;
CD LV AMT SIG SIGN_PATH
-------------------- ---------- ---------- --- ----------
1000 1 0 + ,+
--1100 2 100 - ,+,-
----1110 3 200 + ,+,-,+
------1111 4 50 - ,+,-,+,-
----1120 3 100 + ,+,-,+
--1200 2 150 - ,+,-
----1210 3 300 + ,+,-,+
<리스트 5> 의 쿼리는 코드 1000 을 시작으로 하는 하위노드들을 모두 가져와 쇠상위 코드로부터 각각의 코드까지 도달하는데 거쳐간 부호들을 차례대로 나열하는 모습을 보여줍니다.
코드 1000은 자신의 부호 + 하나만 적용하면 되지만 2레벨의 코드 1100은 자신의 부호 -를 적용후 상위코드에 합산될때는 상위코드의 부호 +가 한번 더 적용되어야 합니다.
이와 마찬가지로 보면 레벨4의 1111은 모두 4번의 부호적용(,+,-,+,-)을 받아야 합니다.
50 * -1 * +1 * -1 * +1 = 50
결국 레벨 의 에 두 번 두 번씩 적용하면 최상위 4 50 + - 코드에 값이 합산될때는 50이 합산되게 됩니다. 3레벨의 1110의 경우엔 부호 3개(,+,-,+)가 적용되어 -200 이 합산되게 되는 것입니다.
결론부터 말하면 최종 합산될때의 부호 결정은 - 부호의 개수에 영향을 받게 됩니다.
상위에서 각각의 코드까지 도달할 때 거쳐온 부호들중에 - 가 1개이면 -가 되고 -가 2개이면 다시 +가 됩니다. 즉 - 부호가 홀수개일 때에만 - 부호가 적용이 되면 되는 것입니다.
자 이제 거의 다 왔습니다. CONNECT_BY_PATH 를 이용해 상위부터 부호를 연결하고, 이렇게 연결된 부호들중 -의 개수가 홀수개일때에만 - 로 처리해 주면 됩니다. 그렇다면 -의 개수를 세는 방법을 살펴보겠습니다.
<표 5>의 SIGN_PATH를 보면 항상 그 길이가 2의 배수임을 알 수 있습니다. 부호와 컴마가 쌍을 이루기 때문입니다. 이때 -부호만 지우고 컴마만 남긴다면?
-부호 하나를 지우면 SIGN_PATH의 길이는 홀수가 됩니다. 다시 -부호를 하나 더 지우면 다시 짝수가 됩니다. 즉, -부호를 지우고 SIGN_PATH의 길이를 쟀을 때 그 길이가 짝수인 지 홀수인지를 보면 되는 것입니다. 홀수 짝수 구분은 MOD 함수를 이용합니다.
정리해보면 부호의 연결경로인 SIGN_PATH를 구하고 -부호를 제거한뒤 길이를 구해, 그값을 2로 나눈 나머지가 0이면 양수(1), 1이면 음수(-1)을 각 하위코드의 금액에 곱해주고 그 값들을 모두 합산하면 되는 것입니다.
이를 쿼리로 표혐하면 최종 <리스트 2>의 정답쿼리가 완성됩니다.
- [리스트2] 정답 리스트
SELECT LPAD(cd, 2+LEVEL*2, '-') cd
, LEVEL lv
, amt
, sign
, (SELECT SUM(DECODE(
MOD(LENGTH(REPLACE(SYS_CONNECT_BY_PATH(sign,','),'-')),2)
,0,1,-1) * amt)
FROM test
START WITH cd = main.cd
CONNECT BY PRIOR cd = pcd
) sum_amt
FROM test main
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
;
CD LV AMT SIG SUM_AMT
-------------------- ---------- ---------- --- ----------
1000 1 0 + -800
--1100 2 100 - -350
----1110 3 200 + 150
------1111 4 50 - -50
----1120 3 100 + 100
--1200 2 150 - -450
----1210 3 300 + 300
- 강좌 URL : http://www.gurubee.net/lecture/2643
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.