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

<ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블>

---

<문제>

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

---

<풀이>

이 문제는 처음에는 쉽게 date_format을 이용해 시간을 구한다음 그룹바이를 해주면 되는 쉬운 문제인지 알았는데

결과값을 보니 없는 시간대가 있었고 이걸 새로 만들어줘야했다 그래서 1차로 테이블에 애초에 없는데 어떻게 만들지란 생각이 들었고

결국엔 구글링 해보는 수 밖에 없었다.

그 결과 밑과 같은 쿼리문 실행!

with recursive cte (hour) as
( select 0
  union all
  select hour + 1
  from cte
  where hour < 23
 )
select cte.hour,
       count(animal_outs.animal_id) as 'count'
from cte left join animal_outs on cte.hour=hour(animal_outs.datetime)
group by hour

WITH RECURSIVE 구문으로 hour column에 0~23 값을 가진 cte 뷰를 생성
cte 와 animal_outs를 left join -> cte.hour와 animal_outs의 hour(animal_outs.datetime)이 같은 녀석들을 기준으로 left join
**hour()함수는 animal_outs의 datetime 형식에서 hour 데이터만 쏙 뽑아올 수 있는 함수

left join을 하는 이유?
오른쪽 테이블에 맞는 컬럼이 없어도 왼쪽 테이블을 기준으로 무조건 JOIN 되기 때문에,
만약 hour(animal_outs.datetime)에 18시가 없다 한들 cte에는 있으므로 cte.hour 18에 해당하는 count(animal_outs.animal_id) 값은 0으로 row가 생성될 것

**GROUP BY 개수 세라고 하면 count(*) 많이 하는데, 여기서 count(*) 하면 틀림
-hour(animal_outs.datetime)에 18시가 없다 하더라도 cte.hour에는 18이 있기 때문에 left join한 테이블에는 animal_outs값은 NULL로 채워진 cte.hour 18 row가 있을 것

따라서, count(*)을 하게 되면 row 개수를 세는 것이기 때문에 입양데이터가 없어도 cte 뷰 때문에 생성된 row가 count 되어 count(*)값은 무조건 1 이상이 된다.
그러나, 우리는 입양 건수가 있는 것만 세어야 하므로 여기서는 count(animal_outs.animal_id)를 세도록 함

<인용 사이트 : https://huilife.tistory.com/42>

 

<이 외의 다른 풀이>

set 함수를 이용한 문제풀이

SET @HOUR := -1; # 변수선언

SELECT (@HOUR := @HOUR +1) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23

• set 함수를 이용하여 데이터베이스에는 없는 시간까지해서 0부터 23시까지를 만들어야야 함 --> 0부터 23시까지의 테이블을 먼저 만들어 주기

https://huilife.tistory.com/42

[WITH RECURSIVE] 프로그래머스 - 입양 시각 구하기(2)

set 함수를 사용해본 적도 없고 본 적도 없었는데 파이썬 처럼 변수선언을 통해 해결할 수 있다는 것을 처음 알았다.

이 블로그에서 set 함수에 대한 풀이를 잘 정리해놓으셨기 때문에 참고 사이트로 올린다.

---

• WITH RECURSIVE - https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

MySQL :: MySQL 8.0 Reference Manual :: 15.2.20 WITH (Common Table Expressions)

예시를 통해 알 수 있음(첫 번째 참고했던 블로그에서도 잘 정리해둠)

WITH RECURSIVE 생성할_뷰_이름 AS
(
	초기_SQL
    
    	UNION ALL

	반복할_SQL_반복을_멈출_WHERE절_포함
)

 

• set

set @변수이름 = 대입값; 
select @변수이름 := 대입값;

https://wnwa.tistory.com/53

[MySQL] SET을 통해 변수 사용하기

- SET 명령어 외에 =는 비교연산자로 활용되기 때문에 SELECT에 변수를 선언하고 값을 대입할 때에는 :=를 사용