TIL(Today I Learned)14일차(23.12.15)

1. SQL

 1) 프로그래머스 73번 문제

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

프로그래머스

# 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회

 

73번 문제의 요구사항이다.

아래처럼 count함수를 사용하면 간단하게 구할 수 있다.

SELECT HOUR(DATETIME) HOUR, COUNT(*) COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
ORDER BY HOUR

 

그런데 문제에서는 COUNT값이 없는 시간대도 모두 표시하라고 한다.

어떻게 할 수 있을까??

 

방법을 찾아보던 중

가상의 테이블을 하나 만들고

그 테이블과 기존 테이블을 JOIN해서

각 시간대별 COUNT값을 구하는 방법이 있었다.

 

그 때 사용하는 함수가 바로 WITH RECURSIVE 함수이다.

 

가상의 테이블을 만들어 주는 WITH RECURSIVE 쿼리부터 먼저 살펴보자

WITH RECURSIVE NEW_TIME AS
(
    SELECT 0 T
    
    UNION ALL
    
    SELECT T+1
    FROM NEW_TIME
    WHERE T<23
)

문제에서 사용한 WITH RECURSIVE 쿼리인데 이렇게 보면 이해가 잘 안 될 수 있으니

WITH RECURSIVE 함수의 문법 구조를 살펴보자

WITH RECURSIVE 테이블명 AS
(
   SELECT 초기값 AS 별명1
    
   UNION ALL
    
   SELECT 계산식(EX. 별명1+1)
   FROM 테이블명
   WHERE 제어문(EX.별명1<23)
)

하나씩 뜯어보면

 

WITH RECURSIVE 테이블명 AS ( ) 안에

두 개의 SELECT가 있고

이 두 개의 SELECT가 UNION ALL로 붙는다.

 

RECURSIVE함수는 재귀함수로 여기서 재귀함수란 자기 자신을 호출하는 함수이다.

자기 자신을 호출한다는 것은 제동이 걸리기 전까지는 이전 작업을 반복한다라고 이해할 수 있다.

제동을 걸어야하기때문에 WHERE절에 제어문을 넣어주는 것이다.

 

우리는 0에서부터 1씩 더해서 23까지 만들것이기때문에

위 문법 구조에서 '초기값'에는 0을,

'계산식'에는 별명1 + 1을,

WHERE절에는 별명1 < 23을

넣어주면된다.

 

이렇게 WITH RECURSIVE함수를 활용해서 가상의 테이블을 만들어 주고,

이 테이블과 기존 테이블을 LEFT JOIN 해서

시간(HOUR)과, 시간대별 입양 숫자(COUNT)를 계산하면 된다.

WITH RECURSIVE NEW_TIME AS
(
    SELECT 0 T
    
    UNION ALL
    
    SELECT T+1
    FROM NEW_TIME
    WHERE T<23
)

SELECT NEW_TIME.T HOUR, COUNT(ANIMAL_ID) COUNT
FROM NEW_TIME LEFT JOIN ANIMAL_OUTS A ON NEW_TIME.T = HOUR(A.DATETIME)
GROUP BY HOUR
ORDER BY HOUR