[정리노트] 데이터분석 IT자격증 SQLD 2024년 개정판 - 계층형 질의

 

 

 

 

[정리노트] 데이터분석 IT자격증 SQLD 2024년 개정판 - 계층형 질의

---

계층형 질의

• 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 토앻 행과 행 사이의 계층(depth)을 표현하는 기법
• PRIOR 의 위츠에 따라 연결하는 테이터가 달라짐
• 문법

SELECT 컬럼명
  FROM 테이블명
 START WITH 시작조건 -- 시작점을 지정하는 조건 전달
CONNECT BY [NOCYCLE] PRIOR 연결조건; -- 시작점 기준으로 연결 데이터를 찾아가는 조건

• START WITH: 데이터를 출력할 시작 지정 조건
• CONNECT BY PRIOR: 행을 이어나갈 조건
• NOCYCLE: 순환이 발생하면 무한 루프가 될 수 있기 떄문에 이를 방지

ex) DEPT2 테이블에 대해 각 부서의 레벨을 출력(최상위 부서가 1레벨)

SELECT D.*, LEVEL
  FROM DEPT2 D
 START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT;

→ 사장실의 DCODE를 넘겨 다시 각 행들의 PDEPT와 비교해야 하므로 먼저 정해져야 하는 값의 방향에 PRIOR 전달

ex) 계층형 질의 조건 전달

• CONNECT BY 절에 전달: 연결 조건이 추가되었으므로 모든 조건이 만족할 경우만 하위 레벨로 연결됨

SELECT D.*
  FROM DEPT2 D
 START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT AND AREA = '서울지사';

• WHERE 절에 전달: 모든 출력 결과 중 ‘서울지사’ 데이터만 출력됨

SELECT D.*, LEVEL
  FROM DEPT2 D
 WHERE AREA = '서울지사'
 START WITH DEPT IS NULL
CONNECT BY PRIOR DECODE = PDEPT;

---

계층형 질의 가상 컬럼 및 가상 함수

• 계층형 질의 가상 컬럼
  • LEVLE: 각 DEPTH를 표현 (시작점부터 1)
  • CONNECT_BY_ISLEAF: LEAF NODE(최하위노드) 여부(참:1, 거짓0)
• 계층형 질의 가상 함수
  • CONNECT_BY_ROOT 컬럼명: 루트노드의 해당하는 컬럼값
  • SYS_CONNECT_BY_PATH(컬럼, 구분자): 이어지는 경로 출력
  • ORDER SIBLINGS BY 컬럼: 같은 LEVEL이 ㄹ경우 정렬 수행
  • CONNECT_BY_ISCYCLE: 계층형 쿼리의 결과에서 순환이 발생했는지 여부

ex) 계층형 질의절 가상 컬럼 및 함수의 사용

SELECT D.*, LEVEL,
			 CONNECT_BY_ROOT DNAME,
			 SYS_DONNECT_BY_PATH(DNAME, '-')
  FROM DEPARTMENT D
 START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO = PDEPT
 ORDER SBLINGS BY DNAME;

→ ORDER SIBLINGS BY를 사용하여 같은 레벨일 경우 DNAME 오름차순으로 정렬,

2레벨은 자연과학부 < 컴퓨터공학부 순서대로 출력되며, 자연과학부 내 3레벨은 수학과 < 통계학과 < 화학공학과 순서대로 리턴되었다.

 

ex) NOCYCLE 옵션

테이블 <EMPLOYESS DATA>

SELECT EMPLOYEE_ID, NAME, LEVEL, CONNECT_BY_ISCYCLE AS IS_CYCLE
  FROM EMPLOYEES2
 START WITH EMPLOYEE_ID = 1000
CONNECT BY NOCYCLE PRIOR EMPLOYEE_ID = MANAGER_ID;