240809 TIL / recursive CTE 관련

SQL

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

처음에 접근 했던것은 입양시간에서 시간을 조회해오고 group by 를 활용한 후 집계함수 인 count 를 쓰면 되지 않을까?

 

with animal_outs_new as
    (
    SELECT *,
            substr(datetime, 12,2) HOUR
    from animal_outs
    )
select HOUR,
        count(animal_id) COUNT
from animal_outs_new
group by hour
order by 1

 

with 절을 사용한 이유는 datetime 에서 시간을 뽑아 새로운 컬럼을 만든것을 활용하기 위함 이었다.

 

 

근데 이렇게 하고 보니까....... 입양이 없는 시간대의 행은 나오지 않는다.

 

예를 들어 00시 부터 06까지 행이 없고 20시 부터 23시까지 행이 없다.

 

그러면 행을 추가해서 뭔가를 해야 하나 싶을때

 

00~23 까지 나오게 하는 방법이 없나? 싶어서 검색한 결과 나온

 

Recursive CTE

 

https://velog.io/@cyanred9/SQL-Recursive

[SQL] Recursive 표현으로 쿼리 만들기

 

with recursive CTE as
(
    select 0 HOUR
    union all
    select HOUR+1
    from CTE
    where HOUR < 23
)
select  CTE.HOUR HOUR,
        count(o.datetime) COUNT
from CTE left join animal_outs o on CTE.HOUR= substr(o.datetime, 12,2)
group by 1
order by 1

 

with 를 활용해 0 부터 23까지 나오는 테이블을 만들고 hour 를 key 로 활용해서 animal_outs 테이블과 join 함