SQLD 2과목 SQL 활용 - 계층형 질의와 셀프 조인 (1)

<목차> 서브쿼리 집합연산자 그룹 함수 윈도우 함수 TOP N 쿼리 계층형 질의와 셀프 조인 PIVOT 절과 UNPIVOT 절 정규 표현식

※ 자료출처: 데이터 자격검정 협회

 

6. 계층형 질의와 셀프 조인

계층형 질의

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 계층형 질의(Hierarchical Query) 사용

※ 계층형 데이터: 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

사원에 대한 순환관계 데이터 모델 (출처; 데이터 자격 검정)

---

Oracle 계층형 질의

 

Oracle은 계층을 질의를 지원하기 위해 다음과 같은 계층형 질의 구문 제공

SELECT ... FROM 테이블 WHERE condition AND condition... START WITH condition CONNECT BY [NOCYCLE] condition AND condition... [ORDER SIBLINGS BY column, column, ...

 

• START WITH 절: 게층 구조 전개의 시작 위치를 지정하는 구문 (루트 데이터 지정)
• CONNECT BY 절: 다음에 전개될 자식 데이터를 지정하는 구문
• PRIOR: CONNECT BY 절에 사용되며, 현재 읽은 칼럼 지정
• PRIOR 자식 = 부모 형태: 계층구조에서 부모데이터 (자식 → 부모) 방향으로 전개하는 순방향 전개
• PRIOR 부모 = 자식 형태: 계층구조에서 자식데이터 (부모 → 자식) 방향으로 전개하는 역방향 전개
• NOCYCLE: 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타나는 것을 가리켜 사이클(CYCLE)이 형성되었다고 하는데, 사이클이 발생한 데이터는 런타임 오류가 발생. ▶ NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개 하지 않음.
• ORDER SIBLINGS BY: 형제 노드(동일 LEVEL) 사이에서 정렬 수행
• WHERE: 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)

[Oracle 계층형 질의에서 사용되는 가상 칼럼]

가상 칼럼	설명
LEVEL	- 루트 데이터이면 1, 그 하위 데이터이면 2. - 리프(Leaf) 데이터까지 1씩 증가
CONNECT_BY_ISLEAF	전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	- 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0 - 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터 - CYCLE 옵션을 사용했을 때만 사용 가능

 

[예제] SELECT LEVVEL, LPAD(' ', 4 * (LEVEL-1) || 사원 사원,                                관리자, CONNECT_BY_ISLEAF ISLEAF FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자;

 

• A는 루트 데이터이기 때문에 레벨 1.
• A의 하위 데이터인 B, C는 레벨 2.
• C의 하위 데이터인 D, E는 레벨 3.
• 리프 데이터는 B, D, E
• 관리자 → 사원 방향을 전개하기 때문에 순방향 전개

순방향 게층형 질의 결과의 논리적인 모습 (출처: 데이터 자격 검정)

 

 

[예제]

사원 'D'로부터 자신의 상위관리자를 찾는 역방향 전개

SELECT LEVEL, LPAD(' ', 4*(LEVEL-1) || 사원 사원,                             관리자, CONNECT_BY_ISLEAF ISLEAF FROM 사원 START WITH 사원 = 'D' CONNECT BY PRIOR 관리자 = 사원;

 

• 역방향 전개이기 때문에 하위 데이터에서 상위 데이터로 전개
• 결과를 보면 내용을 제외하고 표시 형태는 순방향 전개와 동일
• D는 루트 데이터이기 때문에 레벨 1.
• D의 상위 데이터인 C는 레벨 2.
• C의 상위 데이터인 A는 레벨 3.
• 리프 데이터는 A.
• 루트 및 레벨은 전개되는 방향에 따라 반대가 됨.

역방향 계층형 질의 결과의 논리적인 모습 (출처: 데이터 자격 검정)

 

[Oracle 계층형 질의에서 사용되는 함수]

함수	설명
SYS_CONNECT_BY_PATH	- 루트 데이터부터 현재 전개할 데이터까지의 경로 표시 - 사용법: SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
CONNECT_BY_ROOT	- 현재 전개할 데이터의 루트 데이터 표시 - 단항 연산자 - 사용법: CONNECT_BY_ROOT

 

[예제]

SELECT CONNECT_BY_ROOT 사원 루트사원 SYS_CONNECT_BY_PATH(사원, '/') 경로, 사운, 관리자 FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자

 

• START WITH 를 통해 추출된 루트 데이터가 1건 이기 때문에 루트사원은 모두 A.
• 경로는 루트로부터 현재 데이터까지의 경로를 표시. (예. D의 경로는 A → C → D)

---

SQL Server 계층형 질의

 

SQL Server 2000 버전까지는 계층형 질의 작성 문법 미지원

▶ 조직도처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 WHILE 루프 문에서 임시 테이블을 사용하는 등 프로그램 방식으로 전개

 

SQL Server 2005 버전부터 지원

 

[예제]

Northwind 데이터베이스에 접속하여 Emplyoees 테이블의 데이터 조회

• 총 9개의 로우 중 ReportsTo 칼럼이 상위 사원에 해당, EmployeeID 칼럼과 재귀적 관계.
• EmplyoeeID가 2인 Fuller 사원의 경우, ReportsTo 칼럼 값이 NULL 이므로 계층 구조의 최상위.

 

[예제]

CTE(Common Table Expression)를 재귀 호출함으로써 Emplyoees 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리의 결과

• WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 쿼리 두 개를 결합. (위에 있는 쿼리 - 앵커 멤버(Anchor Member) / 아래에 있는 쿼리 - 재귀 멤버(Recursive Member)

■ 재귀적 쿼리의 처리 과정

• CTE 식을 앵커 멤버와 재귀 멤버로 분할
• 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만듦. ▶ 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인 시작
• Ti는 입력으로 사용하고 Ti+1 은 출력으로 사용하여 재귀 멤버 실행 ▶ 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인 반복
• 빈 집합이 반환될 때까지 3단계 반복 
• 결과 집합을 반환 ▶ 조인 결과가 비어 있으면 (더 조인할 수 없으면) 지금까지 만들어진 결과 집합을 모두 합하여 리턴
• T0에서 Tn 까지의 UNION ALL

[예제]

데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 그 결과

• CTE 재귀 호출로 만들어낸 계층 구조는 실제와 다른 모습으로 출력
• 조직도와 같은 모습으로 출력하려면 ORDER BY 절 추가해 원하는 순서대로 결과 정렬

[예제]

실제 조직도와 같은 모습의 결과를 출력하도록, CTE에 Sort 라는 칼럼 추가 + 쿼리 마지막에 Order by 조건 추가

 

[실행결과]

 

---

[이해가 안 될때 보면 좋은 영상]

[SQLD] 셀프조인, 계층형 질의 이해를 위한 꿀팁 영상

 

[계층형 질의 보강 자료]

SQLD 2과목 SQL 활용 - 계층형 질의(보강)

 

---

이전글

TOP N 쿼리

 

다음글

계층형 질의와 셀프 조인 (2)