계층적 데이터 조회

https://school.programmers.co.kr/learn/courses/30/lessons/301650

📌 테이블 구조 (ECOLI_DATA)

Column Name	Type	Nullable	설명
ID	INTEGER	FALSE	개체의 고유 ID
PARENT_ID	INTEGER	TRUE	부모 개체의 ID (최초 개체는 NULL)
SIZE_OF_COLONY	INTEGER	FALSE	개체의 크기
DIFFERENTIATION_DATE	DATE	FALSE	분화된 날짜
GENOTYPE	INTEGER	FALSE	개체의 형질

---

1️⃣ Oracle – CONNECT BY 사용

SELECT ID
FROM ECOLI_DATA
START WITH PARENT_ID IS NULL
CONNECT BY PRIOR ID = PARENT_ID
WHERE LEVEL = 3
ORDER BY ID;

🔹 CONNECT BY 계층적 쿼리 설명

• START WITH PARENT_ID IS NULL: 1세대 개체부터 시작
• CONNECT BY PRIOR ID = PARENT_ID: 부모 ID를 기준으로 자식 개체를 재귀적으로 탐색
• LEVEL = 3: 3세대 개체만 필터링
• ORDER BY ID: ID 오름차순 정렬

Oracle은 CONNECT BY 를 사용하여 계층적 데이터를 처리할 수 있음.
LEVEL을 활용하면 특정 세대 데이터를 쉽게 조회 가능.

---

2️⃣ MySQL – RECURSIVE CTE 사용

WITH RECURSIVE ECOLI_CTE AS (
    -- 1세대 개체 찾기
    SELECT ID, PARENT_ID, 1 AS Generation
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 부모 개체를 기준으로 자식 개체를 찾음
    SELECT E.ID, E.PARENT_ID, C.Generation + 1
    FROM ECOLI_DATA E
    INNER JOIN ECOLI_CTE C ON E.PARENT_ID = C.ID
)
SELECT ID
FROM ECOLI_CTE
WHERE Generation = 3
ORDER BY ID;

🔹 RECURSIVE CTE 설명

• WITH RECURSIVE ECOLI_CTE AS (...) : 재귀적으로 데이터 조회
• Generation = 1 : 1세대 개체 선택 (PARENT_ID IS NULL)
• UNION ALL : 부모 개체를 기준으로 자식 개체를 찾고 세대를 증가시킴
• WHERE Generation = 3 : 3세대 개체만 필터링
• ORDER BY ID : ID 오름차순 정렬

MySQL에서는 WITH RECURSIVE 를 활용하여 계층적 데이터를 처리함.
MySQL 8.0 이상에서 지원

---

3️⃣ MS SQL Server – CTE 사용

WITH ECOLI_CTE AS (
    -- 1세대 개체 찾기
    SELECT ID, PARENT_ID, 1 AS Generation
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 부모 개체를 기준으로 자식 개체를 찾음
    SELECT E.ID, E.PARENT_ID, C.Generation + 1
    FROM ECOLI_DATA E
    INNER JOIN ECOLI_CTE C ON E.PARENT_ID = C.ID
)
SELECT ID
FROM ECOLI_CTE
WHERE Generation = 3
ORDER BY ID;

설명

• WITH ECOLI_CTE AS (...) : 공통 테이블 표현식(CTE) 정의
• SELECT ID, PARENT_ID, 1 AS Generation : 1세대 개체 선택
• UNION ALL : 부모 ID를 기준으로 재귀적으로 탐색
• WHERE Generation = 3 : 3세대 개체만 선택
• ORDER BY ID : ID 기준 오름차순 정렬

MS SQL Server도 RECURSIVE CTE 방식과 다르지 않게 계층적 데이터를 처리함.

---

🛠 SQL 계층적 쿼리 정리

DBMS	계층적 쿼리 방식	특징
Oracle	CONNECT BY	LEVEL을 활용하여 계층적 데이터를 탐색
MySQL	WITH RECURSIVE	MySQL 8.0 이상에서 사용 가능
MS SQL Server	WITH	CTE를 활용하여 계층적 데이터 조회

물론 이에 더하여 UNION ALL과 WHERE절을 잘 활용하여야 함.

한 줄 정리

• Oracle → CONNECT BY PRIOR
• MySQL / MS SQL Server → (RECURSIVE) CTE 사용