퀴즈로 배우는 SQL
[퀴즈] 오라클 계층구조 쿼리의 응용 2 7 16,266

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

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 아발란체 [2013.01.31 09:17:37]

계층 쿼리... 종결 강좌....!!


by macoto [2013.03.27 14:48:11]
강좌 감사합니다! :D 
CONNECT_BY_ROOT를 이용해서 조인 방식으로 하는 방법은 생각 못 했네요 ㅎ

by 손님 [2013.04.04 10:53:33]

좋은 강좌 감사합니다.


by 박현갑 [2013.04.04 10:54:56]

좋은 강좌 감사합니다.  배움이 깊어졌습니다. ^^

by 헬로헬로일 [2014.10.12 03:32:15]

잘보고가용 ~


by XX큰타이거 [2015.01.20 11:37:37]

매번 느끼는 부분인데요..

마농님은 진정한 구세주 이십니다...ㅜㅜ

일반적인 계층형이 아니라 이틀 고생했는데 이 강좌를 차근차근 보니 기초적인 부분에 답이 있었네요.

또다시 기초의 중요성이...ㅜㅜ

한수 잘 배우고 갑니다. 


by 오류가 낙타낳다 [2017.02.17 17:54:33]

좋은 퀴즈와 해답 감사드립니다

select를 mgr max(sal) , min(sal), sum(sal)로 해야될 때에는 어떻게 해야될까요?

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입