[Oracle] 고급 쿼리, 계층형 쿼리 (START WITH, CONNECT BY PRIOR, ORDER SIBLINGS BY)

[개념 정리]

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

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

 

select parent_c as p, child_c as c, level 
from t1
start with parent_c = 'a'
connect by prior child_c = parent_c;

[실행 결과]

P  C       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을 보면 첫 번째 최상위 부모 행을 찾으면 자식이 없을 때까지 계속 타고 들어갔다가 마지막까지 탐색 후 두 번째 최상위 부모 행의 자식을 탐색합니다.

---

[CONNECT BY에 조건절을 넣을 경우 예제]

select parent_c as p, child_c as c, level 
from t1
start with parent_c = 'a'
connect by prior child_c = parent_c and parent_c='c';

[실행 결과]

P  C       LEVEL
-- -- ----------
a  b           1
a  c           1
c  d           2
c  e           2

[실행 결과 해석]

start with 절에서 선택된 부모 데이터는 무조건 포함이 되고 자식 데이터들에 의해 parent_c = 'c' 조건으로 필터링된 결과가 나타납니다.

P  C       LEVEL
-- -- ----------
a  b           1
a  c           1
c  d           2
c  e           2

 

[ORDER SIBLINGS 사용한 계층형 쿼리 예제]

select parent_c as p, child_c as c, level 
from t1
start with parent_c = 'a'
connect by prior child_c = parent_c
order siblings by child_c desc;

[실행 결과]

P  C       LEVEL
-- -- ----------
a  c           1 <- LEVEL 1 중에서 child_c desc 정렬했을 때 c가 가장 맨위에 옴
c  e           2 <- LEVEL 2 중에서(위 LEVEL 1의 자식 기준) child_c 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

[실행 결과 해석]

LEVEL 1에서 진입하기 전에 정렬을 한 후 정렬한 결과의 첫 번째 행부터 자식 형을 찾습니다. 이는 자식행을 들어가서도 같은 LEVEL 내에서 정렬이 된 후 첫번째 행부터 타고 들어갑니다.

[자식 -> 부모 역방향 전개 계층형 쿼리 예제]

select parent_c as p, child_c as c, level
from t1
start with child_c = 'f'
connect by child_c = prior parent_c;

[실행 결과]

P  C       LEVEL
-- -- ----------
e  f           1 <- 첫번째 자식행을 시작으로 부모 탐색
c  e           2
a  c           3
b  c           3
a  b           4 <- 첫번째 자식행의 부모 탐색 종료

[실행 결과 해석]

child_c = 'f'에 대한 자식행이 1행 밖에 없으므로 (테스트 데이터 참조) 마지막 부모 계층까지 탐색 후 종료 됩니다.

---

결론

계층형 쿼리 사용법을 알아두면 부서별 계층 관계, 품목 설계도에서 부품 간 계층 관계 등 여러 비지니스 로직에 활용할 수 있을 것으로 생각됩니다.