SQL RECURSIVE CTE

🟦 RECURSIVE CTE란?

• CTE (Common Table Expression)는 SQL 안에서 임시 결과 테이블을 만들 때 쓰인다.
• 여기에 RECURSIVE를 붙이면,
  스스로를 반복 호출하면서 데이터를 점점 확장할 수 있다.

👉 쉽게 말하면,
"시작값"을 정하고, "조건"을 주면서 계속 쌓아가는 방식이다.

보통 계층 구조(부모-자식)를 타고 내려가거나,
숫자, 날짜를 1씩 증가시키면서 생성할 때 사용한다.

---

🟦 RECURSIVE CTE 기본 구조

WITH RECURSIVE CTE이름 AS (
    -- 시작점
    SELECT ...

    UNION ALL

    -- 다음으로 확장하는 규칙
    SELECT ...
    FROM CTE이름
    WHERE 확장 조건
)
SELECT ...
FROM CTE이름;

• SELECT로 초기값을 설정하고
• UNION ALL을 이용해 자기 자신을 반복 호출한다.
• WHERE로 언제까지 반복할지 조건을 건다.

---

🟦 문제 설명

🐶 0시부터 23시까지 시간별로 입양이 몇 건 발생했는지 구하는 문제입니다.
시간대가 비어있어도(=입양이 0건이어도) 0부터 23까지 모두 출력해야 합니다.

📋 주어진 테이블:

• ANIMAL_OUTS
  • ANIMAL_ID : 동물 ID
  • DATETIME : 입양된 시간 (날짜+시간 정보 포함)

✅ 목표:

• 0~23시까지 시간을 무조건 다 보여주고
• 해당 시간대에 입양된 동물 수를 세기
• 결과를 HOUR 오름차순 정렬

---

🟦 RECURSIVE CTE를 활용한 해결 방법

1단계: 0부터 23까지 시간을 생성하기

WITH RECURSIVE HOUR_N (HOUR) AS (
    SELECT 0
    UNION ALL
    SELECT HOUR + 1
    FROM HOUR_N
    WHERE HOUR < 23
)

• HOUR_N이라는 이름의 CTE를 만든다.
• 0부터 시작해서, 1씩 더해가면서 23까지 숫자를 만든다.
• 즉, 0, 1, 2, ..., 23 이 자동으로 채워진다.

2단계: 입양 기록 테이블과 LEFT JOIN 하기

SELECT A.HOUR, COUNT(B.ANIMAL_ID) AS COUNT
FROM HOUR_N A
LEFT JOIN ANIMAL_OUTS B
ON A.HOUR = HOUR(B.DATETIME)
GROUP BY A.HOUR
ORDER BY A.HOUR;

• 만든 시간 테이블(HOUR_N)과
  ANIMAL_OUTS 테이블을 LEFT JOIN한다.
• 입양된 DATETIME의 시간을 HOUR() 함수로 추출해서 매칭한다.
• 없는 시간대는 0건으로 집계된다.
• 마지막에 GROUP BY로 시간별로 묶고 정렬한다.

---

🟦 전체 SQL 코드

WITH RECURSIVE HOUR_N (HOUR) AS (
    SELECT 0
    UNION ALL
    SELECT HOUR + 1
    FROM HOUR_N
    WHERE HOUR < 23
)
SELECT 
    A.HOUR, 
    COUNT(B.ANIMAL_ID) AS COUNT
FROM HOUR_N A
LEFT JOIN ANIMAL_OUTS B
    ON A.HOUR = HOUR(B.DATETIME)
GROUP BY A.HOUR
ORDER BY A.HOUR;

---

🟦 이 쿼리 흐름 요약

1. WITH RECURSIVE로 0 ~ 23까지 시간 생성한다.
2. 생성한 시간(HOUR_N)과 입양 기록(ANIMAL_OUTS)을 LEFT JOIN한다.
3. GROUP BY로 시간별 입양 건수를 센다.
4. 입양이 없는 시간대는 자동으로 0건 처리된다.

---

🟦 최종 정리

✅ RECURSIVE CTE는 반복적으로 데이터를 생성할 때 강력하다.
✅ 이번 문제처럼 시간, 날짜를 하나씩 늘리면서 다루는 경우에 특히 유용하다.
✅ RECURSIVE를 쓰면 0~23시 모든 경우를 빠짐없이 만들 수 있다.
✅ 비어 있는 시간대도 LEFT JOIN으로 0건 처리할 수 있다.