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

with recursive time as (
    select 0 as hour
    union all
    select hour+1 from time where hour<23)

SELECT r.hour as HOUR,COUNT(ANIMAL_ID)as COUNT
FROM ANIMAL_OUTS as o right join time as r
on HOUR(o.DATETIME) = r.hour
GROUP BY 1
ORDER BY 1

문제

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

 

 이 문제는 약간 노가다(?)로 시간대를 하나하나 만들어서 사용하거나, 재귀함수를 사용하여 0시부터 23시까지 만들어서 재귀 쿼리를 기준으로 Right 또는 Left 조인을 통해서 만듭니다.

 

 먼저 재귀쿼리 with recursive "alias" as 를 만듭니다. 재귀 쿼리는 반복을 한번하든 아니든 반복을 해야하는 구간이 필수 입니다. 그리고 UNION을 사용해 주어야 합니다. 그리고 반복문의 정지 조건이 필요합니다. 기본적으로 반복구간이 UNION 위에있고 정지 조건이 UNION 아래에 있습니다. UNION 위 쿼리의 select 문이 재귀쿼리 default 값 입니다. alias와 함께 0시부터니 0으로 지정해줍니다. 

 다음으로 반복문을 UNION ALL 아래에 지정해줍니다. Select "alias"에 반복할때 마다 변경할 값을 지정해 준뒤(여기선 +1) from 절에 재귀함수 alias 명을 기입하고 where 조건에 재귀함수 정지조건을 지정합니다. 23시까지 필요하므로 23 미만으로 지정해줍니다. (23 이하로 지정해주면 23+1까지 반복하여 24가 나오게 됩니다)

 이렇게 재귀함수 time과 그 컬럼 hour을 만들었습니다. (참고로 시간 컬럼을 따로 만드는 이유는 DATETIME 에는 0시에서 6시 구간 등 일부 시간이 없기 때문에 HOUR 함수를 지정해서 만들면 해당 시간대가 나오지 않기 때문에 만듭니다)

 

 다음으로는 메인쿼리를 짜야합니다. 메인 쿼리에서는 시간, 그리고 시간대별 입양이 일어난 건수를 확인해야합니다. 따라서 시간을 기준으로 GROUP BY 처리하여 동물 ID를 수를 세어줍니다. 그러기전에 재귀쿼리테이블과 기존 테이블을 JOIN 시켜주어야 합니다. 그러나 기존 테이블에는 0시등 기타 시간이 없어서 inner join을 하게 될 경우 해당 값들이 날아가 버립니다. 따라서 해당값이 모두 있는 아까만든 재귀 쿼리 테이블을 기준으로 join을 해줍니다. 

 위의 문장에서는 재귀쿼리를 오른쪽에 썻으므로 right join으로 썼습니다. 그리고 on 절로 시간을 연결해주었는데 ANIMAL_OUTS에서는 시간 컬럼이 없어서 함수를 써서 만들어 비교해 주었습니다. 그렇게 해주면 아까만들어진 0시부터 23시까지 기준으로 값이 일치하는 데이터가 연결이 됩니다. 그 후  select 문에서 재귀쿼리 hour을 시간 컬럼으로 써주고 해당기준으로 GROUP BY처리해줍니다. 그다음  유니크한 값을 지니는 ANIMAL_ID를 COUNT 해주어 시간에 맞는 입양보낸 동물을 수를 세어줍니다. ( ANIMAL_OUTS 자체가 입양보낸 동물들의 데이터로 따로 데이터를 더 처리할 필요 없음)

 그 후 마지막으로 시간 순으로 정렬하여 마무리합니다.