[SQL] 계층형 쿼리(Hierarchical Query)란?

---

SQLD를 공부할때, 읽는것만으로 이해가 잘 안되서 계층형 쿼리에 대해 한 번 알아보고자 한다. 계층형 쿼리란 테이블에 계층형 데이터가 존재하는 경우 이를 조회하기 위한 쿼리이다.

 

먼저 계층형 데이터란? 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. 예시로 하드웨어 계층적 구조를 살펴보면, 중앙처리장치와 기억장치, 입력장치의 상위 데이터는 하드웨어이며, 하드웨어의 하위 데이터는 중앙처리장치, 기억장치, 입출력장치 3개이다.

 

 

ex) 테이블로 나타냈을때 예시

	장치이름	상위장치
1	하드웨어	null
2	중앙처리장치	하드웨어
3	기억장치	하드웨어
4	입출력장치	하드웨어
5	입력장치	입출력장치
6	출력장치	입출력장치

 

계층의 쿼리의 형태를 살펴보자.

START WITH 조건
CONNECT BY [NOCYCLE] 조건
[ORDER BY SIBLINGS BY Colum];

[   ]의 내용들은 필수적인 사항들이 아니다.

 

START WITH절은 계층 구조 전개의 시작 위치를 지정하는 곳이다. 예를들어 위에서 언급한 테이블에서 START WITH 상위장치 IS NULL을 하면 1행을 시작위치로 지정하는 것이다.

CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는데 있어 PRIOR를 사용할때 지정한다. 이 경우 PRIOR가 Child = Parent 형태를 지닐 경우 순방향 전개. Parent = Child의 경우에는 역방향으로 전개를 한다.

 

(전개하니깐 왜 포병의 전개가 생각나지....라고 잠시 딴 생각을 해본 포병장교 출신 로춘남...)

 

CONNECT BY절에서 [   ] NOCYCLE은 데이터를 전개하면서 이미 나타난 데이터가 또 나타나는것을 방지해준다. ORDER BY SIBLINGS BY절은 형제 노드(동일 LEVEL) 사이에서 정렬을 수행해준다.

 

말로만 하기엔 조금 어려운 부분이 있으므로 출처 페이지에서 언급한대로 나도 직접 테이블을 만들어보면서 계층형 쿼리를 써봐야겠다.

 

CREATE TABLE TAB1 (
    장치이름 VARCHAR2(20),
    상위장치 VARCHAR2(20) );
    
INSERT INTO TAB1 VALUES ('하드웨어', NULL);
INSERT INTO TAB1 VALUES ('중앙처리장치', '하드웨어');
INSERT INTO TAB1 VALUES ('기억장치', '하드웨어');
INSERT INTO TAB1 VALUES ('입출력장치', '하드웨어');
INSERT INTO TAB1 VALUES ('입력장치', '입출력장치');
INSERT INTO TAB1 VALUES ('출력장치', '입출력장치');

SELECT * FROM TAB1;

이렇게 테이블을 만들고 계층형 쿼리를 실행해보면(순방향)

SELECT 장치이름, 상위장치, LEVEL FROM TAB1
START WITH 상위장치 IS NULL
CONNECT BY PRIOR 장치이름 = 상위장치;

다음과 같은 결과를 얻을 수 있다. LEVEL이라 가상컬럼을 넣은 이유는 계층형 쿼리가 어떤식으로 전개되는지 보기편하도록 자작성하였다고한다.

 

뭔가 정보처리기사 공부 할때 Fan In/Out의 느낌이 살짝 나는거 같기도...?!

 

START WITH 상위장치 IS NULL을 통해 하드웨어를 계층형 전개의 시작점으로 사용. 이때 시작점은 루트 데이터로 LEVEL 1을 의미한다. CONNECT BY PRIOR 장치이름 = 상위장치를 통해 자식 데이터를 전개하게 된다.

 

이후 이전 계층의 장치이름(하드웨어)를 상위장치로 가지고 있는 기억장치, 입출력장치, 중앙처리장치는 LEVEL 2가 된다.

이후 이전 계층의 장치이름(입출력장치)를 상위장치로 가지고 있는 입력장치, 출력장치는 LEVEL 3이 된다.

 

반대로 역방향 전개를 진행해본다.

SELECT 장치이름, 상위장치, LEVEL FROM TAB1
START WITH 상위장치 = '입출력장치'
CONNECT BY PRIOR 상위장치 = 장치이름;

또한 ORDER SIBLINGS BY절을 이용하여 같은 레벨의 노드 간에 정렬을 할 수도 있다.

 

---

출 처 : https://velog.io/@dongchyeon/%EC%98%A4%EB%9D%BC%ED%81%B4Oracle-%EA%B3%84%EC%B8%B5%ED%98%95-%EC%BF%BC%EB%A6%AC