계층구조 쿼리란?
오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.
예를 들어서 아래의 데이터를 보면
1 2 3 4 | 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을 기준으로 계층 구조로 조회하는 예이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | -- 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의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 한눈에 볼 수 있게 표현한 예이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | -- 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별로 급여 합계와 사원수를 조회하는 예제이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -- 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연산자를 이용해서 조회하는 예제이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- 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)으로 조회 해 보자
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- 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 |
- - 순방향(Top Down-상위~하위) : PRIOR 하위 = 상위
- - 역방향(Bottom Up-하위~상위) : PRIOR 상위 = 하위
출처:http://www.gurubee.net/lecture/1300
Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.
예를 들면, 아래와 같이 직원 테이블이 있다고 생각 하자.
직원 직속상사 직급
--------------------
철수 순희 대리
순희 영희 과장
길동 순희 대리
영희 개똥 부장
개똥 사장
기본적인 SQl을 사용하여 계층 관계를 표현하는것은 불가능하다. 하지만 재귀 PL/SQL 루틴과 connect by 를 사용한다면 표현이 가능하다.
재귀 PL/SQL은개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야한다는 점도 무시 할수 없다.
이에 오라클에서는 connect by라는 확장된 select 구문을 지원한다.
기본형식
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원
직원 직급
-------------
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
start with
select 구문의 start with 절은 계층 구조가 어떤 행에서 시작하는지 지정하는 기능을 한다.
정의 : start with <조건>
where 절의 내용으로 쓸 수 있는 조건이라면 start with로도 사용이 가능하며, 하나 이상의 조건을 결함하는 것도 가능하다.
ex) start with 직원 ='개똥'and 직원 ='순희'
start with 적의 조건에 맞는 행은 결과셋의 루트 노드가 된다. 주의할점은 조건에 맞는 행이 한 번 이상 등장할 경우이다.
예를 들면 start with 직원 ='개똥'and 직원 ='순희' 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.
(한번은 개똥의 하위에서, 그리고 한 번은 루트로서)
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 = '개똥' or 직원 ='순희'
connect by 직속상사 = prior 직원 직원 직급
-------------
순희 과장
철수 대리
길동 대리
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
처음 쿼리의 예제에서 직원 ='개똥'이라는 조건을 사용했으며, 이는 회사의 가장 높은 사람을 의미하는 것으로 전체 직원에 대한 목록이 만들어 진다. 하지만 이러한 방법은 그다지 좋지 않다. 왜냐하면, 개똥이 테이블에서 빠져나간다면 새로운 쿼리를 작성하여 직속상사가 의 값이 NULL 인 직원으로 부터 루트 노드가 다시 시작되도록 해야할 것이다.
그러므로, 가능하면 보다 구체적인, 즉 결과셋의 양이 적은 조건을 사용하는 것이 바람직하다. 직원 테이블을 보면 개똥의 직속상사의 값이 NULL로 저장되어 있는데, 이는 개똥이라는 직원이 보고할 사람이 없음을, 즉 가장 최상의 간부임을 의미한다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직속상사 is null
connect by 직속상사 = prior 직원
직원 직급
-------------
개똥 사장
영희 부장
순희 과장
철수 대리
길동 대리
Connect by Prior
connect by 절은 각 행이 어떻게 연결되는지를 오라클에게 알려주는 역할을 한다. 즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다.
현재 행과 다른 행은 Prior라는 키워드를 통해 구별된다. Prior는 상위 행을 참조하는 것으로, 우리의 예제에서는 다음과 같이 사용되었다.
connect by 직속상사 = prior 직원
이는 "방금 전 행의 직원 값이 현재 행의 직속상사 값인 모든 행을 찾아라"라는 의미이다.
쉽게 말하면, 방금전에 살펴본 직원이 현재 직원의 상사가 되는 방식으로 리턴하라는 것이다.
다음 예제 코드를 보면, prior 부분이 = 기호를 사이에 두고 반대편으로 건너갔는데, 결과는 다음과 같이 트리를 거슬러 내려가는 것이 아니라, 거슬러 올라가는 방식으로 리턴되었다.
select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원
직원 직급
-------------
철수 대리
순희 과장
영희 부장
개똥 사장
이 쿼리에서는 철수가 루트 노드이며, 그의 상사가 오히려 아래에 표현되어 있다. 그 이유는 " 방금 전 행의 직속상사 값이 현재 행의 직원 값인 모든 행을 찾아라"라고 선언했기 때문이다. 이와 같이 prior 키워드를 등호의 반대편으로 넣어도 오류가 발생하지 않고, 전혀 다른 결과가 얻어짐을 알 수 있다.
prior 키워드는 또한 이전 행의 열을 참조하기 위해 다음과 같이 select 절 내에서 사용 될 수도 있다.
select lpad(' ',(level-1)*2,' ')||직원 직원, prior 직원 상사,직급
from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원
직원 상사 직급
-------------------
철수 대리
순희 철수 과장
영희 순희 부장
개똥 영희 사장
여기서는 직원과 직속상사의 이름을 동시에 선택하였는데, 사실 두 값은 같은 행에 존재하는 것이 아니기 때문에 평범한 방법으로는 이와 같은 결과를 얻을 수 없다. 그래서 예제에서는 두 행을 동시 접근하여 각각 값을 얻어낸 것이다.
Level
level은 오라클에서 실행되는 모든 쿼리 내에서 사용 가능한 가상-열로서, 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수값이다.
계층적인 쿼리가 아니라면 다음과 같이 모든 값이 0, 즉 같은 단계를 가질 것이다.
select 직원,level
from 직원
직원 level
-----------
철수 0
순희 0
길동 0
영희 0
개똥 0
한편, 계층적 쿼리에서는 level의 값을 통해 트리에서의 위치를 확인할 수 있다. 루트 노드의 level 값이 1이다.
select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사
직원 직급 level
-------------------
개똥 사장 1
영희 부장 2
순희 과장 3
철수 대리 4
길동 대리 4
트리를 한 단계씩 거슬러 내려갈 때마다 값이 1씩 증가함을 알 수 있다.
level은 여러 가지 면에서 아주 유용하다. 먼저, 다음과 같이 각 항목을 출력할 때 앞에 붙는 공백의 양을 조절하여 계층적인 형식을 한눈에 알아볼 수 있도록 하는 것이 가능하다.
select lpad(' ',(level-1)*2,' ')||직원 직원
또한, level 값이 3까지인 내용만을 출력하라. 등의 명령도 가능하다.
select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사 and level <=3
직원 직급 level
-------------------
개똥 사장 1
영희 부장 2
순희 과장 3
철수와 길동의 경우는 level 값이 4이기 때문에 출력되지 않았다.
level <=3 이라는 조건을 where 절이 아닌 connect by 절에 넣은 것에 주의해야한다. 어떤 곳에 넣어도 결과는 같지만, where 절에 넣으면 전체 트리를 구성한 후에 다시 선택하는 반면, connect by 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다.
http://blog.naver.com/crossbow71?Redirect=Log&logNo=60111909206
START WITH ... CONNECT BY 절
설명
계층적 질의란 테이블에 포함된 행(row)간에 수직적 계층 관계가 성립되는 데이터에 대하여 계층 관계에 따라 각 행을 출력하는 질의이다. START WITH ... CONNECT BY 절은 SELECT 구문과 결합하여 사용된다.
구문
SELECT column_list
FROM table_joins | tables
[WHERE join_conditions and/or filtering_conditions]
[START WITH condition]
CONNECT BY [NOCYCLE] condition
START WITH 절
START WITH 절은 계층 관계가 시작되는 루트 행(root row)을 지정하기 위한 것으로, START WITH 절 다음에 계층 관계를 검색하기 위한 조건식을 포함한다. 만약, START WITH 절에 다음에 위치하는 조건식이 생략되면 대상 테이블 내에 존재하는 모든 행을 루트 행으로 간주하여 계층 관계를 검색할 것이다.
참고 START WITH 절이 생략되거나, START WITH 조건식을 만족하는 결과 행이 존재하지 않는 경우, 테이블 내의 모든 행을 루트 행으로 간주하여 각 루트 행에 속하는 하위 자식 행들 간 계층 관계를 검색하므로 결과 행들 중 일부는 중복되어 출력될 수 있다.
CONNECT BY [NOCYCLE] PRIOR 절
- PRIOR : CONNECT BY 조건식은 한 쌍의 행에 대한 상-하 계층 관계(부모-자식 관계)를 정의하기 위한 것으로, 조건식 내에서 하나는 부모(parent)로 지정되고, 다른 하나는 자식(child)으로 지정된다. 이처럼 행 간의 부모-자식 간 계층 관계를 정의하기 위하여 CONNECT BY 조건식 내에 PRIOR 연산자를 이용하여 부모 행의 컬럼 값을 지정한다. 즉, 부모 행의 컬럼 값과 같은 컬럼 값을 가지는 모든 행은 자식 행이 된다.
- NOCYCLE : CONNECT BY 절의 조건식에 따른 계층 질의 결과는 루프를 포함할 수 있으며, 이것은 계층 트리를 생성할 때 무한 루프를 발생시키는 원인이 될 수 있다. 따라서, CUBRID는 루프를 발견하면 기본적으로 오류를 반환하고, 특수 연산자인 NOCYCLE이 CONNECT BY 절에 명시된 경우에는 오류를 발생시키지 않고 해당 루프에 의해 검색된 결과를 출력한다.
만약, CONNECT BY 절에서 NOCYCLE이 명시되지 않은 계층 질의문을 수행 중에 루프가 감지되는 경우, CUBRID는 오류를 반환하고 해당 질의문을 취소한다. 반면, NOCYCLE이 명시된 계층 질의문에서 루프가 감지되는 경우, CUBRID는 오류를 반환하지는 않지만 루프가 감지된 행에 대해 CONNECT_BY_ISCYCLE 값을 1로 설정하고, 더 이상 계층 트리의 검색을 확장하지 않을 것이다.
예제
아래 예제를 참조하여 계층 질의문을 작성할 수 있다. 예제를 실습하기 위해 필요한 데이터베이스 스키마는 다음과 같다.
tree 테이블
ID | MgrID | Name | BirthYear |
|---|---|---|---|
1 | NULL | Kim | 1963 |
2 | NULL | Moy | 1958 |
3 | 1 | Jonas | 1976 |
4 | 1 | Smith | 1974 |
5 | 2 | Verma | 1973 |
6 | 2 | Foster | 1972 |
7 | 6 | Brown | 1981 |
tree_cycle 테이블
ID | MgrID | Name |
|---|---|---|
1 | NULL | Kim |
2 | 11 | Moy |
3 | 1 | Jonas |
4 | 1 | Smith |
5 | 3 | Verma |
6 | 3 | Foster |
7 | 4 | Brown |
8 | 4 | Lin |
9 | 2 | Edwin |
10 | 9 | Audrey |
11 | 10 | Stone |
-- tree 테이블을 만들고 데이터를 삽입하기
CREATE TABLE tree(ID INT, MgrID INT, Name VARCHAR(32), BirthYear INT);
INSERT INTO tree VALUES (1,NULL,'Kim', 1963);
INSERT INTO tree VALUES (2,NULL,'Moy', 1958);
INSERT INTO tree VALUES (3,1,'Jonas', 1976);
INSERT INTO tree VALUES (4,1,'Smith', 1974);
INSERT INTO tree VALUES (5,2,'Verma', 1973);
INSERT INTO tree VALUES (6,2,'Foster', 1972);
INSERT INTO tree VALUES (7,6,'Brown', 1981);
-- tree_cycle 테이블을 만들고 데이터를 삽입하기
CREATE TABLE tree_cycle(ID INT, MgrID INT, Name VARCHAR(32));
INSERT INTO tree_cycle VALUES (1,NULL,'Kim');
INSERT INTO tree_cycle VALUES (2,11,'Moy');
INSERT INTO tree_cycle VALUES (3,1,'Jonas');
INSERT INTO tree_cycle VALUES (4,1,'Smith');
INSERT INTO tree_cycle VALUES (5,3,'Verma');
INSERT INTO tree_cycle VALUES (6,3,'Foster');
INSERT INTO tree_cycle VALUES (7,4,'Brown');
INSERT INTO tree_cycle VALUES (8,4,'Lin');
INSERT INTO tree_cycle VALUES (9,2,'Edwin');
INSERT INTO tree_cycle VALUES (10,9,'Audrey');
INSERT INTO tree_cycle VALUES (11,10,'Stone');
-- CONNECT BY 절을 이용하여 계층 질의문 수행하기
SELECT id, mgrid, name
FROM tree
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name
======================
1 null Kim
2 null Moy
3 1 Jonas
3 1 Jonas
4 1 Smith
4 1 Smith
5 2 Verma
5 2 Verma
6 2 Foster
6 2 Foster
7 6 Brown
7 6 Brown
7 6 Brown
-- START WITH 절을 이용하여 계층 질의문 수행하기
SELECT id, mgrid, name
FROM tree
START WITH mgrid IS NULL
CONNECT BY prior id=mgrid
ORDER BY id;
id mgrid name
=============================
1 null Kim
2 null Moy
3 1 Jonas
4 1 Smith
5 2 Verma
6 2 Foster
7 6 Brown
http://www.cubrid.org/ko_manual41/entry/START%20WITH%20%E2%80%A6%20CONNECT%20BY%20%EC%A0%88