[프로그래머스] GROUP BY 입양 시각 구하기(2); WITH RECURSIVE CTE, HOUR
https://school.programmers.co.kr/learn/courses/30/lessons/59413
1. Problem
입양 기록이 담긴 ANIMAL_OUTS 테이블에서 0시부터 23시까지의 모든 시간대별 입양 건수를 조회해야 한다. 단순히 GROUP BY를 사용하면 입양 기록이 없는 시간대(예: 새벽 3시)는 결과에서 아예 누락되지만, 문제의 요구사항은 기록이 없어도 0으로 표기하는 것이다.
2. Solution: 가상 시간대 생성과 외부 조인
재귀 쿼리로 0~23까지의 숫자 테이블을 먼저 만들고, 이를 기준으로 실제 데이터를 붙여 유실을 방지한다.
3. Takeaway: 왜 이 방식이 '고급'인가? (객관적 분석)
- WITH RECURSIVE의 강력함: 데이터베이스에 저장된 실제 데이터가 부족할 때, 로직만으로 **'연속된 데이터 세트'**를 즉석에서 생성할 수 있다. 이는 날짜별 통계, 계층형 조직도 등에서 활용도가 매우 높다.
- LEFT JOIN과 COUNT의 상관관계:
- 조인 전략: CTE(0~23시)가 기준이 되어야 하므로 LEFT JOIN을 사용한다. INNER JOIN을 쓰면 데이터가 없는 행이 다시 사라져 도루묵이 된다.
- 집계의 정교함: COUNT(*)는 데이터가 없어서 조인 결과가 NULL인 행도 '행이 존재한다'고 판단해 1을 반환할 위험이 있다. 따라서 실제 입양 건수를 나타내는 **COUNT(A.ANIMAL_ID)**를 사용하여 정확히 0을 출력하게 만든 점이 훌륭하다.
- 비즈니스 리포팅의 정석: "빈틈없는 리포트"는 데이터 분석의 기본이다. 값이 없는 구간을 0으로 채워 넣는 이 기법은 실제 현업에서 통계 대시보드를 구축할 때 가장 기본이 되는 테크닉이다.
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
예시
SQL문을 실행하면 다음과 같이 나와야 합니다.
정답 쿼리
WITH RECURSIVE CTE AS (
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1 FROM CTE
WHERE HOUR < 23
)
SELECT CTE.HOUR, COUNT(HOUR(A.DATETIME)) AS COUNT
FROM CTE
LEFT JOIN ANIMAL_OUTS A ON CTE.HOUR = HOUR(A.DATETIME)
GROUP BY CTE.HOUR
ORDER BY CTE.HOUR
[SQL/Level 4] 입양 시각 구하기(2) - 없는 데이터 생성하기
1. 문제 풀이의 핵심: "데이터의 빈틈을 메워라"
일반적인 GROUP BY HOUR(DATETIME)를 사용하면, 입양 기록이 있는 시간대만 출력됩니다. 하지만 이 문제는 입양 기록이 0건인 0시부터 23시까지 모든 시간이 나와야 합니다.
이때 사용하는 것이 바로 **WITH RECURSIVE**입니다.
2. 핵심 개념: WITH RECURSIVE (재귀 CTE)
재귀 CTE는 자기 자신을 참조하여 연속된 데이터를 생성하는 가상 테이블입니다.
- Anchor Member (시작점): SELECT 0 AS HOUR (0부터 시작)
- Recursive Member (반복부): SELECT HOUR + 1 FROM CTE (1씩 증가)
- Termination Check (정지 조건): WHERE HOUR < 23 (23이 되면 멈춤)
4. 주의사항 및 디테일 (오답노트용)
- LEFT JOIN 필수: INNER JOIN을 쓰면 다시 데이터가 없는 행이 사라집니다. 가상 테이블 CTE를 기준으로 왼쪽 조인을 해야 빈 시간대가 유지됩니다.
- COUNT(*) vs COUNT(컬럼): COUNT(*)를 쓰면 조인된 결과가 NULL이어도 행 자체가 존재하므로 1이 카운트될 수 있습니다. 실제 데이터가 존재할 때만 세어지도록 COUNT(A.ANIMAL_ID)를 쓰는 것이 가장 안전합니다.
🎯 기업 쿼리테스트 출제자의 의도
이 문제는 지원자의 **"고급 SQL 구사 능력"**과 **"데이터 정합성에 대한 이해"**를 동시에 테스트합니다.
① 가상 데이터 생성 능력 (Dummy Data Generation)
실무에서는 통계 리포트를 만들 때 "데이터가 없는 날짜도 0으로 표기해달라"는 요구사항이 많습니다. 이때 당황하지 않고 CTE나 숫자 테이블을 만들어낼 수 있는지 봅니다.
- 의도: "현업의 복잡한 리포팅 요구사항을 해결할 수 있는가?"
② 조인(Join)의 깊은 이해
단순히 합치는 것을 넘어, **"보존해야 할 데이터(시간대)"**와 **"집계해야 할 데이터(입양 기록)"**의 관계를 정확히 설정할 수 있는지 확인합니다.
- 의도: "데이터 유실 없이 정확한 통계를 산출할 수 있는가?"
🚩 마무리
"데이터가 없으면 만들어서라도 집계한다."
쿼리 테스트에서 WITH RECURSIVE를 자유자재로 쓴다는 것은, 단순히 저장된 값을 꺼내오는 수준을 넘어 데이터의 구조를 직접 설계할 수 있다는 뜻.