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

프로그래머스 문제를 풀다가.. 띠용 했다.

 

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

코딩테스트 연습 - 입양 시각 구하기(2)

이 문제였는데, 주어진 테이블로 SELECT 연산을 하면 0~23시 데이터 중 빈 데이터가 있어 모든 시간마다 column이 생성되지 않아 정답이 틀렸다고 나왔다.

 

여기서 혼란이 왔다.

아니 애초에 테이블에 정보가 없는 데이터는 SELECT 으로 나올 수가 없고

그 정보를 가진 테이블이랑 JOIN을 해야 할 텐데 어떻게 만들지?

 

질문하기 눌러서 탐색해보다가 갓-답변을 발견했다.

 

깔끔쓰한 해결책은 바로 WITH RECURSIVE 구문 사용하기!

---

WITH RECURSIVE

 

mysql에서 제공하는 WITH RECURSIVE 구문은 말 그대로 재귀함수처럼 동작하는 친구이다.

 

구성은 다음과 같다.

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

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

이게 뭐람? 나도 그랬다.

 

근데 공식 홈페이지에 나온 예시를 보면 대충 눈치를 챌 수 있다.

 

mysql 공식 홈페이지 예제

예제를 살펴보자.

 

일단 recursive 구문을 통해 생성할 뷰 이름은 cte 이고, cte에는 n이라는 column이 존재한다.

 

그리고 SELECT 1 구문을 통해 딱 1이 담긴 initial row set 하나를 생성한다.

 

여기까지 하면 cte 테이블의 형태는

n

1

과 같을 것이다.

 

이제 아래의 반복문을 실행하면서 나온 결과를 모두 UNION ALL 하게 되는데,

이 반복문의 뜻은 n이 가지고 있는 직전 row set 값이 5보다 작을 때, n+1인 row set을 하나 만든다는 것이다.

 

고로 initial row set이 위의 표와 같을 때,

첫번째 반복문에서는 n = 1이므로 반복문을 통해 아래의 row set이 생성될 것이다.

n

2

그럼 얘랑 아까 걔랑 UNION ALL 하면

n

1

2

이 되는 것이다!

 

이걸 직전 row set 값이 4일때까지 반복하므로, 결과적으로 1~5값이 담긴 cte 뷰가 생성된다.

 

이제 얘를 이용해서 HOUR가 0~24인 뷰를 만들고, 걔랑 주어진 데이터를 JOIN 하면 된다.

---

풀이

 

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(AHIMAL_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)를 세도록 하였다.