[프로그래머스/MySQL/GROUP BY/LV.4] 입양 시각 구하기(2)

문제

문제 설명

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

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

예시

SQL문을 실행하면 다음과 같이 나와야 합니다.

HOUR	COUNT
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	3
8	1
9	1
10	2
11	13
12	10
13	14
14	9
15	7
16	10
17	12
18	16
19	2
20	0
21	0
22	0
23	0

---

본 문제는 Kaggle의 "Austin Animal Center Shelter Intakes and Outcomes"에서 제공하는 데이터를 사용하였으며 ODbL의 적용을 받습니다.

출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges

---

✔️  문제접근

1. 시간대를 0~23까지 생성하는 임시 테이블을 생성한다.
   • RECURSIVE 키워드와 UNION ALL 키워드를 활용해 0~23시까지 시간대를 생성한다.
2. 시간대별 COUNT를 포함하는 결과 쿼리를 작성한다.
   • ANIMAL_OUTS 테이블과 앞서 만든 임시 테이블을 LEFT JOIN 연산한다.
   • GROUP BY로 HOUR 별 그룹화를 진행한다.
   • COUNT 연산을 진행할 때, COALESCE를 사용해 NULL 값을 0으로 변환시킨다.

⭐️   내 정답

-- 임시 테이블 생성 (시간대를 0~23까지 생성)
WITH RECURSIVE hours AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM hours
    WHERE HOUR < 23
)

-- 시간대별 COUNT를 포함하는 결과 쿼리
SELECT h.HOUR, 
       COALESCE(COUNT(a.DATETIME), 0) AS COUNT
FROM hours h
LEFT JOIN ANIMAL_OUTS a 
ON HOUR(a.DATETIME) = h.HOUR
GROUP BY h.HOUR
ORDER BY h.HOUR;

🔎 짚고 넘어가기

1. RECURSIVE (재귀적 CTE)

재귀적 CTE(Common Table Expression)는 SQL에서 재귀적으로 데이터를 생성하거나 처리할 때 사용하는 방식입니다. 이 방법을 사용하면 테이블의 계층 구조를 탐색하거나 숫자 시퀀스를 생성하는 등 다양한 재귀적 연산을 수행할 수 있습니다.

RECURSIVE CTE의 기본 구조

재귀적 CTE는 두 부분으로 나뉩니다

• 앵커 멤버(Anchor member): 재귀의 기초를 형성하는 초기 쿼리입니다.
• 재귀 멤버(Recursive member): 자신의 결과를 참조하여 반복적으로 데이터를 생성하는 쿼리입니다.

WITH RECURSIVE cte_name AS (
    -- 앵커 멤버 (초기 결과 생성)
    SELECT initial_query
    UNION ALL
    -- 재귀 멤버 (자기 자신을 참조하여 데이터 확장)
    SELECT recursive_query
    FROM cte_name
    WHERE termination_condition
)
SELECT * FROM cte_name;

예시

0부터 5까지의 숫자를 생성하는 재귀적 CTE를 예시

WITH RECURSIVE numbers AS (
    SELECT 0 AS num          -- 앵커 멤버: 0을 초기값으로 시작
    UNION ALL
    SELECT num + 1           -- 재귀 멤버: 이전 결과에서 1을 더함
    FROM numbers
    WHERE num < 5            -- 종료 조건: num이 5보다 작을 때까지 반복
)
SELECT * FROM numbers;

쿼리 결과

num
----
0
1
2
3
4
5

2. COALESCE 함수

COALESCE는 SQL 함수로, 인수 목록 중에서 첫 번째로 NULL이 아닌 값을 반환합니다. 주로 NULL을 다른 값으로 대체할 때 사용됩니다.

기본 구조

COALESCE(expression1, expression2, ..., expressionN)

• expression1부터 expressionN까지의 리스트를 받아, 첫 번째로 NULL이 아닌 값을 반환합니다.
• 모든 인수가 NULL일 경우 NULL을 반환합니다.

예시

SELECT COALESCE(NULL, NULL, 'Hello', 'World') AS result;

이 쿼리는 Hello를 반환합니다. NULL 값을 무시하고, 첫 번째 NULL이 아닌 값을 반환하기 때문입니다.

COALESCE는 NULL 값을 처리하거나 기본값을 설정할 때 매우 유용합니다. 예를 들어, 데이터베이스에 특정 열의 값이 NULL인 경우 기본값을 설정하거나, 여러 열 중 유효한 값을 선택할 때 사용됩니다.