[SQLD/P] table 계층구조와 CONNECT BY

Hyo Kyun Lee·2022년 8월 5일

SQLD/P

목록 보기

15/82

table 계층구조란, table을 이루는 data들이 계층구조를 이루고 있는 상황을 의미한다.

이 계층구조를 처음 접하면 이해하기가 어려운데, 아래와 같은 구조가 있다고 해보자.

이때 화살표가 가르키는 방향으로 data의 포함관계(혹은 상하관계)가 구축이 되는 것을 파악할 수 있다. 이러한 data 구조를 계층구조라 한다.

이러한 계층구조에 대해 table로 표현해보자.

위 table 구조에서, 일련번호는 각 분류항목에 대한 PK(인덱스)를 나타내고 상위번호는 각 분류항목이 속하는 상위항목(부모항목)의 일련번호를 의미한다.

계층구조 관련하여, 반드시 기억해야 하는 4가지 키워드가 있다.

POINT1

우리가 원하는 결과는 위 table, 각 분류항목 별 계층구조가 정해져 있는 상태의 table이다.

이를 위해 우리는 계층구조 관련 4가지의 키워드를 활용하여 단계적으로 구현을 해볼 것이다.

POINT2

START WITH

계층구조의 최상위 항목, root node를 정의할 수 있는 COLUMN에 대해 정의한다.

이 경우, 각 항목들의 식별해주는 일련번호 COLUMN을 계층구조를 정의할 수 있는 항목으로 일단 정한다.

이후 가장 최상위 항목을 정의하는데, 이 경우 상위번호가 NULL인 물건이 가장 최상위가 될 것이므로 "상위번호 = NULL"을 최상위 항목에 대한 조건으로 설정한다.

SELECT 일련번호, 상위번호, 분류
FROM TABLE(=table 이름)
START WITH 상위부서번호 IS NULL;

PRIOR

각 data를 계층구조화, 즉 부모항목과 자식항목을 서로 이어주는 중요한 키워드이다.

다시 말해, 계층구조 형성을 위해 어떠한 COLUMN이 해당 항목의 상위(부모)항목인지 정의하여 주는데, 해당 COLUMN의 상위번호와 부모 COLUMN의 일련번호가 일치하면 되므로 이를 PRIOR 조건에 넣어준다.

이때 PRIOR A = B로 표현할 수 있고 A COLUMN이 자식항목,
B COLUMN이 부모항목이 된다.

CONNECT BY

위 PRIOR 키워드를 CONNECT BY 키워드와 함께 사용하여 최종 Query 구현한다.

SELECT 일련번호, 상위번호, 분류
FROM TABLE(=table 이름)
START WITH 부서번호 IS NULL
CONNECT BY PRIOR 일련번호 = 상위번호;

LEVEL

현재 계층이 몇번째 계층인가를 나타내주는 키워드이자. 계층구조 Query에서만 사용할 수 있는 COLUMN이다.

SELECT 일련번호, 상위번호, 분류, 계층구조, LEVEL
FROM TABLE(=table 이름)
START WITH 부서번호 IS NULL
CONNECT BY PRIOR 일련번호 = 상위번호;

참고로 이 LEVEL를 oracle dummy table인 dual과 함께 이용한다면, 최근 n개월 등 동적 변수에 따른 Dynamic Query 구현이 가능해진다.