by 마농 재귀 쿼리 Recursive Query 계층구조 쿼리 START WITH CONNECT BY PRIOR ORDER SIBLINGS BY CONNECT_BY_ROOT [2013.01.29]
이번 퀴즈로 배워보는 SQL 시간에는 오라클에서 사용할 수 있는 계층구조 쿼리를 이용해 하위 노드들의 값을 상위노드에 합산하여 보여주는 쿼리를 어떻게 작성하는지에 대해 알아본다.
지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한후 정답과 해설을 참조하길 바란다.
공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.
문제
사원번호(empno), 성명(ename), 상사의 사원번호(mgr) 를 가진 테이블 emp가 있습니다. 이 원본테이블(<표 1> 참조)을 이용하여 <표 2>의 계층구조 결과테이블을 완성하는 쿼리를 작성하세요
- 퀴즈 테스트용 scott user 생성 스크립트 참고
- [표 1] 원본 테이블
SELECT empno, ename, mgr, sal FROM emp; EMPNO ENAME MGR SAL ------ ---------- ---------- ---------- 8002 TIGER 7839 3000 8003 TIGER 7839 3000 7566 JONES 7839 2975 7654 MARTIN 7698 1250 7698 BLAKE 7839 2850 7782 CLARK 7839 2450 7788 SCOTT 7566 3000 7839 KING 5000 7844 TURNER 7698 1500 7876 ADAMS 7788 1100 7900 JAMES 7698 950 7902 FORD 7566 3000 7934 MILLER 7782 1300
- [표 2] 결과 테이블
LV ENAME ENAMES SAL SUM_SAL
------ --------------- ------------------------- ---------- ----------
1 KING KING 5000 31375
2 JONES KING-JONES 2975 10075
3 SCOTT KING-JONES-SCOTT 3000 4100
4 ADAMS KING-JONES-SCOTT-ADAMS 1100 1100
3 FORD KING-JONES-FORD 3000 3000
2 BLAKE KING-BLAKE 2850 6550
3 MARTIN KING-BLAKE-MARTIN 1250 1250
3 TURNER KING-BLAKE-TURNER 1500 1500
3 JAMES KING-BLAKE-JAMES 950 950
2 CLARK KING-CLARK 2450 3750
3 MILLER KING-CLARK-MILLER 1300 1300
2 TIGER KING-TIGER 3000 3000
2 TIGER KING-TIGER 3000 3000
문제설명
이번 퀴즈는 지난 "[퀴즈] 오라클 계층구조 쿼리의 이해"강좌 후속편으로 계층구조 쿼리의 응용 이라 제목을 붙여 봤습니다. 지난시간 배웠던 내용을 복습하고, 한걸음 더 나아갈 수 있도록 문제를 구성해 보았습니다.
이 문제는 사원번호(EMPNO)와 상사의 사원번호(MGR)를 가진 테이블(EMP)로부터 상사와 부하직원간의 관계를 이용하여 계층적인 구조를 표현하는 문제입니다.
<표 2>의 결과를 보면 상사가 없는 사원 KING으로부터 시작하여 레벨이 1이 되고, 그다음 상사가 KING인 부하직원 JONES, BLAKE, CLARK 이 레벨 2가 됩니다.
이때 부하직원 3명의 정렬순서는 사원번호 순서대로 정렬을 합니다. 그리고 각각의 2레벨 직원들의 부하직원이 3레벨로 연결되고, 다시 3레벨 직원의 부하직원이 4레벨로 연결되는 구조로 이루어집니다.
<표 2>의 결과 테이블에 추가된 항목은 다음과 같습니다.
- - LV 는 계층구조의 레벨을 의미합니다.
- - ENAME 은 사원이름을 계층 레벨에 따라 두칸씩 들여쓰기한 것입니다.
- - ENAMES 는 최상위 계층에서부터 해당 직원까지 연결된 이름을 나열한 것입니다.
- - SUM_SAL 은 계층구조의 하위항목에 대한 누적합계를 구한 것입니다. 다시 말하면, 자신의 급여를 포함하여 자신의 부하직원들의 급여를 합산한 금액입니다.
이번 문제의 핵심은 계층구조의 하위항목에 대한 누적합계를 어떻게 구할 것인가 입니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트1] 정답 리스트
SELECT LEVEL lv
, RPAD(' ', LEVEL*2-2) || ename ename
, SUBSTR(SYS_CONNECT_BY_PATH(ename,'-'), 2) enames
, sal
, (SELECT SUM(sal)
FROM scott.emp
START WITH empno = a.empno
CONNECT BY PRIOR empno = mgr
) sum_sal
FROM scott.emp a
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
지난 "[퀴즈] 오라클 계층구조 쿼리의 이해"강좌에서 이미 계층구조 쿼리에 대해 설명했었지만, 못보신 분들도 있을 것이고, 보신 분들도 다시 한번 복습하는 차원에서 처음부터 다시 설명해 나가도록 하겠습니다.
동일 테이블끼리 EMPNO 와 MGR 을 조인하면 2단계까지 부하직원을 찾을 수 있습니다. 하지만 이 문제는 부하직원의 부하직원을 계속해서 찾아 내려가야만 하는 문제로, 부하직원이 어느 레벨까지 내려갈지 알 수 없기 때문에 셀프 조인을 사용할 수는 없습니다.
따라서 이 문제를 풀기 위해서는 순환구조로 테이블을 반복적으로 읽어 내려갈 수 있는 쿼리가 필요합니다.
이렇게 반복적으로 자기 자신을 참조하는 쿼리를 재귀 쿼리(Recursive Query)라고 합니다.
오라클에서는 CONNECT BY 라고 하는 오라클만의 방식을 제공합니다. 다른 DBMS 에서는 WITH 문으로 정의 된 집합을 WITH 문 안에서 다시 조회하는 방식으로 재귀 쿼리를 구현합니다. 오라클 버전 10G 까지는 이와 같은 방식을 사용할 수 없었지만 11G 부터는 이와 같은 방식도 사용이 가능해 졌습니다.
하지만 이번 시간에는 오라클만의 독특한 방식인 CONNECT BY 절을 이용해 문제를 풀어 보도록 하겠습니다.
우선 계층구조를 전개하기 위해 필요한 것은 계층구조의 최상위 시작점입니다. 상사와 부하로 연결되는 구조에서 가장 최상위는 상사가 없는 직원이 됩니다.
즉 MGR 이 NULL 인 직원을 시작으로 계층구조를 전개하면 됩니다. START WITH 절은 계층구조의 시작조건을 주는 조건절이 됩니다.
START WITH MGR IS NULL
두 번째로 계층구조를 전개하기 위해 필요한 것은 상위와 하위 간의 관계입니다.
이 문제에서는 사원번호(EMPNO)와 상사의 사원번호(MGR) 간의 관계를 이용합니다. 정확하게 표현하면 상위 행의 사원번호와 하위 행의 상사의 사원번호가 서로 연결되는 구조입니다.
계층구조의 상하위간의 관계에 대한 조건은 CONNECT BY 절에 주게 됩니다. 이때 PRIOR 는 상위에 해당하는 값임을 의미합니다.
CONNECT BY PRIOR empno = mgr
이렇게 계층구조의 시작조건 구문과 계층관계 조건 구문이 완성되었습니다. 이 두 개 조건만으로 쿼리(<리스트 2>)를 실행해 보겠습니다.
- [리스트2] 계층구조 쿼리
SELECT empno
, ename
, mgr
, sal
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
;
- [표 3] 계층구조 쿼리 결과
EMPNO ENAME MGR SAL ------ --------------- ---------- ---------- 7839 KING 5000 7566 JONES 7839 2975 7788 SCOTT 7566 3000 7876 ADAMS 7788 1100 7902 FORD 7566 3000 7698 BLAKE 7839 2850 7654 MARTIN 7698 1250 7844 TURNER 7698 1500 7900 JAMES 7698 950 7782 CLARK 7839 2450 7934 MILLER 7782 1300 8002 TIGER 7839 3000 8003 TIGER 7839 3000
<리스트 2> 계층구조 쿼리의 실행결과 <표 3> 를 보면 계층관계를 이용해 결과가 나온 것을 알 수 있습니다. 시작조건과 관계조건 두 개 만 주면 손쉽계 계층구조 쿼리가 완성되는 것입니다.
하지만 결과가 원본과 크게 달라 보이지 않아 계층구조인지 아닌지 파악하기 어렵습니다. 나머지 추가 항목들을 만들어 보겠습니다.
계층구조에서 단계, 레벨을 나타내 주는 함수는 LEVEL입니다.
, LEVEL lv
계층구조의 레벨에 따라 들여쓰기를 하기 위해 RPAD 함수를 이용해 보겠습니다.
, RPAD(' ', LEVEL*2-2) || ename ename
계층구조 전개에 있어서 부하직원은 사원번호 순으로 정렬해야 합니다. 일반적인 정렬구문(<리스트 3>)을 사용해 보겠습니다.
- [리스트3] 일반 정렬 구문
SELECT empno
, LEVEL lv
, RPAD(' ', LEVEL*2-2) || ename ename
, SUBSTR(SYS_CONNECT_BY_PATH(ename, '-'), 2) enames
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY empno
;
- [표 4] 일반 정렬 구문 결과
EMPNO LV ENAME ENAMES ------- ---------- --------------- ----------------------- 7566 2 JONES KING-JONES 7654 3 MARTIN KING-BLAKE-MARTIN 7698 2 BLAKE KING-BLAKE 7782 2 CLARK KING-CLARK 7788 3 SCOTT KING-JONES-SCOTT 7839 1 KING KING 7844 3 TURNER KING-BLAKE-TURNER 7876 4 ADAMS KING-JONES-SCOTT-ADAMS 7900 3 JAMES KING-BLAKE-JAMES 7902 3 FORD KING-JONES-FORD 7934 3 MILLER KING-CLARK-MILLER 8002 2 TIGER KING-TIGER 8003 2 TIGER KING-TIGER
<표 4> 일반 정렬 구문 결과를 보면 전체 자료가 사원번호 순서대로 정렬이 되어 계층구조가 흐트러진 것을 확인 할 수 있습니다.
우리가 원하는 결과는 계층구조는 그대로 유지하면서 부하사원들끼리의 순서만 정렬해주는 것입니다.
이때 사용되는 구문은 ORDER SIBLINGS BY 구문입니다.
ORDER SIBLINGS BY empno
자 이제 마지막으로 계층구조의 하위항목에 대한 누적합계를 구해 보도록 하겠습니다. 계층구조의 최상위인 KING의 하위항목에 대한 누적합계는 어떻게 구하면 될까요?
최상위이므로 하위 모든 행의 급여를 합산하면 되겠구요. 2레벨의 JONES 의 경우엔 자기 자신을 포함하여 3레벨 부하직원 SCOTT, FORD 와 또 그 다음 4레벨 부하직원 ADAMS, SMITH 까지 총 5명의 급여를 합산하면 됩니다.
이처럼 각 사원마다 자기 자신으로부터 시작하여 자신의 부하직원들의 급여를 합산하면 됩니다. 다르게 표현하면 각 사원마다 자기 자신의 사원번호로 시작하는 계층구조 쿼리를 다시 한번 돌린다고 생각하면 이해하기 쉬울 듯 합니다.
- [리스트4] 사원번호 시작 조건
SELECT SUM(sal) FROM scott.emp START WITH empno = [각 사원의 사원번호 조건] CONNECT BY PRIOR empno = mgr ;
각 사원의 사원번호를 계층구조 쿼리의 시작 조건으로 주고 급여를 합산합니다.
위 구문을 메인 쿼리에 어떻게 적용하면 될까요? 각 레코드마다 계층구조 쿼리를 다시 돌리는 것은 스칼라서브쿼리(SELECT 절에서 사용되는 서브쿼리)를 이용하면 됩니다.
- [리스트5] 정답리스트(스칼라서브쿼리)
SELECT LEVEL lv
, RPAD(' ', LEVEL*2-2) || ename ename
, SUBSTR(SYS_CONNECT_BY_PATH(ename,'-'), 2) enames
, sal
, (-- 스칼라서브쿼리
SELECT SUM(sal)
FROM scott.emp
START WITH empno = a.empno
CONNECT BY PRIOR empno = mgr
) sum_sal
FROM scott.emp a
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno
;
스칼라 서브쿼리를 이용하여 정답 쿼리를 완성하였습니다.
스칼라 서브쿼리를 사용하면 각 레코드마다 서브쿼리가 따로 실행되는 단점도 있습니다. 그래서 이번에는 스칼라 서브쿼리가 아닌 조인방식으로도 풀어보겠습니다.
이번 누적 합산의 원리가 각 사원들을 시작점으로 하여 계층 결과를 합산하는 방식이라면? 계층구조 전개시 시작 조건을 안주고 전개하면 모든 사원이 시작점이 되어 계층 구조가 전개될 것입니다.
이때 각각의 시작점(사원)을 기준으로 급여를 합산한다면 같은 결과가 나올까요? 한번 시도해 보도록 하겠습니다.
- [리스트6] CONNECT_BY_ROOT 를 이용한 급여 합계
SELECT empno
, SUM(sal) sum_sal
FROM (SELECT CONNECT_BY_ROOT empno empno
, sal
FROM scott.emp
CONNECT BY PRIOR empno = mgr
)
GROUP BY empno
;
CONNECT_BY_ROOT 를 이용하여 각 계층 구조의 사작점을 구하고 이를 기준으로 급여를 합산하였습니다. 이 결과집합을 다시 emp 와 조인하여 계층구조 전개를 하면 원하는 결과가 나오게 됩니다.
- [리스트7] 정답리스트
SELECT LEVEL lv
, RPAD(' ', LEVEL*2-2) || ename ename
, SUBSTR(SYS_CONNECT_BY_PATH(ename,'-'), 2) enames
, sal
, sum_sal
FROM emp a
, (SELECT empno, SUM(sal) sum_sal
FROM (SELECT CONNECT_BY_ROOT empno empno
, sal
FROM scott.emp
CONNECT BY PRIOR empno = mgr)
GROUP BY empno
) b
WHERE a.empno = b.empno
START WITH a.mgr IS NULL
CONNECT BY PRIOR a.empno = a.mgr
;
- 강좌 URL : http://www.gurubee.net/lecture/2250
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
계층 쿼리... 종결 강좌....!!
CONNECT_BY_ROOT를 이용해서 조인 방식으로 하는 방법은 생각 못 했네요 ㅎ
좋은 강좌 감사합니다.
좋은 강좌 감사합니다. 배움이 깊어졌습니다. ^^
잘보고가용 ~
매번 느끼는 부분인데요..
마농님은 진정한 구세주 이십니다...ㅜㅜ
일반적인 계층형이 아니라 이틀 고생했는데 이 강좌를 차근차근 보니 기초적인 부분에 답이 있었네요.
또다시 기초의 중요성이...ㅜㅜ
한수 잘 배우고 갑니다.
좋은 퀴즈와 해답 감사드립니다
select를 mgr max(sal) , min(sal), sum(sal)로 해야될 때에는 어떻게 해야될까요?
) 버튼을 클릭하여 작성 하시면 됩니다.