[programmers] SQL Lv. 4 - 입양 시각 구하기(2)

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

 

문제 설명

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

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

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

 

---

풀이

초기 접근 방식

SELECT DATE_FORMAT(DATETIME, '%H') AS HOUR, COUNT(*) FROM ANIMAL_OUTS AS COUNT
GROUP BY DATE_FORMAT(DATETIME, '%H')
ORDER BY CAST(HOUR AS UNSIGNED)

위 쿼리는 입양된 동물이 있는 시간대만 출력하기 때문에 문제의 조건과 다른 결과가 출력된다.

• 모든 시간대가 포함되지 않음
  • 이 쿼리는 ANIMAL_OUTS 테이블에서 실제 입양 기록이 있는 시간대만 반환
  • 입양된 동물이 없는 시간대가 결과에서 누락되지 않도록 개선해야한다.
• `GROUP BY`를 사용하여 시간대별 그룹화
  • 데이터가 없는 시간은 처리가 불가능함
  • 0~23시 범위의 시간대를 강제로 포함하도록 개선해야한다.

 

개선된 풀이

0~23까지 모든 시간대를 테이블로 생성

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

• 입양 기록이 없는 시간대도 JOIN을 통해 출력이 가능하도록 시간대 테이블을 생성

 

LEFT JOIN을 통해 조건에 맞게 조회

SELECT 
    H.HOUR,
    COUNT(A.DATETIME) AS COUNT
FROM HOUR_TABLE H
LEFT JOIN ANIMAL_OUTS A
    ON H.HOUR = HOUR(DATETIME)
GROUP BY H.HOUR
ORDER BY H.HOUR

• 생성한 시간 테이블(HOUR_TABLE)을 ANIMAL_OUTS 테이블과 `LEFT JOIN`하여 입양 기록이 없는 시간도 조회 가능하게 만듦
• `LEFT JOIN`을 사용하면 ANIMAL_OUTS에 데이터가 없는 경우에도 시간대가 유지되므로, COUNT가 0인 시간대도 출력이 가능

 

---

제출

-- 0 ~ 23시까지 시간대 테이블 생성
WITH RECURSIVE HOUR_TABLE AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM HOUR_TABLE WHERE HOUR < 23
)
-- 조회
SELECT 
    H.HOUR,
    COUNT(A.DATETIME) AS COUNT
FROM HOUR_TABLE H
LEFT JOIN ANIMAL_OUTS A
    ON H.HOUR = HOUR(DATETIME)
GROUP BY H.HOUR
ORDER BY H.HOUR