입양 시각 구하기2

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


[단계별 풀이]
해당 문제는 이빠진 데이터를 채워야하는 문제로 재귀저문제]

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

 

 

[단계별 풀이]

해당 문제는 이빠진 데이터를 채워야하는 문제로 재귀테이블을 사용해야한다

구조를 알아보자

WITH RECURSIVE 테이블명 (컬럼명) AS (
    SELECT 초기값
    UNION ALL
    SELECT 반복할 쿼리 -- 이전값에 대해 쿼리 적용
    WHERE 반복 조건 -- FALSE가 나오면 반복종료, WHERE/LIMIT 등 사용하면 됨
)
SELECT *
FROM 테이블명

 

1. 재귀 테이블을 활용해서 0부터 23까지 시간데이터를 만든다

2. 마찬가지로 OUTS 테이블에서 HOUR 컬럼을 만든다 (만드는 김에 HOUR로 GROUP BY 해서 COUNT도 구해준다)

3. 재귀 테이블을 기준으로 LEFT JOIN 한다

4. HOUR와 COUNT 컬럼을 조회하되 COUNT컬럼의 NULL값을 0으로 대체한다

5. HOUR를 기준으로 오름차순 정렬한다

 

 

[정답]

WITH RECURSIVE HOUR_CTE (HOUR) AS (
    SELECT 0 
    UNION ALL
    SELECT HOUR + 1
    FROM HOUR_CTE
    WHERE HOUR < 23
)
SELECT H.HOUR,
       IFNULL(COUNT, 0) AS COUNT
FROM HOUR_CTE H
LEFT JOIN (SELECT HOUR(DATETIME) HOUR,
                  COUNT(ANIMAL_ID) COUNT
           FROM ANIMAL_OUTS
           GROUP BY 1) SUB
ON H.HOUR = SUB.HOUR