Postgresql 계층형 쿼리 구현 방법

▶ 오라클

SELECT 컬럼명 FROM 테이블명 START WITH 계층구조 시작 조건(루트 노드 식별) CONNECT BY PRIOR 계층구조 상하위 조건(부모 자식 노드간의 관계)

SELECT A.CONTS_ID,               A.CONTS_NM,              A.UP_CONTS_ID,              A.MENU_ORD,              LEVEL   /* 계층구조에서 단계, 레벨을 나타내주는 함수 */ FROM CLT_MENU A WHERE A.MENU_INCL_YN = 'Y'              AND LEVEL IN (2,4) START WITH A.CONTS_ID = 'voc'   /* 계층구조의 시작조건을 주는 조건절 */  CONNECT BY PRIOR A.CONTS_ID = A.UP_CONTS_ID  /* 계층구조의 상,하위 간의 관계 조건 */ ORDER SIBLINGS BY A.MENU_ORD   /* 계층구조를 유지하면서 정렬해주는 구문 */

▶ PostgreSQL

WITH RECURSIVE  [view 명] (보여주고 싶은 컬럼) as (     부모쿼리 작성, 데이터의 시작조건을 구하는 쿼리     lUNION ALL      계층구조 작성, 하위 데이터를 찾아가기 위한 반복 쿼리,  ) view 쿼리

WITH RECURSIVE CODE_LIST(CONTS_ID, CONTS_NM, UP_CONTS_ID, MENU_ORD, DEPTH, PATH, CYCLE) as (              /* 계층구조의 시작조건 쿼리 */              SELECT A.CONTS_ID,                            A.CONTS_NM,                               A.UP_CONTS_ID,                            A.MENU_ORD,                            1,                            ARRAY[A.CONTS_ID::text],                            false              FROM CLT_MENU A              WHERE A.CONTS_ID = 'voc'                            AND A.MENU_INCL_YN = 'Y'              UNION ALL              /*하위 데이터를 찾아가기 위한 반복조건 쿼리*/              SELECT A.CONTS_ID,                            A.CONTS_NM,                               A.UP_CONTS_ID,                            A.MENU_ORD,                            B.DEPTH + 1,                            ARRAY_APPEND(B.PATH, A.CONTS_ID::text),                            A.CONTS_ID = any(B.PATH)              FROM CLT_MENU A, CODE_LIST B              WHERE A.UP_CONTS_ID = B.CONTS_ID                            AND A.MENU_INCL_YN = 'Y'                            AND NOT CYCLE ) /*View 쿼리*/ SELECT CONTS_ID,               CONTS_NM,               UP_CONTS_ID,              MENU_ORD,               DEPTH AS A_MENU_LEVEL,              PATH FROM CODE_LIST WHERE DEPTH IN (2,4) ORDER BY PATH

- CYCLE은 RECURSIVE를 통한 재귀 쿼리 수행 시 성능 상의 문제를 해결하기 위함
UNION ALL 다음의 반복조건 쿼리가 수행되면 CYCLE이 false이기 때문에 SELECT문이 수행 되고 검색된 자식 node의 ID 값이 배열(ARRAY[A.CONTS_ID::text])에 추가(ARRAY_APPEND(B.PATH, A.CONTS_ID::text)) 됨.
- ANY(B.PATH)는 PATH 배열에 자신의 ID값이 있는 지를 검사하여, 이미 찾은 값에 대해서는 더 이상 데이터 검색을 수행하지 않도록 함. 
- 배열에는 DataType이 int, text인 형태만 담을 수 있으므로 배열에 담을 varchar타입의 컬럼 뒤에 ::text 를 붙여 형태를 변환 해줌.

출처 : https://m.blog.naver.com/wiseyoun07/221135850258

# 오라클(Oracel)

SELECT 컬럼명

FROM 테이블명

START WITH 계층구조 시작 조건(루트 노드 식별)

CONNECT BY PRIOR 계층구조 상하위 조건(부모 자식 노드간의 관계)

 

1 2 3 4 5 6 7 8

SELECT grp_id,      (LPAD(' ', LEVEL) || grp_nm) as grp_nm,      top_grp_id, LEVEL /* 계층구조에서 단계, 레벨을 나타내주는 함수 */ FROM INFO_GROUP  START WITH top_grp_id IS NULL   /* 계층구조의 시작조건을 주는 조건절 */ CONNECT BY PRIOR grp_id = top_grp_id /* 계층구조의 상,하위 간의 관계 조건 */ ORDER SIBLINGS BY top_grp_id ASC; /* 계층구조를 유지하면서 정렬해주는 구문 */

 

# Postgresql

WITH RECURSIVE  [view 명] (보여주고 싶은 컬럼) as (
    부모쿼리 작성, 데이터의 시작조건을 구하는 쿼리
    union all 
    계층구조 작성, 하위 데이터를 찾아가기 위한 반복 쿼리, 
)
view 쿼리

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

WITH RECURSIVE search_group(grp_id, grp_nm, top_grp_id, level, grp_path, cycle) as (     /* 계층구조의 시작조건 쿼리 */     SELECT g.grp_id,              g.grp_nm,              g.top_grp_id,              0,              array[g.grp_id],              false     FROM "INFO_GROUP" g     WHERE g.top_grp_id IS NULL      UNION ALL     /*하위 데이터를 찾아가기위한 반복조건 쿼리*/     SELECT g.grp_id,              g.grp_nm,             g.top_grp_id,              level+1,              grp_path||g.grp_id,              g.grp_id = any(grp_path)     FROM "INFO_GROUP" g, search_group sg     WHERE g.top_grp_id = sg.grp_id      and NOT cycle ) /*View 쿼리*/ SELECT grp_id,          lpad(' ',level) || grp_nm as grp_nm,          top_grp_id,          level,          grp_path FROM search_group  ORDER BY grp_path

cycle은 RECURSIVE를 통한 재귀 쿼리 수행 시 성능 상의 문제를 해결하기 위함

UNION ALL 다음의 반복조건 쿼리가 수행되면 

cycle이 false이기 때문에 SELECT문이 수행이 되고 검색된 자식 node의 ID 값이 배열에 추가(array[g.grp_id])

any(grp_path) 는 grp_path배열에 자신의 ID값이 있는 지를 검사하여, 

이미 찾은 값에 대해서는 더 이상 데이터 검색을 수행하지 않도록 함.

# Postgresql 결과

 

 

출처 : https://m.blog.naver.com/elren/220779694207