[오라클] 계층구조 쿼리

 

SELECT JOB, EMPNO, ENAME, MGR FROM EMP;

계층적인 구조를 파악하기 힘듦

• 데이터의 계층적인 구조를 표현하는 데 사용함
• MRG은 상급자의 EMPNO임
• PRESIDENT KING은 사장이라 상급자가 없음

SELECT LEVEL,
       LPAD(' ', (LEVEL-1)*3) || JOB AS JOB,
       EMPNO,
       ENAME,
       MGR
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR;

LPAD를 이용해 시각적으로 계층 표현

---

START WITH

• 계층구조가 어떤 행에서 시작할지 지정함
• 뒤에 조건식을 요구함
• WHERE절 처럼 자유롭게 서브쿼리까지 가능하나 조건에 맞는 행이 2개 넘으면 이상해짐
• 유지보수차원에서 JOB = "PRESIDENT"보다 MGR IS NULL이 더 좋긴 함
• JOB 데이터 값의 변화와 상관없이 상급자가 없는 JOB이 최상급자이기 때문

SELECT LEVEL,
       LPAD(' ', (LEVEL-1)*3) || JOB AS JOB,
       EMPNO,
       ENAME,
       MGR
FROM EMP
START WITH JOB = 'PRESIDENT' OR JOB = 'ANALYST'		// 조건에 맞는 행 추가
CONNECT BY PRIOR EMPNO = MGR;

ANALYST로 시작하는 계층이 중복되어 나옴

---

CONNECT BY

• 계층구조의 연결 조건을 지정
• PRIOR 연산자와 함께 상위행과 하위행을 연결
• 비교 연산자를 기준으로 어느 쪽에 PRIOR을 넣느냐에 따라 
• PRIOR 연산자는 SELECT절이나 다른 곳에서도 사용 가능

SELECT LEVEL,
       LPAD(' ', (LEVEL-1)*3) || JOB AS JOB,
       EMPNO,
       ENAME,
       MGR
FROM EMP
START WITH JOB = 'CLERK'		// 시작점 변경
CONNECT BY EMPNO = PRIOR MGR;	// PRIOR 위치 변경 => 이전행의 상급자를 하위행으로 삼겠다는 뜻

조건에 맞는 행이 4개(CLERK 수)라서 4개의 계층구조

SELECT LEVEL,
       LPAD(' ', (LEVEL-1)*3) || JOB AS JOB,
       EMPNO,
       PRIOR ENAME "직속상사명"
FROM EMP
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR;

SELECT절에 PRIOR 연산자 활용해 직속상사명 표시

---

LEVEL

• 모든 쿼리 내에서 사용가능한 가상의 열
• 트리 내 어떤 단계(LEVEL)에 있는지 나타냄
• 계층적인 구조가 아니라면 모든 LEVEL은 0
• 계층이 깊을 경우 LEVEL <= 5 등과 같은 조건으로 원하는 내용만 볼 수 있음
• 위와 같은 조건은 WHERE 절이 아닌 CONNNECT BY 절에 넣어야 효과적
• 결과는 같지만 코드의 실행 순서는 START WITH->CONNECT BY->WHERE 순임
• 따라서 WHERE절에 조건을 주면 전체 트리를 구성한 후에 조건을 적용해 비효율적

SELECT LEVEL,
       LEVEL-1 AS "RANGE(11)"
FROM DUAL
CONNECT BY LEVEL<=11;

LEVEL의 활용

이걸 이용하면 간단히 풀 수 있는 문제

https://programmers.co.kr/learn/courses/30/lessons/59413

코딩테스트 연습 - 입양 시각 구하기(2)

---

https://vaert.tistory.com/166

http://www.gurubee.net/lecture/1300