[ORACLE/오라클] 계층형 쿼리를 이용한 트리구조 데이터 정렬하기

어떤 테이블에서 아래와 같이 컬럼으로 부모 자식 간의 관계가 정의되어 있지 않고,

한 컬럼에 부모 데이터, 자식 데이터가 다모두 존재하면서

이 컬럼에서 부모 데이터와 자식 데이터를 구분할 수 있는 패턴이 존재하는 경우,

계층형 쿼리를 이용해서 해당 데이터의 계층 구조를 컬럼으로 분산할 수 있다.

예를 들어 아래와 같은 데이터가 있다고 가정할 때,

코드	코드명
A	자동차
A0	세단
A01	K8
A1	SUV
A11	쏘렌토

 

자동차라는 ROOT 데이터 아래 LEAF노드로 A0, A01, A1, A11로 나뉘는 걸 파악할 수 있다.

부모/자식 코드로 혼재되어 있는 데이터를 컬럼별로 나누는 작업을 진행한다.

SELECT 코드 AS 코드
     , 코드명 AS 코드명
     , SUBSTR(코드,1,LENGTH(코드)-1) AS 부모코드
  FROM 테이블;

코드	코드명	부모코드
A	자동차	NULL
A0	세단	A
A01	K8	A0
A1	SUV	A
A11	쏘렌토	A1

 

코드와 부모코드가 나누어 졌으니, 해당 쿼리를 인라인 뷰로

계층형 쿼리를 작성하여 "부모코드" 컬럼과 "코드"의 관계를 완성한다.

SELECT A.부모코드
     , A.코드 
     , A.코드명
     , LEVEL AS 레벨
  FROM (
       SELECT 코드 AS 코드
            , 코드명 AS 코드명
            , SUBSTR(코드,1,LENGTH(코드)-1) AS 부모코드
         FROM 테이블
       ) A
 WHERE 1=1
 START WITH A.부모코드 IS NULL
CONNECT BY PRIOR A.코드 = A.부모코드

부모코드	코드	코드명	레벨
NULL	A	자동차	1
A	A0	세단	2
A0	A01	K8	3
A	A1	SUV	2
A1	A11	쏘렌토	3

 

가장 하위 레벨로 데이터를 걸러내고

"SYS_CONNECT_BY_PATH"와 정규표현식으로 정리하면 

아래의 표와 같이 결과를 정리할 수 있다.

 

SELECT LEVEL AS 레벨
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(A.코드,','),'[^,]+',1,1) AS 대분류
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(A.코드,','),'[^,]+',1,2) AS 중분류
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(A.코드,','),'[^,]+',1,3) AS 소분류
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(A.코드명,','),'[^,]+',1,1) AS 대분류명
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(A.코드명,','),'[^,]+',1,2) AS 중분류명
     , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(A.코드명,','),'[^,]+',1,3) AS 소분류명
  FROM (
       SELECT 코드 AS 코드
            , 코드명 AS 코드명
            , SUBSTR(코드,1,LENGTH(코드)-1) AS 부모코드
         FROM 테이블
       ) A
 WHERE 1=1
   AND LEVEL = 3
 START WITH A.부모코드 IS NULL
CONNECT BY PRIOR A.코드 = A.부모코드

레벨	대분류	중분류	소분류	대분류명	소분류명	소분류명
3	A	A0	A01	자동차	세단	K8
3	A	A1	A11	자동차	SUV	쏘렌토