[ SQL ] SQL 트리형 구조 테이블 ( WITH CTE ··· ) 을 활용해보자

 

 

안녕하세요 !   😃

 

오늘은 트리형 구조를 가진 테이블에 대해서 공부 해볼 건데요.

 

기본적인 함수들은 한번 다룬적이 있으니 기본 함수 내용은 아래 글을 참조해주시면 될 것 같습니다.

 

[ORACLE] 오라클 SQL 함수 정리

 

---

 

트리형 구조를 가진 테이블은 주로 어디에 쓰일까요?

 

트리 구조로 나타날 수 있는 데이터들을 관리할 때 쓸 수 있습니다.

 

예를 들면,

 

	서울시	성남시
구	강서구, 송파구, 강남구, 관악구 ...	수정구, 분당구 ...
동	화곡동, 마곡동, 개화동	분당동, 수내 1동, ...

 

위와 같은 데이터를 서울시, 성남시를 상위 노드로 본다면 구가 하위 노드, 구의 하위 노드는 동으로 볼 수 있겠죠.

 

region이라는 테이블을 생성한다고 봤을 때, 다음과 같이 넣을 수 있겠습니다.

 

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 1, '서울시', '');

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 2, '성남시', '');

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 11, '강서구', 1);

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 12, '송파구', 1);

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 13, '수정구', 2);

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 14, '분당구', 2);

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 101, '화곡동', 11);

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 102, '마곡동', 11);

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 103, '분당동', 14);

INSERT INTO REGION ( regionNo, regionNm, hirnkRegionNo ) VALUES( 104, '수내 1동', 14);

 

테이블 안에 지역 번호, 지역 이름, 상위 지역 번호가 있다고 하면 해당 데이터를 저장할 때도, 불러올때도 쉽게 이해할 수 있습니다.

 

위와 같은 트리 구조의 테이블이 있을 때 최하위인 '동' 데이터들을 가져오는 방법은 다음과 같습니다.

 

SELECT C.regionNo, C.regionNm 
FROM REGION A
    LEFT OUTER JOIN REGION B ON A.regionNo = B.hirnkRegionNo
    LEFT OUTER JOIN REGION C ON B.regionNo = C.hirnkRegionNo
WHERE C.hirnkRegionNo IS NOT NULL;

 

 

쿼리를 실행시켰을 때 얻을 수 있는 데이터는 다음과 같습니다.

 

 

 

트리 구조의 테이블을 다루는 것에 익숙해져봅시다 !

 

이렇게 계층 구조를 가진 테이블을 다룰 때, 노드의 깊이가 10 이상이 되면 어떻게 조회하면 될까요?

 

위에 작성한 쿼리처럼  LEFT OUTER JOIN을 여러 문장에 걸쳐 쓰는 게 어려워질 수 있는데,

 

트리의 노드 깊이가 깊어지면 RECURSIVE를 사용하면 간결하게 작성할 수 있어서 반복 작업을 피할 수 있습니다 !!

 

RECURSIVE는 WITH 를 사용하여 작성해주게되는데.. 지금은 노드 깊이가 3이지만 쿼리문을 작성해볼까요?

 

WITH CTE (regionNo, regionNm, hirnkRegionNo, depth)
AS(
    SELECT A.regionNo, A.regionNm, A.hirnkRegionNo, 1 depth
    FROM REGION A
    UNION ALL
    SELECT A.regionNo, A.regionNm, A.hirnkRegionNo, B.depth + 1
    FROM REGION A
    INNER JOIN CTE B ON B.regionNo = A.hirnkRegionNo
)
SELECT * FROM CTE ORDER BY depth;

 

 

위 쿼리문을 실행하면 가장 부모 노드인 데이터들이 depth 1 로 표현되고 가장 하위 노드인 데이터들은 depth 3으로 표현됩니다.

 

출력된 데이터를 보면

 

 

 

다음과 같으며 그럼 가장 하위 노드를 출력하려면 어떻게 해야할까요 ?

 

작성된 ORDER BY 대신 WHERE 절을 써서 depth = 3 인 데이터를 가져오면 되겠습니다.

 

자, 이렇게 오늘은 트리 형 구조를 가지는 테이블에 대해서 알아보았습니다 !

 

다음 글에서도 유용하고 유익한 정보를 가져오도록 하겠습니다.

 

 

 

감사합니다 !