SQL > 프로그래머스 59413 [level 4] 입양 시각 구하기(2)

문제

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

프로그래머스

[문제 포인트 짚기]

포인트는 단 하나. 0시부터 23시까지 이다. 이게 왜 포인트인가?

기존 테이블에 DATETIME은 0~23시의 값을 모두 갖지 않기 때문이다. (예제는 7~19시의 값을 갖고 있다.)

 

풀이

[RECURSIVE]

0~23시의 값을 만들어내기 위해 어떤 방법이 있을까 찾아보던 중 RECURSIVE에 대해 알게 되었다. 다음 블로그를 참고해서 작성한다.

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

[MYSQL] 📚 RECURSIVE (재귀 쿼리)

 

기본적인 문법은 다음과 같다. WITH RECURSIVE > 내부에서 UNION 사용.

WITH RECURSIVE CTE AS (
	SELECT 1 AS n -- 초기 값 설정
    UNION ALL
    SELECT n+1 AS num -- 재귀
    FROM CTE
    WHERE n<3 -- 정지 조건
 )

 

1. 컬럼 n의 초기값을 설정한다.
2. UNION ALL을 사용!
3. 아래는 재귀 함수를 작성한다.
4. WHERE을 이용해서 종료 조건을 설정

[특징과 조건]

• 메모리 상에 가상의 테이블 저장
• UNION 사용
• 초기값을 위해 최소 1개의 비반복문 사용
• 바깥의 가상 테이블을 참조하는 반복문이 필요
• 반복문은 정지조건이 필수
• 가상의 테이블을 구성하면서 그 자신을 이용해 값을 결정할 때 유용

[진짜 풀이]

순서는 이렇다.

1. 0~23의 시간을 만드는 임시 테이블을 생성

: 방법은 위에 나와있는 RECURSIVE와 동일하다.

WITH RECURSIVE CTE AS (
    select 0 as HOUR
    UNION ALL
    select HOUR+1 as num
    from CTE
     where HOUR+1 <= 23
),

 

2. ANIMAL_OUTS에서 시간대별 입양 건수 계산 -> 임시테이블사용

: HOUR로 그룹을 묶고 ANIMAL_ID의 수를 계산한다.

CTE2 AS (
    SELECT HOUR(DATETIME) AS HOUR , COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY 1
)

 

3. 두 테이블 조인 및 비어있는 값은 COALESCE를 이용해서 0으로 출력하기

: CTE를 뒤에 사용해서 RIGHT조인을 사용했는데 왼쪽에 넣고 LEFT 조인을 해도 괜찮다.

여기서는 COUNT가 문제인데 COALESCE를 사용했다. 값이 없다면 0으로 표시되도록 작성했다.

-- 전체 코드
WITH RECURSIVE CTE AS (
    select 0 as HOUR
    UNION ALL
    select HOUR+1 as num
    from CTE
     where HOUR+1 <= 23
),
CTE2 AS (
    SELECT HOUR(DATETIME) AS HOUR , COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY 1
)
select c1.hour, COALESCE(c2.count,0) AS COUNT
FROM CTE2 c2 right JOIN CTE c1
    ON c1.hour = c2.hour