[Programmers SQL] WITH RECURSIVE문

WITH RECURSIVE문 (재귀 쿼리)

✅ WITH RECURSIVE 쿼리문을 작성하고 내부에 UNION을 통해 재귀를 구성하는 것이 포인트

 

 

Ex. n 컬럼의 값이 3보다 작은 값 찾기

WITH RECURSIVE cte_count 
AS ( 
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    SELECT 1 AS n
    UNION ALL
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    SELECT n + 1 AS num 
    FROM cte_count
    WHERE n < 3 
)

SELECT * FROM cte_count;

 

• 반드시 UNION을 사용해야 함
• 반드시 Non-Recursive도 최소한 1개 요구됨

 

 

---

[ SQL 문제 ] SELECT > 특성 세대의 대장균 찾기

 

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

프로그래머스

 

 

Q.
3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.

 

재귀 쿼리를 몰랐던 때,

SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID
                    FROM ECOLI_DATA
                    WHERE PARENT_ID IN (SELECT ID
                                        FROM ECOLI_DATA
                                        WHERE PARENT_ID IS NULL))
ORDER BY 1

 

• 문제는 3세대였지만, 5, 8세대가 되면 위 코드는 곤란함

 

 

재귀 쿼리를 적용하면,

WITH RECURSIVE cte AS (
    -- 1세대 (PARENT_ID가 NULL인 개체)
    SELECT ID, PARENT_ID, 1 AS generation
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 재귀적으로 자식 개체를 찾아 세대 계산
    SELECT e.ID, e.PARENT_ID, cte.generation + 1 AS generation
    FROM ECOLI_DATA e
    JOIN cte ON e.PARENT_ID = cte.ID
)
-- 3세대의 ID만 선택
SELECT ID
FROM cte
WHERE generation = 3
ORDER BY ID;

 

 

---

관련 문제 연습

[ SQL 문제 ] GROUP BY > 입양 시각 구하기(2)

 

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

프로그래머스

 

Q.
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

 

기존에 하던 GROUP BY 방식은 값이 없는 경우에 출력을 하지 않음

 

 

 

재귀 쿼리를 적용하면,

WITH RECURSIVE cte AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n+1 FROM cte WHERE n < 23)

SELECT n 'HOUR', 0
FROM cte
WHERE n NOT IN (SELECT HOUR(DATETIME) HOUR
    FROM ANIMAL_OUTS
    GROUP BY HOUR)
UNION ALL
SELECT HOUR(DATETIME) HOUR, COUNT(ANIMAL_ID) COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
ORDER BY HOUR

 

 

 

추가적인 학습

• MySQL에서의 차집합 방법

1) LEFT JOIN & IS NULL 활용

2) NOT IN

3) NOT EXISTS