[프로그래머스 sql] cte recursive

- 이전 문제 풀이 다른 사람들이 한 것을 보다가 재귀를 사용하신 분이 있었는데,

depth를 활용해서 하는 게 아직도 익숙하지 않아서 

그분의 답을 보며 아래에 정리하면서 공부하려고 함. 

 

앞이랑 똑같은  문제이다. 

 

특정 세대의 대장균 찾기

WITH RECURSIVE RE AS (
    -- (1) 초기 조건: 최상위 부모 노드 찾기 (Depth = 1)
    SELECT ID, 
           1 AS DEPTH
    FROM   ECOLI_DATA
    WHERE  PARENT_ID IS NULL

    UNION ALL

    -- (2) 재귀적으로 하위 노드를 탐색하여 Depth 증가
    SELECT A.ID, 
           B.DEPTH + 1 AS DEPTH
    FROM   ECOLI_DATA A, RE B
    WHERE  A.PARENT_ID = B.ID
)

---

 

- CTE: 쿼리 안에서 임시로 사용할 수 있는 가상 테이블 정의 방식

- 재귀 CTE: 자기 자신을 호출하는 CTE

 

- 재귀 cte 기본 구조

- 종료 조건: where절 or 특정 db에서 최대 재귀 횟수에 도달하면 자동 종료됨 (mysql은 1000회)

WITH RECURSIVE CTE_NAME AS (
    -- (1) Anchor Query (기본 쿼리): 최초 실행되는 쿼리
    SELECT ... FROM ... WHERE 조건

    UNION ALL

    -- (2) Recursive Query (재귀 쿼리): 자신을 다시 호출하는 쿼리
    SELECT ... FROM CTE_NAME, 다른_테이블 WHERE 연결 조건
)
SELECT * FROM CTE_NAME;

 

- 트리 구조에서 재귀 cte가 동작하는 방식

  : 부모- 자식 관계 저장. 

ID	PARENT_ID
1	null
2	1
3	1
4	2

 

 

- 1번에 조건 바로 가져오기 

WITH RECURSIVE RE AS (
    -- (1) Anchor Query: 최상위 부모(루트) 노드 찾기
    SELECT ID, 
           1 AS DEPTH
    FROM   ECOLI_DATA
    WHERE  PARENT_ID IS NULL

    UNION ALL

    -- (2) Recursive Query: 부모-자식 관계를 따라가며 DEPTH 증가
    SELECT A.ID, 
           B.DEPTH + 1 AS DEPTH
    FROM   ECOLI_DATA A, RE B
    WHERE  A.PARENT_ID = B.ID
)
SELECT ID 
FROM RE 
WHERE DEPTH = 3;

 

- 임시테이블로 저장한 후에 불러오기

 - 이렇게 하면 여러 번 사용이 가능함.

create temporary table temp as -- 임시 테이블로 만들어 줌
with recursive re as (
	select id, 
    	1 as dept
    from ecolia_data
    where parent_in is null
    
    
    union all
    
    select a.id, 
    	b.depth + 1 as depth
    from ecolia_data a, re b
    where a.parent_id = b.id
)
select * from re;

-- 여러번 사용 가능 
select * from temp;
select id from temp where depth = 3 order by id;