[SQL] 재귀쿼리 : RECURSIVE

🔭 프로그래머스 SQL 문제 : 입양 시각 구하기(2)

내가 처음 사용한 코드는 다음과 같다.

SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT 
FROM ANIMAL_OUTS 
GROUP BY HOUR 
HAVING HOUR BETWEEN 0 AND 23
ORDER BY HOUR;

오답 이유

테이블에 존재하지 않는 시간에 대해서도 표기해야 한다

하지만 예시에서의 정답은 0부터 23시까지, 값이 없는 경우의 시간도 모두 표현되어 있지만 내가 쓴 답은 집계가 되지 않는 시간은 표현되지 않는다. 단순히 제공된 테이블에서만 처리할 수 있는 문제가 아니라고 생각했다. 그럼 어떻게 풀어야하지? 아무리 고민해도 답이 나오지 않았는데 다행히도! 나같은 사람이 많았는지 지금까지 푼 프로그래머스 SQL 문제 중 가장 많은 질문 수가 있었다.

해결 방법

재귀쿼리 사용

질문들은 살펴본 결과 '재귀쿼리' 라는 것을 사용해야 가장 간단한 답을 뽑아낼 수 있는 것 같았다.

재귀쿼리에 대해 아는 것이 하나도 없었기에 정답 코드를 보고 역으로 개념으로 들어가기로 했다.

정답 코드

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

SELECT HOUR, COUNT(HOUR(DATETIME)) AS 'COUNT' 
FROM TIME
LEFT OUTER JOIN ANIMAL_OUTS
ON (HOUR = HOUR(DATETIME))
GROUP BY HOUR;

재귀쿼리

재귀쿼리의 개념을 살펴보기 위해 MySQLtutorial의 글을 참고했다.

# 재귀 쿼리로 테이블 만들기

WITH RECURSIVE cte_count (n)   # cte_count는 만들 테이블 명
AS(
  SELECT 1   # 초기값
  UNION ALL   # 위의 쿼리와 아래 쿼리의 값을 연산
  SELECT n + 1   # 값을 불러와 연산
  FROM cte_count   # 값을 불러올 테이블
  WHERE n < 3   # 연산을 멈출 조건절
)
# 재귀쿼리 사용 방법
SELECT n
FROM cte_count;   

해당 재귀쿼리로 TIME 이라는 테이블을 만들어보니 아래와 같은 결과가 나왔다.

정답을 보면 HOUR이 0, 1 과 같은 때는 값이 없는데도 테이블에 존재한다. 따라서 LEFT OUTER JOIN을 사용해 제공된 테이블의 HOUR(DATETIME)을 GROUP BY HOUR로 COUNT(DATETIME)다.