계층구조 쿼리란?
오라클 데이터베이스 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
...
PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서 상하위간의 관계를 기술할때 사용.
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
PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서만 사용할 수 있는 것은 아니다.
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
PRIOR 컬럼에 따라(상위 OR 하위) 계층전개 방향이 달라진다.
- – 순방향(Top Down-상위~하위) : PRIOR 하위 = 상위
- – 역방향(Bottom Up-하위~상위) : PRIOR 상위 = 하위
댓글 남겨도 되나요?