본문 바로가기

Database

[Oracle] 계층형 쿼리 Hierarchiacal Query ( + LISTAGG() 함수)

계층형 쿼리 Hierarchiacal Query

 : 오라클에서만 지원

 

  • 순방향 전개 : 최상위 노드에서 하위 노드로 이동
  • 역방향 전개 : 하위 노드에서 자신의 부모노드만 방문

Query 작성 예시

  • -- 순방향 전개 (prior 자식 = 부모)
    SELECT LPAD(' ', 2*(LEVEL-1)) || node_name 
    FROM node_table  
    START WITH node_id = '100'
    CONNECT BY PRIOR node_id = parent_id;
  • -- 역방향 전개 (자식 = prior 부모)
    SELECT LPAD(' ', 2*(LEVEL-1)) || node_name  
    FROM node_table  
    START WITH node_id = '246'
    CONNECT BY node_id = PRIOR parent_id;

 

  1. Node : 원모양으로 표시된 항목. 각각의 품목이 하나의 노드가 되며 실제 테이블에서는 하나의 ROW에 대응된다.
  2. Parent : 부모노드라고도 한다. 트리구조에서 상위에 있는 노드를 말한다.
  3. Child : 자식노드라고도 한다. 위 그림에서 모니터는 컴퓨터의 자식노드이며, 컴퓨터는 모니터와 본체의 부모 노드라고 한다
  4. Leaf : 리프노드라고 한다. 더 이상 하위에 연결된 노드가 없는 항목을 말한다. 즉 자식 노드가 없는 노드이다.
  5. Root : 계층형 트리구조에서 최상위에 있는 노드.
  6. Level : 트리구조에서의 각각의 계층 루트에 해당되는 컴퓨터가 1레벨이 되며 그 하위에 있는 모니터, 본체 등이 2레벨이다.

 

 * ORA-01436: CONNECT BY의 루프가 발생되었습니다 

 - 루프 발생 시 nocycle 과 함께 써서 어디 데이터에서 루프가 발생되는지 찾을 수 있다. 

Query 작성 예시

  SELECT LPAD(' ', 2*(LEVEL-1)) || node_name, CONNECT_BY_ISCYCLE
  FROM node_table
  START WITH node_id = '510' 
  CONNECT BY NOCYCLE node_id = PRIOR parent_id;

 

 

1. LEVEL : root데이터면 1, 그 하위 데이터이면2, leaf데이터까지 1씩 증가
2. CONNECT_BY_ISLEAF : 현재 행이 마지막 leaf노드인지 리턴 (1:리프노드, 0:리프노드X)
3. CONNECT_BY_ISCYCLE : 계층형 쿼리에서 해당하는 로우가 자식노드를 가지고 있는데 다시 부모느드 인지를 찾아주는 함수 (중복참조하여 자식노드가 있을 경우 1 , 그렇지 않을 경우 0을 반환)
4. CONNECT_BY_ROOT(컬럼) : 최상위 데이터의 컬럼 정보 조회
5. SYS_CONNECT_BY_PATH(컬럼,'구분자') : 계층 순회 경로 표현, 두번째 인자(경로 표현 구분자)

 

 

계층형 쿼리에서의 조건 처리 start with … connect by example: pruning branches

select
  lpad(' ', 2*level) || child
from
  prune_test
start with
  parent is null
connect by
  prior child = parent and
  parent not in (1, 71)          -- Exclude children below and 71

Query 출력 결과 

  1             -- 가지치기 됌
  6
    61
    63
    65
      653
    69
  7
    71       -- 가지치기 됌
    74
      744
      746
      748
            7481
            7483
            7487

 

 

계층경로 출력하는 방법

  1. SYS_CONNECT_BY_PATH() 사용
        select *
        from (
            select node_id, LTRIM(SYS_CONNECT_BY_PATH(nname,'>'),'>') path, parent_id, connect_by_iscycle
            from node_table
            start with node_id= '510' connect by node_id= prior parent_id
        );
        where parentid = '0';  
  2. LISTAGG() 사용
        select listagg(nname, '>') within group (order by level desc)
        from node_table
        start with node_id= '510' connect by node_id= prior parent_id;

Query 출력 결과 

리트리버>개>포유류>동물>생물

 

 

 

* LISTAGG() 집계함수

 : LISTAGG 함수는 문자열을 병합하여 일련의 문자열 요소를 하나의 문자열로 집계. 선택적으로 인접한 입력 문자열 사이에 삽입되는 구분자 문자열을 제공한다.

즉, 여러 row 데이터를 하나의 row로 문자열을 합쳐서 보여줄 수 있는 함수이다.

 

RANK, DENSE_RANK 함수들처럼 WITHIN GROUP을 사용하여 쓸 수 있다. 

 -> WITHIN GROUP ( ORDER BY 컬럼, 컬럼... [ ASC | DESC ] )

 

Query 작성 예시

   SELECT workdept, LISTAGG(lastname, ', ') WITHIN GROUP(ORDER BY lastname) AS employees

   FROM emp

   GROUP BY workdept

예상 출력 결과 : 부서별로 그룹화되고 알파벳순으로 표시되며 쉼표로 구분된 이름 목록을 생성

 

* ORA-01489: result of string concatenation is too long 
 -  "값"열을 최대 4000 자로 제한을 위반할 때 발생되는 에러

    -> ON OVERFLOW 옵션을 사용

SELECT LISTAGG(product_name, ',' on overflow truncate) WITHIN GROUP(ORDER BY product_name)
FROM products;