[Programmers] 입양 시각 구하기(2)_데이터 없는 날짜 0으로 채워 조회하기

문제

 

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

 

풀이

WITH RECURSIVE tt AS (
    SELECT 0 AS tn
    UNION ALL
    SELECT tn + 1 FROM tt
    WHERE tn < 23)

SELECT tt.tn as HOUR, COUNT(ht.animal_id) as COUNT
FROM tt
LEFT JOIN (SELECT DATE_FORMAT(datetime, '%H') as hour
           , animal_id
           FROM animal_outs) ht
ON tt.tn = ht.hour
GROUP BY 1

• WITH RECURSIVE를 통해 0~23시까지의 시간 테이블을 만들 수 있다. 
• LEFT JOIN 시, ON 조건에 animal_outs 테이블에서 바로 HOUR(datetime)를 쓸 수 없으므로 따로 서브쿼리(시간, animal_id만 나오는)를 만들어 주고 난 후, ht 서브쿼리 테이블의 hour칼럼을 JOIN 조건에 적어준다.

 

배운 점

데이터가 없는 날짜를 0으로 채워서까지 추출하고 싶다면
1. Recursive 테이블로 필요한 연, 월, 시간 등 날짜 테이블을 만들고
2. Recursive 테이블의 연, 월, 시간 등 날짜 테이블을 기준으로 LEFT JOIN하고 해당 칼럼을 추출하자.

 

번외) 시간 임시 테이블 말고, 날짜 임시 테이블은?

문제의 데이터를 살펴보면 2015년 8월달에 데이터가 그나마 많다.

따라서 위의 문제와 비슷하게 이번에는 8월 한달 동안의 임시 테이블을 만들고 조인해 일별로 입양이 몇 건 일어났는지 살펴보려고 한다. 

WITH RECURSIVE cte AS (
        SELECT DATE_FORMAT('20150801', '%Y-%m-%d') AS DT FROM DUAL
        UNION ALL
        SELECT DATE_ADD(DT, INTERVAL 1 DAY) FROM cte -- ADDDATE도 가능
        WHERE DT < DATE_FORMAT('20150831', '%Y-%m-%d')
)

SELECT cte.dt as date, COUNT(ht.animal_id) as COUNT
FROM cte
LEFT JOIN (SELECT DATE_FORMAT(datetime, '%Y-%m-%d') as datee
           , animal_id
           FROM animal_outs
           WHERE DATE_FORMAT(datetime, '%Y-%m') = '2015-08') ht
ON cte.dt = ht.datee
GROUP BY 1