[SQL] - 재귀쿼리/계층형쿼리 Oracle vs PostgreSQL

서론

현재 Oracle 쿼리를 PostgreSQL로 변경하는 작업을 하고있다. 처음에는 Oracle과의 호환성이 좋다고 하여 무지성 복붙을 하고 쿼리를 날려봤더니 에러가 났다... 알고보니 몇 가지 다른 함수들이 있었다. 그 중 재귀쿼리에 대하여 정리를 해보고자 한다. 재귀쿼리가 쓰이는 곳이 꽤나 많아서 제대로 이해하고 넘어가도록 하자.

Oracle - START WITH ... CONNECT BY PRIOR

우선 테스트 할 테이블과 데이터를 넣어줬다.

CREATE TABLE DEP (
     DEP_CD NUMBER NOT NULL, -- 부서코드
     PARENT_CD NUMBER, -- 상위부서 코드
     DEPT_NAME VARCHAR2(100) NOT NULL, -- 부서이름
     PRIMARY KEY (DEP_CD)
);

INSERT INTO DEP VALUES ( 101, NULL, '총괄개발부');
INSERT INTO DEP VALUES ( 102, 101, '모바일개발센터');
INSERT INTO DEP VALUES ( 103, 101, '웹개발센터');
INSERT INTO DEP VALUES ( 104, 101, '시스템개발센터');

INSERT INTO DEP VALUES ( 105, 102, '쇼핑몰(모바일)');
INSERT INTO DEP VALUES ( 106, 103, '외주SI');
INSERT INTO DEP VALUES ( 107, 103, '쇼핑몰');
INSERT INTO DEP VALUES ( 108, 105, '전산지원팀');
INSERT INTO DEP VALUES ( 109, 106, '구축1팀');
INSERT INTO DEP VALUES ( 100, 106, '구축2팀');
INSERT INTO DEP VALUES ( 111, 104, 'ERP시스템');

그리고 Oracle의 계층형 쿼리 형식이다.

SELECT [컬럼...]
FROM [테이블]
WHERE [조건...]
START WITH [최상위 조건]
CONNECT BY [NOCYCLE] [PRIOR 계층형 구조 조건]

위 테이블 구조는 최상위 부서가 '총괄개발부 - 101'이고, 총괄개발부 밑으로는 부서번호-[102,103,104]가 있다. 이후 다른 부서들도 계층형 구조로 이루어져 있다.
이제 계층형 쿼리로 작성해보자.

SELECT DEPT_NAME, DEP_CD, PARENT_CD, LEVEL
FROM DEP 
START WITH PARENT_CD IS NULL --최상위 컬럼 조건
CONNECT BY PRIOR DEP_CD = PARENT_CD --계층형 구조 조건
ORDER BY LEVEL;

이제 구문을 하나씩 보도록 하겠다.

SELECT DEPT_NAME, DEP_CD, PARENT_CD, LEVEL
FROM DEP

SELECT 절에 컬럼들을 나열해 주었고, LEVEL이란 컬럼은 루트 계층 부터의 단계를 나타내는데 계층형 쿼리에서만 사용하는 모조 컬럼이다. 따라서 '총괄개발부'가 최상위 계층이기 때문에 LEVEL이 1인 것을 볼 수 있다. 그리고, '총괄개발부'밑의 계층인 부서번호-[102,103,104]의 LEVEL은 2인 것을 볼 수 있다.

START WITH PARENT_CD IS NULL

START WITH 에는 최상위 계층이 될 조건이다. PARENT_CD컬럼이 부모컬럼이기 때문에 모든 행의 계층구조를 보기 위해 '총괄개발부'를 기준으로 봐야한다. 따라서 '총괄개발부'의 PARENT_CD는 NULL 이기 때문에 PARENT_CD IS NULL 이라는 조건을 넣어준다.

CONNECT BY PRIOR DEP_CD = PARENT_CD

먼저 START WITH에서 조건에 맞는 최상위 행(위 예에서는 '총괄개발부')을 가져온 후 이 행의 DEP_CD를 PARENT_CD로 갖는 계층 데이터를 끝까지 가져온다.
만약 PRIOR 반대로 설정하게 된다면? - CONNECT BY PRIOR PARENT_CD = DEP_CD
START WITH에서 최상위 행을 구한 후 이 행의 PARENT_CD를 DEP_CD로 갖는 계층 데이터를 끝까지 가져온다. 하지만 이때 최상위 행인 '총괄개발부'의 PARENT_CD는 NULL이기 때문에 쿼리 결과는 최상위 행 하나만 출력되게 된다.

ORDER BY LEVEL

LEVEL을 기준으로 오름차순 정렬을 했다. 쿼리 결과에서 해당 행이 몇 번째 계층인지 명확하게 보기 위해서이다.

마치며

이번에는 간단하게 Oracle의 계층형 쿼리에 대해 정리해 보았다. 계층형 쿼리를 응요하여 사용하게 된다면 무궁무진하게 사용 할 수 있을 것 같다. 다음에는 PostreSQL 재귀쿼리를 정리해 보도록 하겠다.

참조

https://heavenlake.tistory.com/45
https://coding-factory.tistory.com/461