SQL) 재귀 쿼리로 반복문 만들기 / WITH RECURSIVE

≣ 목차

 

재귀 쿼리

• 반복적으로 데이터를 생성하는 작업이 필요할 때 사용된다.
• 파이썬의 for문과 비슷한 역할 수행

 

기본 구조

WITH RECURSIVE cte_name AS (
    -- 1. Anchor 부분: 초기 데이터 생성
    SELECT <columns>
    FROM <table>
    WHERE <condition>
    
    UNION ALL
    
    -- 2. Recursive 부분: 재귀적으로 처리할 쿼리
    SELECT <columns>
    FROM cte_name -- 자기 자신을 참조
    WHERE <condition>
)
SELECT * FROM cte_name;

 

1. Anchor 부분

• 재귀 쿼리의 시작점. 초기 데이터를 생성한다.

2. Recursive 부분

• Anchor에서 생성된 데이터를 기반으로 반복적으로 계산을 수행한다.
• 여기서 `cte_name`을 참조하여 자기 자신을 호출

3. 종료 조건

• `where`절로 반복을 멈출 조건을 지정한다.

4. 최종 select

• 재귀적으로 생성된 데이터를 최종적으로 출력한다.

 

활용 예시 1. 숫자 생성

• 숫자 생성 (0~N까지)

WITH RECURSIVE numbers AS (
    -- Anchor: 0부터 시작
    SELECT 0 AS num
    UNION ALL
    -- Recursive: num에 1씩 추가
    SELECT num + 1
    FROM numbers
    WHERE num < 10 -- 종료 조건 (num이 10보다 작을 때까지만 반복)
)
SELECT num
FROM numbers;

 

출력 결과

 

활용 예시 2. 시간을 24시간으로 표현한 테이블 생성

WITH RECURSIVE time AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1
    FROM time
    WHERE hour < 23
)

 

• SELECT 0 AS hour
  • 초기 값으로 hour = 0을 생성한다. (재귀의 시작점)
• UNION ALL
  • hour에 1을 더한 값을 계속 추가한다.
  • 예: 0 → 1 → 2 → ... → 23
• WHERE hour < 23
  • hour가 23이 되면 멈추도록 설정한다.

출력 결과

0~23시까지

 

 

예제) 입양 시각 구하기

코드카타 73번

 

문제:

0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 

이때 결과는 시간대 순으로 정렬해야 합니다.

WITH RECURSIVE time AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1
    FROM time
    WHERE hour < 23
)
SELECT t.hour,
       COUNT(animal_id) as count
FROM time t 
LEFT JOIN (
    SELECT *, HOUR(datetime) AS hour
    FROM animal_outs
) a
ON t.hour = a.hour
GROUP BY 1
ORDER BY 1;

• `WITH RECURSIVE`
  • `time`테이블에서 0~23시까지 `hour` 값을 생성.
• `LEFT JOIN`
  • `time.hour`와 `animal_outs`에서 `HOUR(datetime)`으로 추출한 `hour` 값을 결합.
  • 😱이때 실수한 것😱
    • - select date_format(datetime, '%H') as HOUR from animal_outs 으로 했더니 오류 남.
      🔎 왜?
      - `HOUR(datetime)` : 정수(int)값을 반환함. 0,1,2 ...
      - `DATE_FORMAT(datetime, %H)`: 문자열(varchar)값을 반환함. 00,01,02 ...
      👉 결론
      `JOIN`에서 데이터 형식이 맞지 않아서 매칭이 안 된거임.
• `COUNT`
  • 각 시간에 해당하는 데이터 개수를 계산

출력 결과