[Oracle] 계층구조 쿼리(Hierarchical Queries), (계층형 쿼리)

계층구조 쿼리(Hierarchical Queries)

 

Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다. (계층형 쿼리라고도 한다)

 

1. 문법정리

[START WITH condition] CONNECT BY condition

START WITH 조건1

- 루트 노드를 식별한다. 조건1을 만족하는 모든 ROW들이 루트노드가 된다.

- START WITH는 생략가능한데, 이러한 경우 모든 ROW들이 루트노드가 된다.

- 서브쿼리도 올 수 있다.

- CONNECT BY 는 부모 계층형 쿼리에서 부모노드와 자식노드 특정한 관계를 나타내는데 사용한다.

부모노드가 되는 쪽에 PRIOR operator 가 반드시 명시 되어야 한다.

 

CONNECT BY의 실행순서

(1) START WITH절

(2) CONNECT BY절

(3) WHERE 절

 

START WITH

- 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.

- 서브쿼리를 사용할 수도 있다.

 

CONNECT BY 조건

- 오직 계층형 쿼리에서만 사용하는 오라클 SQL 연산저

- 부모 로우를 식별하는데 사용

- Connect by 에 들어가는 조건은 오직 하나 만 가능

 

2. LEVEL

- 계층형 정보를 표현할 때 레벨을 나타낸다.

 

3. 의사컬럼들.

CONNECT_BY_ISLEAF

- 해당 로우가 리프노드인지 여부 체크, 리프로드인경우 1 반환 아닌경우 0

SYS_CONNECT_BY_PATH (COLUMN, CHAR)

- 계층형 쿼리에서만 사용, 루트노드로부터 해당로우 항목까지 경로 반환

CONNECT_BY_ISCYCLE : 무한루프 컬럼을 찾아냄

CONNECT_BY_ROOT   : 최상위 로우를 반환

 

4.정렬

ORDER BY, GROUP BY를 쓰면 계층형 구조가 깨져버리기 때문에 굳이 정렬이 필요하면 ORDER SIBLINGS BY를 사용한다.

Oracle 9i부터 지원.

계층성을 훼손하지 않으면서 원하는 정렬일 가능하도록 설정 가능.

 

5. SYS_CONNECT_BY_PATH

계층적 질의에서만 사용가능, 최상위 루트에서부터 해당 노드까지 결과를 이어준다.

 

6.장점과 단점

장점

테이블에서 계층형 구조를 표현하기 위한, 컬럼이나, 긴 데이터를 제거 하거나 줄일 수 있다.

간단한 쿼리로, 계층구조의 테이터를 가져올 수 있다.

계층구조의 변화를 쉽게 적용할 수 있다.

 

단점

오라클에서만 사용 가능

 

/

-- 1 번 --

with tempSal2 as (

select '팀장님' name, null as supername , '팀장' as part, '부장' as position, '40000' as sal from dual

union all

select '파트장' name, '팀장님' as supername , '포인트/카드' as part, '과장' as position, '5000' as sal from dual

union all

select '갓대희' name, '파트장' as supername , '포인트' as part, '주임' as position, '5000' as sal from dual

union all

select '곽켱진' name, '파트장' as supername , '카드' as part, '주임' as position, '10000' as sal from dual

union all

select '이지욘' name, '정촤노' as supername , '제휴' as part, '사원' as position, '20000' as sal from dual

union all

select '정촤노' name, '팀장님' as supername , '제휴' as part, '대리' as position, '20000' as sal from dual

union all

select '이정놤' name, '정촤노' as supername , '제휴' as part, '대리' as position, '30000' as sal from dual

)

-- 기본. PRIOR, 사용법, LEVEL, 사용법, LEAF인지 여부

SELECT LEVEL

, LPAD(' ',(LEVEL-1)*2,' ')||NAME AS NAME

, SUPERNAME

, PRIOR PART AS SUPERPART

, PART

, CONNECT_BY_ISLEAF AS 마지막노드여부

, SYS_CONNECT_BY_PATH (NAME, '/ ') PATH

FROM TEMPSAL2

START WITH NAME = '팀장님'

CONNECT BY SUPERNAME = PRIOR NAME

;

 

-- 2번 --

with tempSal2 as (

select '팀장님' name, null as supername , '팀장' as part, '부장' as position, '40000' as sal from dual

union all

select '파트장' name, '팀장님' as supername , '포인트/카드' as part, '과장' as position, '5000' as sal from dual

union all

select '갓대희' name, '파트장' as supername , '포인트' as part, '주임' as position, '5000' as sal from dual

union all

select '곽켱진' name, '파트장' as supername , '카드' as part, '주임' as position, '10000' as sal from dual

union all

select '이지욘' name, '정촤노' as supername , '제휴' as part, '사원' as position, '20000' as sal from dual

union all

select '정촤노' name, '팀장님' as supername , '제휴' as part, '대리' as position, '20000' as sal from dual

union all

select '이정놤' name, '정촤노' as supername , '제휴' as part, '대리' as position, '30000' as sal from dual

)

-- 단계별 그룹함수

SELECT LEVEL

, AVG(SAL) AS 평균급여

, COUNT(NAME) AS 인원수

FROM TEMPSAL2

START WITH SUPERNAME IS NULL

CONNECT BY SUPERNAME = PRIOR NAME

GROUP BY LEVEL

ORDER BY LEVEL

;

 

-- 3번 --

with tempSal2 as (

select '팀장님' name, null as supername , '팀장' as part, '부장' as position, '40000' as sal from dual

union all

select '파트장' name, '팀장님' as supername , '포인트/카드' as part, '과장' as position, '5000' as sal from dual

union all

select '갓대희' name, '파트장' as supername , '포인트' as part, '주임' as position, '5000' as sal from dual

union all

select '곽켱진' name, '파트장' as supername , '카드' as part, '주임' as position, '10000' as sal from dual

union all

select '이지욘' name, '정촤노' as supername , '제휴' as part, '사원' as position, '20000' as sal from dual

union all

select '정촤노' name, '팀장님' as supername , '제휴' as part, '대리' as position, '20000' as sal from dual

union all

select '이정놤' name, '정촤노' as supername , '제휴' as part, '대리' as position, '30000' as sal from dual

)

-- 역순으로 돌려보기

SELECT LEVEL

, LPAD(' ',(LEVEL-1)*2,' ')||NAME AS NAME

, SUPERNAME

, PRIOR PART AS SUPERPART

, PART

, CONNECT_BY_ISLEAF AS 마지막노드여부

, SYS_CONNECT_BY_PATH (NAME, '/ ') PATH

FROM TEMPSAL2

START WITH NAME = '갓대희'

CONNECT BY PRIOR SUPERNAME = NAME

;