Postgre Sql 계층형 쿼리

▶ Postgre에서는 일반적으로 쓰이는 start with ~ connect by ~ 구문을 사용 하지 못하기 때문에
RECURSIVE[재귀쿼리]라는 함수를 사용합니다. 단, WITH 절과 같이 사용해야합니다.

1. level이 존재 하는 경우

   WITH RECURSIVE department AS (
                                                           --START_QUERY : 재귀의 시작점
                                                           SELECT dpt_cd, dpt_nm, up_dpt_cd, dpt_lvl, COALESCE(to_char(cancel_dt,'YYYY'), '9999') AS expire_ymd, dpt_cd AS top_dpt_cd, dpt_nm AS top_dpt_nm
                                                            FROM dept
                                                           WHERE dpt_lvl = 40  -- 특정 레벨에서 시작
   
                                              UNION ALL
   
                                                    --REPEAT_QUERY : 상위 부서를 기준으로 하위 부서들을 반복적으로 조회
                                                    SELECT d.dpt_cd, d.dpt_nm, d.up_dpt_cd, d.dpt_lvl, COALESCE(to_char(d.cancel_dt,'YYYY'), '9999') AS expire_ymd, dp.top_dpt_cd, dp.top_dpt_nm
                                                     FROM dept d
                                             INNER JOIN department dp ON d.up_dpt_cd = dp.dpt_cd
                                                   WHERE d.dpt_lvl > dp.dpt_lvl -- 상위 부서가 존재하는 하위 부서들만 선택
                                                )
               --VIEW_QUERY
               SELECT a.*
                FROM department a
           ORDER BY a.dpt_lvl

2. level이 존재하지 않는 경우

   WITH RECURSIVE department AS (
                                                           --START_QUERY : 재귀의 시작점
                                                           SELECT dpt_cd, dpt_nm, up_dpt_cd, COALESCE(to_char(cancel_dt,'YYYY'), '9999') AS expire_ymd, dpt_cd AS top_dpt_cd, dpt_nm AS top_dpt_nm
                                                            FROM dept
                                                           WHERE dpt_cd = '0' -- 또는 WHERE up_dpt_cd IS NULL로 상위 부서가 없는 부서부터 시작
   
                                             UNION ALL
   
                                                   --REPEAT_QUERY : 상위 부서를 기준으로 하위 부서들을 반복적으로 조회
                                                   SELECT d.dpt_cd, d.dpt_nm, d.up_dpt_cd, COALESCE(to_char(d.cancel_dt,'YYYY'), '9999') AS expire_ymd, dp.top_dpt_cd, dp.top_dpt_nm
                                                    FROM dept d
                                             INNER JOIN department dp ON d.up_dpt_cd = dp.dpt_cd
                                                )
               --VIEW_QUERY
               SELECT a.*
                FROM department a
           ORDER BY a.up_dpt_cd

재귀 쿼리의 동작 방식:
기준 데이터: START_QUERY 에서 정의된 조건에 맞는 첫 번째 데이터를 기반으로 시작합니다.

상위-하위 관계 조회: REPEAT_QUERY 가 실행되면, INNER JOIN을 통해 상위 부서에 속하는 하위 부서들을 찾고 이를 department 라는 임시 뷰에 추가합니다.

반복: 재귀적으로 REPEAT_QUERY 가 실행되어 계속해서 하위 부서를 찾아가며 쿼리가 확장됩니다.

최종 결과: 모든 하위 부서를 포함한 최종 결과는 VIEW_QUERY에서 출력됩니다.

a. --START_QUERY 영역에 해당 부분을 실행하여 '조직도'의 데이터를 가지고 온다.

b. 기준이 되는 데이터를 department 라는 임시뷰에 저장한다.
c. --REPEAT_QUERY 영역을 실행하여 --START_QUERY 영역과 조인하여 상위부서로 갖는 부서들을 찾는다.

d. 해당 데이터도 department라는 임시뷰에 저장한다.

e. 추가된 데이터를 대상으로 상위부서로 갖는 부서들을 찾는다.

f. 지속 반복하여 모든 하위 부서를 찾는다.

▶주의 할 점◀
가장 주의해야 할 점은 무한루프 입니다.
데이터가 알맞게 들어가 문제없이 재귀쿼리를 수행하면 큰 문제는 없지만, 결국 사람이 만들어 넣기 때문에 오류가 날 수 있습니다.

무한루프를 방지하기 위해서는
--REPEAT_QUERY 영역에 조인 조건 또는 where 절을 사용하여 방어로직을 넣어주는 방법이 있습니다.

예를 들어, 부서 간 순환 구조가 있을 경우, REPEAT_QUERY에서 이미 처리된 부서들을 필터링하는 방식으로 무한루프를 방지할 수 있습니다.
WHERE d.dpt_cd NOT IN (SELECT dpt_cd FROM department)