[Oracle] 계층형 쿼리 사용법

계층형 쿼리라고 오라클에서 존재하는 문법이 있다.

테이블 내에서 예를들어

a	b
대한민국	서울
서울	송파구
서울	강남구
송파구	방이동
송파구	문정동

이와 같이 a는 무조건 상위 개념이고 b는 무조건 하위개념인것처럼 이런 계층형을 만들수 있는 쿼리이다.

 

지금 처럼 주소 관련이나 상사, 하사 같은 개념을 표기할때 유용하게 쓰이곤 한다.

 

사용법 이전에 각 문법에 대한 개념 설명은 다음과 같다.

 

• 계층형 쿼리 : 부모, 자식 간의 수직관계를 트리 구조 형태로 보여주는 쿼리
• START WITH : 트리 구조의 최상위 행을 지정
• CONNECT BY : 부모, 자식의 관계를 지정
• PRIOR : CONNECT BY 절에 사용되며 PRIOR에 지정된 컬럼이 맞은편 컬럼을 찾기
• CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼 : 부모 → 자식 순방향 전개
• CONNECT BY PRIOR 부모 컬럼 = 자식 컬럼 : 자식 → 부모 역방향 전개
• ORDER SIBLINGS : 계층형 쿼리에서 정렬을 수행

이 개념을 기반으로 간단한 예제는 아래와 같다.

 

데이터 생성

create table table1 ( col1 varchar2(1), col2 varchar2(1)); insert into table1 values('a','b'); insert into table1 values('b','c'); insert into table1 values('a','c'); insert into table1 values('c','d'); insert into table1 values('c','e'); insert into table1 values('e','f'); commit;

 

조회

col1  col2

----------

   a      b

   b      c

   a      c

   c      d

   c      e

   e      f

 

그럼 이제 이 데이터를 계층형 쿼리로 출력을 해볼것이다.

 

부모 -> 자식 순방향 전개 계층형 쿼리

select col1, col2, level from table1 start with col1 = 'a' connect by prior col2 = col1;

 

위에 설명 했듯이 순방향 전개는 마지막 connect by 에서 나뉜다.

그럼 결과는 다음과 같다.

col1   col2   LEVEL

------------------

   a       b          1 <- 첫번째 부모행을 시작으로 자식 탐색

   b       c          2

   c       d          3

   c       e          3

   e       f           4 <- 첫번째 부모행의 자식 탐색 종료

   a       c           1 <- 두번째 부모행의 자식 탐색 시작

   c       d           2

   c       e           2

   e       f            3 <- 두번째 부모행의 자식 탐색 종료

 

실행 결과는 level 1에는 최상위 레벨을 두고 자식이 없을때까지 계속 타고 들어갔다가 마지막까지 탐색 후 두번째 최상위 부모를 탐색한다.

 

그리고 추가적으로 

order siblings 를 사용하게 되면 

이전 쿼리에 마지막에 

order siblings by col2 desc;

를 추가 한다면 정렬이 이루어지게 된다.

 

col1   col2   LEVEL

------------------

   a       c          1 <- LEVEL 1 중에서 col2 desc 정렬했을 때 c가 가장 맨위에 옴

   c       e          2 <- LEVEL 2 중에서(위 LEVEL 1의 자식 기준) col2 desc 정렬했을 때 e가 가장 맨위에 옴

   e       f          3

   c       d          2

   a       b           1 <- LEVEL 1 중에서 child_c desc 정렬했을 때 두번째인 b가 옴

   b       c           2 

   c       e           3

   e       f           4

   c       d            3 

 

이런식으로 이루어진다.

 

역방향 전개를 한다면 connect by 절에서 순서를 바뀌어서 정렬을 하면 가능하다.