오라클 데이터베이스에는 계층구조를 파악하게 해주기 위해 connect by 절을 지원합니다.
Oracle의 SAMPLE 스키마인 SCOTT의 EMP테이블을 통해 계층구조 쿼리에 대해서 알아보고 정리하겠습니다.
구문정리
WHERE : 데이터를 가져온 뒤 마지막으로 조건절에 맞게 정리
START WITH : 계층질의의 루트로 사용될 행을 지정,서브쿼리를 사용할 수 있음
CONNECT BY : 각 행들의 연결관계를 설정,서브쿼리를 사용할 수 없음
- CONNECT BY PRIOR 자식컬럼=부모컬럼 : 부모에서 자식으로 트리구성
- CONNECT BY PRIOR 부모컬럼=자식컬럼 : 자식에서 부모로 트리구성
LEVEL : 계층구조 쿼리에서 수행결과의 깊이를 의미
ORDER SIBLINGS BY : 계층구조 쿼리에서 편하게 정렬작업을 할 수 있음
START WITH는 가장 처음에 데이터를 거르는 플랜을 타게되고 따라서 이 컬럼에는 인덱스가 걸려있어야 성능을 보장받을 수 있습니다.
CONNECT BY절의 결과에는 LEVEL이라는 컬럼이 있으며 이는 계층의 깊이를 의미합니다.
CONNECT BY의 실행순서는 다음과 같습니다.
1. START WITH 절
2. CONNECT BY 절
3.WHERE절 순서로 풀리게 되어 있습니다.
1.SCOTT 스키마 생성
샘플 스키마인 scott이 없는 사람들을 아래의 sql파일을 다운받아서 수행하면 생성됩니다.
2.emp 테이블 살펴보기
EMPNO와 MGR컬럼이 존재합니다. MGR컬럼은 해당 사원의 관리자의 EMPNO를 의미합니다.
즉, 맨 위 컬럼의 SMITH의 상사는 EMPNO가 7902인 FORD(밑에서 두번째)라고 할 수 있습니다.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
7369 SMITH CLERK 7902 2020/11/19 20:08:48 800 20
7499 ALLEN SALESMAN 7698 2020/11/19 20:08:48 1600 300 30
7521 WARD SALESMAN 7698 2020/11/19 20:08:48 1250 500 30
7566 JONES MANAGER 7839 2020/11/19 20:08:48 2975 20
7654 MARTIN SALESMAN 7698 2020/11/19 20:08:48 1250 1400 30
7698 BLAKE MANAGER 7839 2020/11/19 20:08:48 2850 30
7782 CLARK MANAGER 7839 2020/11/19 20:08:48 2450 10
7788 SCOTT ANALYST 7566 2020/11/19 20:08:48 3000 20
7839 KING PRESIDENT 2020/11/19 20:08:48 5000 10
7844 TURNER SALESMAN 7698 2020/11/19 20:08:48 1500 0 30
7876 ADAMS CLERK 7788 2020/11/19 20:08:48 1100 20
7900 JAMES CLERK 7698 2020/11/19 20:08:48 950 30
7902 FORD ANALYST 7566 2020/11/19 20:08:48 3000 20
7934 MILLER CLERK 7782 2020/11/19 20:08:48 1300 10
14 rows selected.
3.계층구조 쿼리 예제
직업이 PRESIDENT(Root값)을 기준으로 계층 구조로 조회하는 예입니다.
JONES의 관리자는 KING이며, SCOTT의 관리자는 JONES입니다.
LEVEL 컬럼을 통해 각 데이터 간 계층을 확인할 수 있습니다.
(1이 제일 상위,4가 제일 하위)
SQL> --select * from emp;
SQL> select level,empno,ename,mgr,job
from emp
start with job='PRESIDENT'
connect by prior EMPNO=MGR;
LEVEL EMPNO ENAME MGR JOB
----------- ----- ---------- ----- ---------
1 7839 KING PRESIDENT
2 7566 JONES 7839 MANAGER
3 7788 SCOTT 7566 ANALYST
4 7876 ADAMS 7788 CLERK
3 7902 FORD 7566 ANALYST
4 7369 SMITH 7902 CLERK
2 7698 BLAKE 7839 MANAGER
3 7499 ALLEN 7698 SALESMAN
3 7521 WARD 7698 SALESMAN
3 7654 MARTIN 7698 SALESMAN
3 7844 TURNER 7698 SALESMAN
3 7900 JAMES 7698 CLERK
2 7782 CLARK 7839 MANAGER
3 7934 MILLER 7782 CLERK
14 rows selected.
4.LEVEL을 활용하기
LEVEL을 활용하여 LEVEL의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 시각적으로 더 쉽게 볼수 있게 할 수 있습니다.
SQL> 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 PRESIDENT
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
3 ALLEN 7499 7698 SALESMAN
3 WARD 7521 7698 SALESMAN
3 MARTIN 7654 7698 SALESMAN
3 TURNER 7844 7698 SALESMAN
3 JAMES 7900 7698 CLERK
2 CLARK 7782 7839 MANAGER
3 MILLER 7934 7782 CLERK
14 rows selected.
LEVEL별로 급여 합계와 사원수를 조회하는 예제
SQL> SELECT LEVEL, SUM(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
4 rows selected.
BOTTOM UP 조회하기
SQL> SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
FROM emp
START WITH ename='SMITH' -- 최 하위 노드 값이 와야 한다.
CONNECT BY PRIOR mgr = empno;
2 3 4
LEVEL ENAME EMPNO MGR JOB
---------- -------------------- ---------- ---------- ---------
1 SMITH 7369 7902 CLERK
2 FORD 7902 7566 ANALYST
3 JONES 7566 7839 MANAGER
4 KING 7839 PRESIDENT
LEVEL에 조건절을 걸어 원하는 깊이까지만 보기
connect by 에 AND 조건을 추가하여 원하는 깊이까지 확인할 수 있습니다.
--connect by 절에 AND 조건 추가
SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR AND LEVEL <= 3;
LEVEL EMPNO ENAME MGR JOB
----------- ----- ---------- ----- ---------
1 7839 KING PRESIDENT
2 7566 JONES 7839 MANAGER
3 7788 SCOTT 7566 ANALYST
3 7902 FORD 7566 ANALYST
2 7698 BLAKE 7839 MANAGER
3 7499 ALLEN 7698 SALESMAN
3 7521 WARD 7698 SALESMAN
3 7654 MARTIN 7698 SALESMAN
3 7844 TURNER 7698 SALESMAN
3 7900 JAMES 7698 CLERK
2 7782 CLARK 7839 MANAGER
3 7934 MILLER 7782 CLERK
12 rows selected.
--where 절로 조건 추가
SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB
FROM EMP
where LEVEL <= 3
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR;
LEVEL EMPNO ENAME MGR JOB
----------- ----- ---------- ----- ---------
1 7839 KING PRESIDENT
2 7566 JONES 7839 MANAGER
3 7788 SCOTT 7566 ANALYST
3 7902 FORD 7566 ANALYST
2 7698 BLAKE 7839 MANAGER
3 7499 ALLEN 7698 SALESMAN
3 7521 WARD 7698 SALESMAN
3 7654 MARTIN 7698 SALESMAN
3 7844 TURNER 7698 SALESMAN
3 7900 JAMES 7698 CLERK
2 7782 CLARK 7839 MANAGER
3 7934 MILLER 7782 CLERK
12 rows selected.
출처 : www.gurubee.net/lecture/1300
출처 : mozi.tistory.com/155
'SQL(쿼리)' 카테고리의 다른 글
| 실행계획 확인하기,실제 실행계획 확인하기 (0) | 2020.12.22 |
|---|---|
| 트리거 생성(IF문 활용해서 Not Null인 컬럼만 트리거 작동) (0) | 2020.12.07 |
| ROLLUP을 대신하는 방법 (0) | 2020.11.24 |
| ROLLUP (0) | 2020.11.20 |
| COUNT 집계함수 (0) | 2020.11.11 |
댓글