[SQL] 입양 시각 구하기(2)

🔎 문제설명

• 출처: 프로그래머스
• 사용언어(RDBMS): SQL(MySQL)
• 문제:

  보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

✅ 제출답안

# 재귀CTE를 이용한 답안
WITH RECURSIVE TIME AS (
  SELECT 0 AS HOUR
  UNION ALL
  
  SELECT HOUR + 1
  FROM TIME
  WHERE HOUR < 23
),
COUNT_BY_HOUR AS (
    SELECT HOUR(DATETIME) AS HOUR,
           COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR(DATETIME)
)
SELECT HOUR, IFNULL(COUNT, 0) AS COUNT
FROM COUNT_BY_HOUR AS A RIGHT JOIN TIME AS B USING(HOUR)
ORDER BY HOUR

🚀 풀이기록

이 문제의 의도는 재귀적 방법을 사용하여 풀도록 하려고 했던 거 같다. 다시 풀려고 하니 기억이 나지 않아 지난번에 기록했던 내용을 옮겨놓는 데에 의의를 두려고 한다.

• 재귀CTE(Common Table Expression) 참고: Recursive CTE

재귀CTE를 사용하지 않고 GROUP BY를 사용하여 쿼리를 작성하는 경우

문제에서는 0시부터 23시까지의 입양된 동물의 수를 표시하고 싶지만, 테이블에서 특정시간대에 입양된 동물이 존재하지 않는 경우는 그 시각이 나타나지 않는다(아래 이미지에서는 가장 빠른 시간이 0시가 아닌 7시이다.).

따라서 입양이 이루어지지 않은 시간도 나타내기 위해서는, 일일이 숫자를 0부터 23까지 일일이 써줄수 있으나 이건 너무 비효율적이다. 내 머리로는 해결할 수 없는 문제라고 판단하고 다른 분들의 답을 확인했다. 다른 분들은 재귀CTE(Recursive CTE)방법을 사용했다고 하는데 코드를 봐도 잘 이해가 안가서 GPT에게 도움을 요청했다.

재귀CTE의 장점

• 재귀적인 방식으로 숫자를 생성하므로, 추가적인 JOIN이나 복잡한 쿼리를 사용하지 않고 간단하게 시퀀스를 만들 수 있음.
• WHERE 조건을 조정하여 원하는 범위의 숫자만 생성 가능.

재귀 CTE를 사용한 숫자 생성

WITH RECURSIVE NUMBERS AS (
  SELECT 0 AS NUMBER
  UNION ALL
  SELECT NUMBER + 1
  FROM NUMBERS
  WHERE NUMBER < 23
)
SELECT NUMBER FROM NUMBERS;

1. WITH RECURSIVE NUMBERS AS (...): NUMBERS라는 이름의 재귀CTE를 정의
2. SELECT 0 AS NUMBER: 재귀의 시작값으로 0을 설정
3. UNION ALL: 재귀적으로 값을 생성. 첫번째 SELECT는 0을 반환하고, 두 번째 SELECT는 이전 NUMBER에 1을 더하여 숫자를 증가시킴
4. WHERE NUMBER < 23: NUMBER가 23보다 작은 값일 때만 재귀를 계속 수행
5. SELECT NUMBER FROM NUMBERS;: NUMBERSCTE에서 생성된 숫자를 결과로 반환
   => 해당 쿼리는 0부터 23까지의 숫자 리스트를 반환