[SQL] 입양 시각 구하기(2)

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

 

✔️ 문제

 

 

문제는 다음과 같다.

 

 

✔️ 풀이

 

 

다음 예제와 같이 23시까지 없는 시간과 카운트도 출력돼야 한다.

근데, 해당 테이블에는 7부터 시간을 출력할 수 있는데

나머지 0부터 23 중 없는 시간은 어떻게 출력해야 할까?

 

https://stackoverflow.com/questions/54643238/what-is-mysql-query-to-print-1-to-10

what is mysql query to print 1 to 10?

 

여기에 대한 답은 이곳에서 찾을 수 있었다.

즉, RECURSIVE 하게 돌면서 가상의 테이블을 0부터 23까지 데이터를 만들고

해당 테이블과 기존 테이블을 JOIN해서 사용할 수 있었다.

 

WITH RECURSIVE HOUR_NUMS AS (
    SELECT 0 as N UNION ALL SELECT N+1 FROM HOUR_NUMS WHERE N < 23
)
SELECT A.N AS HOUR, COUNT(B.HOUR) as COUNT
FROM HOUR_NUMS A
LEFT JOIN (
    SELECT ANIMAL_ID, EXTRACT(HOUR FROM DATETIME) as HOUR FROM ANIMAL_OUTS
) B ON A.N = B.HOUR
GROUP BY A.N
ORDER BY HOUR

 

가상의 테이블인 HOUR_NUMS라는 테이블을 만들어 준 다음에,

0부터 1씩 순차적으로 증가하면서 N이 22일때까지 증가한 테이블인

0~23에 해당하는 데이터를 삽입한 테이블을 만든다.

 

해당 테이블의 컬럼을 HOUR로, 기존 테이블의 HOUR 수를 COUNT로 지정하는데

이때 JOIN하면서 기존 테이블에 없는 HOUR 컬럼을 반환해주어야 한다.

EXTRACT 구문을 사용하면 DATETIME 타입의 데이터에서 시간, 분 등만을

뽑아서 새롭게 반환할 수 있다. 이 과정을 JOIN 쿼리로 삽입하여

카운트를 할 ANIMAL_ID와 HOUR를 만들어주어 별칭 B로 정한다.

 

이렇게하면 ON 조건에 N과 HOUR을 동일한 조건식으로 만들 수 있고,

0부터 23까지 각 중복의 그룹을 A.N으로 묶어주게 되면

중복된 것을 COUNT하여 출력할 수 있게 된다.

또한, 시간대 순으로 정렬해야하는 것은 ORDER BY로 정렬시켜준다.

 

화이팅 💪