🐸 계층형 질의란?

데이터베이스 계층형 질의(Hierarchical Query)는 데이터가 부모-자식 관계로 연결되어 있을 때, 그 계층 구조를 따라가며 조회하는 질의를 말한다

---

Oracle 계층형 쿼리 문법

(1) 기본 문법 구조

SELECT 컬럼명들, LEVEL, SYS_CONNECT_BY_PATH(...)
FROM 테이블명
START WITH <루트 조건>
CONNECT BY [NOCYCLE] PRIOR 부모컬럼 = 자식컬럼
[ORDER SIBLINGS BY 컬럼명];

(2) 문법 구성요소별 설명

구문	설명
STRAT WITH	계층 구조의 시작점(루트 노드) 지정
CONNECT BY	부모-자식 관계 정의, 오라클은 PRIOR 키워드로 방향을 정한다
PRIOR	부모행을 나타내는 키워드, 앞에 PRIOR이 붙으면 부모쪽을 나타냄
LEVEL	루트(1)부터 내려가며 계층 깊이를 나타냄
ORDER SIBLINGS BY	같은 부모를 가진 형제 노드끼리 정렬할 때 사용
NOCYCLE	순환 참조(부모 = 자식 오류)가 있어도 멈추지 않고 탐색을 계속하도록 허용함
CONNECT_BY_ISLEAF	해당 노드가 리프 노드(자식이 없는 노드)이면 1, 아니면 0 리턴
SYS_CONNECT_BY_PAYH	루트에서부터 현재 노드까지의 전체 경로 문자열을 표시

---

계층형 쿼리 예시와 해석

(1) 기본 계층형 조회

SELECT LEVEL, EMP_ID, EMP_NAME
FROM EMP
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID;

MANAGER_ID가 NULL인 행들을 시작으로 부모인 EMP_ID와 자식 MANAGER_ID가 같은 계층을 계속해서 내려가면서 EMP테이블을 조회한다

(2) 역방향(자식 -> 부모)

SELECT LEVEL, EMP_ID, EMP_NAME
FROM EMP
START WITH EMP_ID IS NULL
CONNECT BY EMP_ID = PRIOR MANAGER_ID;

시작루트가 EMP_ID이고 PRIOR의 반대편이 EMP_ID이므로 자식에서 부모 방향으로 올라감

(3) 형제 노드 정렬

SELECT LEVEL, EMP_NAME
FROM EMP
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID
ORDER SIBLINGS BY EMP_NAME;

같은 부서 내에서 이름 순으로 정렬해서 쿼리 출력

(4) 경로 출력

SELECT SELECT LEVEL, EMP_NAME, SYS_CONNECT_BY_PATH(EMP_NAME, '->') PATH
FROM EMP
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID;

ID(1) -> ID(2) -> ID(3) -> .... 형태로 경로를 출력

(5) 리프 노드만 조회

SELECT EMP_NAME
FROM EMP
WHERE CONNECT_BY ISLEAF = 1
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID;

WHERE 조건절에 CONNECT_BY ISLEAF = 1로 리프노드만 필터해서 조회한다

---

실제 데이터 만들고 출력

(1) 맥도날드 테이블 생성

CREATE TABLE MCDONALDS (
    EMP_ID      NUMBER PRIMARY KEY,
    EMP_NAME    VARCHAR2(50),
    JOB_TITLE   VARCHAR2(30),
    MANAGER_ID  NUMBER NULL,
    BURGER_NAME VARCHAR2(50)
);

(2) 데이터 삽입

-- 1️⃣ 점장 (루트 노드)
INSERT INTO MCDONALDS VALUES (1, '강점장', '점장', NULL, NULL);

-- 2️⃣ 매니저
INSERT INTO MCDONALDS VALUES (2, '김매니저', '매니저', 1, NULL);
INSERT INTO MCDONALDS VALUES (3, '정매니저', '매니저', 1, NULL);

-- 3️⃣ 팀리더
INSERT INTO MCDONALDS VALUES (4, '함팀리더', '팀리더', 2, NULL);
INSERT INTO MCDONALDS VALUES (5, '유팀리더', '팀리더', 3, NULL);

-- 4️⃣ 그릴 크루 (햄버거 제조)
INSERT INTO MCDONALDS VALUES (6, '김크루', '그릴', 4, '빅맥');
INSERT INTO MCDONALDS VALUES (7, '이크루', '그릴', 4, '맥스파이시 상하이 버거');
INSERT INTO MCDONALDS VALUES (8, '윤크루', '그릴', 5, '1955 버거');
INSERT INTO MCDONALDS VALUES (9, '유크루', '그릴', 5, '더블 불고기 버거');

-- 5️⃣ 카운터 크루
INSERT INTO MCDONALDS VALUES (10, '조크루', '카운터', 4, NULL);
INSERT INTO MCDONALDS VALUES (11, '신크루', '카운터', 5, NULL);

COMMIT;

(3) 조직 구조 이해(계층)

강점장 (점장)
 ├─ 김매니저 (매니저)
 │   └─ 함팀리더 (팀리더)
 │       ├─ 김크루 (그릴) - 빅맥
 │       ├─ 이크루 (그릴) - 맥스파이시 상하이 버거
 │       └─ 조크루 (카운터)
 └─ 정매니저 (매니저)
     └─ 유팀리더 (팀리더)
         ├─ 윤크루 (그릴) - 1955 버거
         ├─ 유크루 (그릴) - 더블 불고기 버거
         └─ 신크루 (카운터)

(4) 계층형 질의

SELECT LEVEL,
       EMP_NAME,
       JOB_TITLE,
       BURGER_NAME,
       LPAD(' ', (LEVEL - 1) * 3) || EMP_NAME AS ORG_TREE,
       SYS_CONNECT_BY_PATH(EMP_NAME, ' → ') AS PATH
FROM MCDONALDS
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID
ORDER SIBLINGS BY EMP_NAME;

(5) 실제 출력

(6) 셀프조인으로 매니저 이름 확인

SELECT e.EMP_NAME AS EMPLOYEE,
       e.JOB_TITLE,
       m.EMP_NAME AS MANAGER,
       e.BURGER_NAME
FROM MCDONALDS e
LEFT JOIN MCDONALDS m
       ON e.MANAGER_ID = m.EMP_ID
ORDER BY e.EMP_ID;

조인 결과

---

윈도우 10에서 윈도우 11로 바꾸는 과정에 환경설정이 자동적으로 변해서 윈도우 11에의 Oracle 환경설정을 다시 해주는데 이거하느라 오늘 하루 순삭됐다
다음에는 pivot, unpivot 공부예정