계층구조 쿼리란?

오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.

예를 들어서 아래의 데이터를 보면

 EMPNO   ENAME    SAL    MGR
 ------ ------- ------ ------
   7369  SMITH     800   7902
   7902  FORD     3000   7566

• - empno 7369사원의 관리자는 7902의 empno를 가진 사원이며
• - empno 7902사원의 관리자는 7566의 empno를 가진 사원이다.

이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서 쉽게 조회 할 수 있다.

계층구조 쿼리 Synctax

START WITH

• - 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
• - 서브쿼리를 사용할 수도 있다.

CONNECT BY

• - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
• - PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
• - CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
• - CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
• - CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
• - 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn

• - LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY

• - ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.

• - 첫째 START WITH 절
• - 둘째 CONNECT BY 절
• - 세째 WHERE 절 순서로 풀리게 되어있다.

계층구조 쿼리 예제

간단예제

아래는 직업이 PRESIDENT을 기준으로 계층 구조로 조회하는 예이다.

-- LEVEL컬럼으로 depth를 알수 있다. 
-- JONES의 관리자는 KING 이며, SCOTT의 관리자는 JONES 이다. 
-- 상/하의 계층 구조를 쉽게 조회 할 수 있다. 
SELECT LEVEL, empno, ename, mgr
  FROM emp
 START WITH job = 'PRESIDENT' 
CONNECT BY PRIOR  empno = mgr;


LEVEL      EMPNO  ENAME        MGR
------ -------- --------    -------
     1       7839   KING
     2       7566   JONES      7839
     3       7788   SCOTT      7566 
     4       7876   ADAMS      7788 
     3       7902   FORD       7566
     4       7369   SMITH      7902
...

LEVEL의 활용

LEVEL Pseudocolumn을 이용하면 계층구조 쿼리를 좀 더 다양하게 활용 할 수 있다.

아래는 LEVEL의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 한눈에 볼 수 있게 표현한 예이다.

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL ename FORMAT A20;


-- 왼쪽에 LEVEL만큼 공백을 추가하여 계층구조로 조회하는 예제
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job 
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr; 


LEVEL ENAME                     EMPNO    MGR    JOB
------ --------------------    -------  -----  --------
     1 KING                       7839         PRESIDEN
     2     JONES                  7566   7839  MANAGER
     3         SCOTT              7788   7566  ANALYST
     4             ADAMS          7876   7788  CLERK
     3         FORD               7902   7566  ANALYST
     4             SMITH          7369   7902  CLERK
     2     BLAKE                  7698   7839  MANAGER
...

아래는 LEVEL별로 급여 합계와 사원수를 조회하는 예제이다.

-- LEVEL별로 급여 합계와 사원수를 조회하는 예제
SELECT LEVEL, AVG(sal) total, COUNT(empno) cnt
  FROM emp
 START WITH job='PRESIDENT' 
CONNECT BY PRIOR empno=mgr      
 GROUP BY LEVEL 
 ORDER BY LEVEL;

LEVEL      TOTAL        CNT
-------- ---------- ----------
       1       5000          1
       2       8275          3
       3      13850          8
       4       1900          2

PRIOR의 활용

PRIOR연산자를 SELECT 절에서 사용해보자.

아래는 사원의 관리자를 PRIOR연산자를 이용해서 조회하는 예제이다.

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL mgrname FORMAT A10;


-- SELECT절에 "PRIOR ename mgrname"을 확인해 보자
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, 
       PRIOR ename mgrname,
       empno, mgr, job 
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr; 


  LEVEL ENAME                MGRNAME         EMPNO        MGR JOB
------- -------------------- ---------- ---------- ---------- ---------
      1 KING                                  7839            PRESIDENT
      2     JONES            KING             7566       7839 MANAGER
      3         SCOTT        JONES            7788       7566 ANALYST
      4             ADAMS    SCOTT            7876       7788 CLERK
      3         FORD         JONES            7902       7566 ANALYST
      2     BLAKE            KING             7698       7839 MANAGER
      3         MARTIN       BLAKE            7654       7698 SALESMAN
      3         TURNER       BLAKE            7844       7698 SALESMAN
      3         JAMES        BLAKE            7900       7698 CLERK
      2     CLARK            KING             7782       7839 MANAGER
      3         MILLER       CLARK            7934       7782 CLERK

Bottom Up 조회 예제

위 간단 예제를 역순(자식에서 부모로 트리 구성, Bottom Up)으로 조회 해 보자

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL ename FORMAT A20;


-- ename을 기준으로 Bottom Up으로 조회하는 예제이다.
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job 
  FROM emp
 START WITH ename='SMITH'  -- 최 하위 노드 값이 와야 한다. 
CONNECT BY PRIOR mgr = empno; 


LEVEL ENAME                EMPNO      MGR    JOB
------ ---------------    -------- -------- ---------
     1 SMITH                 7369     7902   CLERK
     2     FORD              7902     7566   ANALYST
     3         JONES         7566     7839   MANAGER
     4             KING      7839            PRESIDENT