[MYSQL] RECURSIVE 재귀쿼리를 통해서 "입양 시각 구하기(2)" 해결하기

문제 : 아래 ANIMAL_OUTS 테이블 스키마를 바탕으로 시간대별 입양건수 구하기

 

결과 예시 :

0~ 23시까지

 

 

1차 풀이 : 단순하게 그루핑하면 될줄 알고 풀이했으나 실패

SELECT EXTRACT(HOUR FROM DATETIME) as HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
ORDER BY HOUR ;

 

2차 풀이 : 0~23 시간대 중에서 데이터가 없는 시간대는 제외되고 출력되므로 별도의 0~23까지의 임시테이블을 만들고 기존 테이블과 병합해야함을 깨달음

아래와 같이 RECURSIVE 를 통해서 문제해결. WITH의 경우에는 판다스에서 생각해본다면 df 를 변수에 저장하는것과 같음

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

SELECT HOUR FROM HOURS;

 

판다스의 경우 아래와 같음

HOURS=pd.DataFrame({"HOUR":[i for i in range(24)]} )

그래처 1차와 2차 풀이한 테이블 두개를 조인하였으나 실패. 값이 없는 시간대가 존재하며 IFNULL을 통해서 NULL => 0 으로 변경하여 다시 풀이

 

 

최종풀이 :

SELECT HOURS.HOUR, IFNULL(ANIMAL_COUNT.COUNT, 0)
FROM 
    (
        WITH RECURSIVE HOURS AS
              (
                SELECT 0 AS HOUR
                UNION
                SELECT HOUR +1 FROM HOURS
                WHERE HOUR < 23
              )

         SELECT HOUR FROM HOURS
    ) AS HOURS
LEFT JOIN 
    (
        SELECT EXTRACT(HOUR FROM DATETIME) as HOUR, COUNT(*) AS COUNT
        FROM ANIMAL_OUTS
        GROUP BY HOUR
        ORDER BY HOUR 
    ) AS ANIMAL_COUNT
ON HOURS.HOUR = ANIMAL_COUNT.HOUR