[LV.5] GROUP BY > 입양 시각 구하기(2) ⭐️

문제

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

프로그래머스

---

문제 설명

• 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려는 상황
• 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성
• 결과는 시간대 순으로 정렬

---

정답 코드

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

SELECT
    h.hour AS HOUR,
    COUNT(a.ANIMAL_ID) AS COUNT
FROM
    hours h
LEFT JOIN
    ANIMAL_OUTS a
    ON HOUR(a.DATETIME) = h.hour
GROUP BY
    h.hour
ORDER BY
    h.hour

---

코드 설명

# 0부터 23까지의 시간(h)을 생성하는 재귀 CTE
WITH RECURSIVE hours AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 FROM hours WHERE hour < 23
)

 

• 0 ~ 23까지의 24개 시간을 가진 임시 테이블을 생성하기 위함
  • 재귀적으로 hour 값을 1씩 증가시켜 23까지 생성

# 각 시간별 입양 횟수를 집계
SELECT
    h.hour AS HOUR,                        -- 시간 (0~23)
    COUNT(a.ANIMAL_ID) AS COUNT            -- 해당 시간의 입양 건수
FROM
    hours h
LEFT JOIN
    ANIMAL_OUTS a
    ON HOUR(a.DATETIME) = h.hour           -- DATETIME 컬럼에서 시간 추출해 매칭
GROUP BY
    h.hour
ORDER BY
    h.hour

• ANIMAL_OUTS 테이블에서 DATETIME 컬럼의 시간 부분만 뽑아서 조인
  • 예: 2025-07-18 14:32:10 → HOUR(DATETIME)는 14
• 왜 COUNT(*) 대신 COUNT(a.ANIMAL_ID)?
  • ANIMAL_ID는 NOT NULL이므로, 입양이 없으면 NULL → COUNT는 0
  • COUNT(*)도 작동하지만, LEFT JOIN 상황에서 NULL 행 포함될 수 있어 정확성에서 COUNT(a.컬럼)이 안전

---

참고) WITH RECURSIVE

• 재귀 CTE를 생성하는 구문으로, 쿼리가 자기 자신을 반복적으로 참조
• 반복적인 데이터 생성이나 계층적 관계 탐색에 사용됨
• 기본 구조

WITH RECURSIVE cte_name AS (
    -- 초기 쿼리 (Base case)
    SELECT ...
    UNION ALL
    -- 재귀 쿼리 (Recursive case)
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name

 

• WITH RECURSIVE: 재귀 CTE의 시작을 나타냄
• cte_name: CTE의 이름. CTE 내에서 정의된 데이터는 이 이름으로 참조할 수 있음
• UNION ALL: 기본 쿼리와 재귀 쿼리의 결과를 결합하는 데 사용됨. UNION을 사용하면 중복 제거가 발생하므로, UNION ALL을 사용하는 것이 효율적

 

---

첫 번째 시도 (실패)

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 0 AND 23
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

• 입양 기록이 없는 시간대는 결과에 포함되지 않음 → 모든 시간대 테이블 생성 후 LEFT JOIN