[SQLD] 계층형 질의

계층형 데이터란?

동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

계층형 질의

• 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용한다.
• 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 통해 행과 행 사이의 계층을 표현하는 기법
• PRIOR 위치에 따라 연결하는 데이터가 달라진다.

SELECT * FROM 테이블 
	START WITH 시작조건 
	CONNECT BY PRIOR 연결조건;

• START WITH 절에서 필터링된 시작 데이터는 결과 목록에 포함!
• 이후 데이터는 CONNECT BY 절에 의해 필터링

예시 1 ) 각 부서의 레벨 출력

SELECT D.*, LEVEL FROM DEPT2 D 
	START WITH PDEPT IS NULL 
	CONNECT BY PRIOR DCODE = PDEPT;

예시 2 ) 연결 조건에 추가 조건이 붙는 경우

SELECT D.* FROM DEPT2 D 
	START WITH PDEPT IS NULL 
	CONNECT BY PRIOR DCODE = PDEPT AND AREA = '서울지사';

예시 3 ) LPAD로 들여쓰기 반영

SELECT D.*, LEVEL, LPAD(' ', (LEVEL-1)*4, ' ')|| DNAME AS 부서명 
	FROM DEPT2 D 
START WITH PDEPT IS NULL 
CONNECT BY PRIOR DCODE = PDEPT;

[SQL Server] 계층형 질의문   

• CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조 전개
• 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행

[Oracle] 계층형 질의문

• WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만 추출하는데 활용
• PRIOR 키워드는 SELECT, WHERE절에서도 사용 가능

종류	정의
STATRT WITH	계층 구조 전개의 시작 위치 지정
CONNECT BY	다음에 전개될 자식 데이터 지정 - 부모 계층형 쿼리에서 부모노드와 자식 노드 사이의 특정한 관계를 나타내는데 사용
PRIOR	CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다. ✦ PRIOR 자식 = 부모 형태 부모→자식 방향의 순방향 전개, 하위의 모든 노드 추출  ✦ PRIOR 부모 = 자식 형태 자식→부모 방향의 역방향 전개, 상위의 모든 노드 추출
NOCYCLE PRIOR	동일한 데이터가 전개되지 않음, 순환구조 발생 지점까지만 전개 (무한 루프 방지)
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)
LEVEL	각 depth 표현 → 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
CONNECT_BY_ISLEAF	해당 데이터가 리프 데이터면 1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	해당 데이터가 조상이면 1, 아니면 0 (CYCLE 옵션 사용했을 시만 사용 가능)
CONNECT_BY_ROOT 컬럼명	현재 전개할 데이터의 루트 노드(최상위)의 해당 컬럼명의 값을 출력
SYS_CONNECT_BY_PATH(컬럼, 구분자)	루트 데이터부터 현재 전개할 데이터까지의 이어지는 경로 출력 (구분자는 ‘-' 등으로)
ORDER SIBLINGS BY 컬럼	형제 노드간의 정렬 수행 (같은 LEVEL 일 때 정렬 순서 정하기)

예제 1 ) CONNECT_BY_ROOT / SYS_CONNECT_BY_PATH / ORDER SIBLINGS BY 비교하기

SELECT D.*, LEVEL, 
		LPAD(' ', (level-1)*4, ' ') || DNAME AS 학과명,
        CONNECT_BY_ROOT DNAME, 
        SYS_CONNECT_BY_PATH(DNAME, ' ')
	FROM DEPARTMENT D
CONNECT BY PRIOR DEPTNO = PDEPT
	ORDER SIBLINGS BY DNAME;
		

예제 2 ) 순방향 전개

select empno, mgr
from emp
start with mgr is null
connect by prior empno = mgr;

select level, empno, mgr
from emp
start with mgr is null
connect by prior empno = mgr;

select level, lpad(' ', 4*(level-1)) || empno, mgr,
connect_by_isleaf isleaf
from emp
start with mgr is null
connect by prior empno = mgr;

select level, lpad(' ', 4*(level-1)) || empno, mgr,
connect_by_root empno, sys_connect_by_path(empno,'/') path
from emp
start with mgr is null
connect by prior empno = mgr;

예제 3 ) 역방향 전개

select level, lpad(' ', 4*(level-1)) || empno, mgr
from emp
start with empno='7566'
connect by prior mgr = empno; -- empno = prior mgr 로 해도 됨