계층형 쿼리 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;

- Node : 원모양으로 표시된 항목. 각각의 품목이 하나의 노드가 되며 실제 테이블에서는 하나의 ROW에 대응된다.
- Parent : 부모노드라고도 한다. 트리구조에서 상위에 있는 노드를 말한다.
- Child : 자식노드라고도 한다. 위 그림에서 모니터는 컴퓨터의 자식노드이며, 컴퓨터는 모니터와 본체의 부모 노드라고 한다
- Leaf : 리프노드라고 한다. 더 이상 하위에 연결된 노드가 없는 항목을 말한다. 즉 자식 노드가 없는 노드이다.
- Root : 계층형 트리구조에서 최상위에 있는 노드.
- 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
계층경로 출력하는 방법
- 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'; - 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;
'Database' 카테고리의 다른 글
[oracle] 리눅스에서 sqlplus 접속하기 (0) | 2021.07.07 |
---|---|
[ORA-65096] 공통 사용자 또는 롤 이름이 부적합합니다. (0) | 2021.02.03 |