SQL 계층형 쿼리 사용법

 

안녕하세요

 

부서별 직원들 DB를 작성하거나 대중소카테고리 정보를 SQL로 표현할때 필요한 쿼리인데요.

오늘은 SQL 계층형 쿼리 사용법을 알려드리겠습니다.

 

계층형 질의는 다음과 같이 부서나 카테고리 종속된 값을 표현할 때 쓰입니다.

즉, 계층형 구조를 나타낼 때 필요한 기능입니다.

 

이는 수직적 관계를 맺고 있는 행들의 계층형 정보를 조회할 수 있는 특징이 있습니다.

 

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해

계층형 질의(Hierarchical Query)를 사용합니다.

 

level1 부모노드 상위 카테고리

level2 자식노드 중위 카테고리

level3 리프노드 하위 카테고리

 

 

 

 

표로 나타낸다면 다음과 같습니다.

 

 

대표사진 삭제

사진 설명을 입력하세요.

 

 

 

예제코드

create table tree(
 tree_name varchar2(100) CONSTRAINT PK_TREE PRIMARY KEY ,
 tree_lvl varchar2(100),
 tree_h_name varchar2(100),
 reg_day date default sysdate
);

insert into tree(tree_name,tree_lvl,tree_h_name) values ('음료','1',null);
insert into tree(tree_name,tree_lvl,tree_h_name) values ('커피','2','음료');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('맥주','2','음료');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('아메리카노','3','커피');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('카페라떼','3','커피');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('카푸치노','3','커피');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('맥스','3','맥주');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('카스','3','맥주');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('테라','3','맥주');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('빵','1',null);
insert into tree(tree_name,tree_lvl,tree_h_name) values ('케익','2','빵');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('고구마케익','3','케익');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('치즈케익','3','케익');
insert into tree(tree_name,tree_lvl,tree_h_name) values ('생크림케익','3','케익');

commit;

 

실행구문

여기서 주요 3개 구문이 있는데요.

 

1)START WITH : 트리 구조의 최상위 행 지정

어떤 계층부터 시작할 지를 정해주는 구문(부모,자식 모두 쓸 수 있음)

예를들어 START WITH tree_h_name is null 이라고 표기하면 최상단부터 시

 

2)CONNECT BY PRIOR : 부모, 자식의 관계를 지정합니다

여기서 prior가 왼쪽에 붙으면 하향

 

오른쪽에 붙으면 상향

 

예제

CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼 : 부모 → 자식 순방향 전개

CONNECT BY PRIOR 부모 컬럼 = 자식 컬럼 : 자식 → 부모 역방향 전개

 

3)ORDER SIBLINGS : 가,나,다 순으로 계층형에 맞게 ORDER BY를 정렬해줍니다.

 

예제 코드

--------------- 하위 트리
SELECT  LPAD(' ',2*(LEVEL-1)) || tree_name, tree_lvl, tree_h_name,level,
connect_by_root tree_name,
connect_by_isleaf is_leaf,
substr(sys_connect_by_path ( tree_name, ' > '),4) chart
FROM tree
START WITH tree_h_name is null
CONNECT BY PRIOR tree_name = tree_h_name    
ORDER SIBLINGS by tree_name
;

--------------- 상위 트리 
select listagg(tree_name,' > ') within group (order by level_t desc)
from (
SELECT  tree_name, tree_h_name, level as level_t
FROM tree
START WITH tree_name ='카스'
CONNECT BY tree_name =  PRIOR tree_h_name    
)
;

-------------- 상위 계층 구조
-------------------------
SELECT --tree_name, level
listagg(tree_name,' > ') within group (order by level desc )
FROM tree
START WITH tree_name ='카스'
CONNECT BY tree_name =  PRIOR tree_h_name   
ORDER SIBLINGS by tree_name 
;


-----------------------NOCYCLE 
SELECT  LPAD(' ',2*(LEVEL-1)) || tree_name, tree_lvl, tree_h_name,level,
connect_by_iscycle cbsc
FROM tree
--where level=2
START WITH tree_h_name is null
CONNECT BY NOCYCLE PRIOR tree_name = tree_h_name                    
ORDER SIBLINGS by tree_name
;

 

감사합니다.