본문 바로가기
My Image
전공지식/DataBase

[SQLD] 제2장 - DB계층형 질의와 셀프조인(Hierarchical Query & Self Join)_1

by Lim-Ky 2018. 6. 3.
반응형

  



DB계층형 질의와 셀프조인(Hierarchical Query & Self Join)


이번시간은 DB계층형 질의와 셀프조인(Hierarchical Query & Self Join)에 대해 알아보겠습니다.

먼저 각각의 개념을 알아보고, 실제 데이터베이스에서 어떻게 질의하고 사용하는지 알아보겠습니다.

우선, DB계층형 질의를 알아보도록 하겠습니다.DB계층형 질의를 들어가기 앞서, 계층형 데이터가 무엇인지 알아야합니다.



1. 계층형 데이터란?



테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의를 사용합니다.

여기서 잠깐 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말합니다.

예를 들어 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재하기 마련입니다. 


계층형 데이터를 가지고 있는 테이블과 계층형 데이터를 트리 그래프로 그려보겠습니다.







EMP 테이블에 사원과 관리자 컬럼이 있습니다.

예를 들어 B사원의 관리자는  A입니다.


즉,

A사원이 관리하는 사원들은 B,C입니다.

이런 원리를 적용하면 C사원이 관리하는 사원은 D,E입니다.

여기서 잠깐 A사원은 관리자가 없습니다. 네 맞습니다.

A사원은 자기보다 윗사람이 없습니다. 즉 A가 최상위 사원이라는 점을

알 수 있습니다.







위 테이블을 트리를 통해 그리면 다음과 같습니다.

트리 그래프를 통해, 더욱 확실하게 상위, 하위 데이터의 관계를 쉽게 파악할 수 있습니다.


A : 최상위

B,C : A의 자식

D,.E : C의 자식

D, E : 단말노드 (자식이 없는 노드)






2. ORACLE 계층형 질의



ORACLE에서 계층형 질의는 아래와 같은 형태입니다.


SELECT ...

FROM 테이블

WHERE  condition AND condition ...

START WITH condirion

CONNECT BY [NOCYCLE] condition AND condition ...

[ORDER SIBILINGS BY column, column, ... ]


각각 어떤 의미가 있는지 알아보겠습니다.



  START WITH 절

  

 계층 구조 전개 시작 위츠를 정하는 구문, 즉, 루트 데이터를 지정함


  CONNECT BY 절

   

자식 데이터를 지정하는 구문, 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야함 


  PRIOR 

  

 CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정함. 


  PRIOR 자식 = 부모 형태는 순방향으로 부모 -> 자식 방향임


  PRIOR 부모 = 자식 형태는 역방향으로 자식 -> 부모 방향임


  NOCYCLE

   

데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개중에 다시 나타나면 이것을 가리켜 사이클(CYCLE))이 형성되었다고 하며, 사이클이 발생한 데이터는 런타임 오류가 발생


하지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않음



  ORDER SIBLING BY 

   

형제 노드 (동일 LEVEL )사이에서 정렬을 수행 


  WHERE  

  

 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출함 





계층형 질의에서 사용되는 가상컬럼


 

 LEVEL 


 루트 데이터이면 1, 그 하위 데이터이면 2 이다. 리프(Leaf) 데이터까지 1씩 증가한다. 

 

 CONNECT_BY_ISLEAF 


 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0 이다.


 CONNECT_BY_ISCYCLE

 

 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0 이다. 여기서 조상이란 자신으로 부터 루트까지의 경로에 존재하는 데이터를 말합니다. CYCLE 옵션을 사용했을때만 사용할 수 있습니다. 




3.1 순방향(부모->자식) 계층형 질의 예제1



우선 EMP테이블에 대한 내용을 확인해보겠습니다.





EMP테이블을 유심히 보면, 사원번호 EMPNO 와 관리자번호 MGR 이 계층형 데이터라는 사실을 알 수 있습니다.

다음과 같은 쿼리를 통해 부모에서 시작해 자식 데이터를 찾아가는 모습을 확인 할 수 있습니다. 

LPAD 함수를 사용하여 공백을 레벨별로 줘서 더욱 명확하게 계층형 데이터를 결과값에서 확인 할 수 있도록 하였습니다.

START WITH MGR을 줘서 관리자번호가 부모컬럼임을 알 수 있으며, IS NULL 조건을 통해 관리자 번호가 없는 사원에서 부터 시작한다는 사실을 파악할 수 있습니다. 


또한, CONNECT BY 자식데이터를 지정할 수 있는데, PRIOR EMPNO = MGR을 통해 EMPNO가 자식데이터이고, 자식데이터 앞에 PRIOR 이 붙어있으므로 순방향(부모 -> 자식) 을 알 수 있습니다.






3.2 역방향(자식->부모) 계층형 질의 예제2



이번에는 역방향으로 특정 사원번호가 자신의 관리자 그리고 그 관리자의 관리자를 추적하여 최상위 루트를 찾아가는 쿼리를 작성해보겠습니다.

START WITH EMPNO = '7876' 을 통해, 시작 컬럼이 사원번호 7876 임을 알 수 있습니다.

또한, 자식데이터를 시작 루트로 지정함으로써, 자식데이터가 자신의 상위 계층 데이터를 찾아감을 눈치 챌 수 있습니다.


CONNECT BY PRIOR MGR = EMPNO; 이 부분을 통해 역방향(자식->부모)임을 더욱 확실히 알 수 있습니다.







4. 계층형 질의에서 사용되는 함수



ORCLE은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 아래와 같은 2가지 함수를 제공합니다.


함수 

설명 

SYS_CONNECT_BY_PATH 

루트 데이터부터 현재 전개할 데이터까지의 경로를 표시합니다.

사용법 : SYS_CONNECT_BY_PATH (칼럼,경로분리자) 

CONNECT_BY_ROOT 

현재 전개할 데이터의 루트 데이터를 표시한다. 단항연산자입니다.

사용법 : CONNECT_BY_ROOT                                               



SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT 를 적용한 예제를 보겠습니다.


경로 컬럼을 보면 경로 맨끝에 있는 사원번호 현재 전개할 데이터의 사원컬럼 값임을 알 수 있습니다.

즉, 현재 7566 사원이 전개 데이터이면, 경로는 7839/7566 이며, 7839는 관리자가 없는 최상위 루트 데이터 입니다.


이역시 마찬가지로 START WITH MGR IS NULL 이라는 구문을 통해 최상위 루트 데이터부터 전개를 시작할 것임을 알 수 있으며,

CONNECT BY PRIOR EMPNO = MGR 을 통해 순방향(부모 -> 자식) 전개임을 알 수 있습니다.





이번시간은 DB에서 사용하는 계층형 질의에 대해 알아봤습니다. 실제 실무에서 계층형 질의는 사원테이블, 조직테이블, 메뉴테이블, 제휴테이블 등등 많이 사용되기 때문에 꼭 숙지해야 합니다.


다음시간은 자기 자신을 JOIN하는 셀프 조인에 대해 알아보도록 하겠습니다.









반응형

댓글