[SQL] Programmers 59413번 입양 시각 구하기(2) - WITH RECURSIVE, HOUR, COUNT

📌 문제 소개

문제 바로가기 - [Programmers 59413번]

 

ANIMAL_OUTS 테이블에서 입양 시간(DATETIME)을 기준으로 0시부터 23시까지
각 시간대별 입양 건수를 집계하기

 

요구사항 요약:

• 0시부터 23시까지, 시간대별 입양 건수 조회
• 입양이 없는 시간대도 0건으로 출력
• 시간(HOUR) 오름차순으로 정렬

---

🧾 테이블 구조

🔹 ANIMAL_OUTS 테이블

컬럼명	타입	설명
ANIMAL_ID	VARCHAR	동물 ID
ANIMAL_TYPE	VARCHAR	동물 종류
DATETIME	DATETIME	입양일 (날짜 + 시간 포함)
NAME	VARCHAR	이름
SEX_UPON_OUTCOME	VARCHAR	입양 당시 성별 및 중성화 여부

---

🔍 풀이 전략

1. 0~23시까지 시간대를 먼저 생성해야 함
   → 모든 시간대가 빠짐없이 나타나도록 하기 위해
2. 생성한 시간대와 ANIMAL_OUTS 테이블을 LEFT JOIN
   → 입양이 없는 시간대도 COUNT = 0으로 출력되게 하기 위함
3. DATETIME에서 HOUR을 추출해 시간대별로 그룹핑

---

🛠️ 사용한 SQL 문법

문법	설명
WITH RECURSIVE	0~23까지 시간 목록 생성 (MySQL 기준)
HOUR(DATETIME)	DATETIME에서 시간(HOUR)만 추출
LEFT JOIN	모든 시간대 표시 (입양 없는 시간도 포함)
GROUP BY	시간대별 집계
ORDER BY	시간 오름차순 정렬

---

✅  MySQL 풀이

-- 0~23시간대 생성 (MySQL에서는 RECURSIVE CTE 사용)
WITH RECURSIVE hours AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM hours WHERE HOUR < 23
)

-- 각 시간대별 입양 수 카운트
SELECT 
    h.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;

---

🧪 결과 예시

HOUR COUNT

HOUR	COUNT
0	0
1	0
2	0
...	...
11	13
13	14
18	16
23	0

---

💡 핵심 정리

• RECURSIVE CTE를 이용해 0~23까지 시간대 생성
• LEFT JOIN을 활용해 입양이 없는 시간대도 0건으로 표시
• HOUR() 함수로 DATETIME에서 시간(HOUR)만 추출
• 시간대 누락 없이 전체 시계열 분석 가능

---

🔄  응용

• 분 단위(MINUTE()), 요일별(WEEKDAY()), 날짜별(DATE()) 통계도 가능
• 시간대별 비율 분석, 히트맵 시각화용 데이터로 활용 가능

---

✅ 마무리

• 시계열 데이터 분석방법을 배울 수 있었다.
• 누락된 시간대 추가를 위한 재귀 CTE 및 GROUP BY + JOIN 활용을 배울 수 있었음!