문제 설명

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

NAME				TYPE		NULLABLE
ANIMAL_ID			VARCHAR(N)	FALSE
ANIMAL_TYPE			VARCHAR(N)	FALSE
DATETIME			DATETIME	FALSE
NAME				VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

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

예시

SQL문을 실행하면 다음과 같이 나와야 합니다.

HOUR	COUNT
0		0
1		0
2		0
3		0
4		0
5		0
6		0
7		3
8		1
9		1
10		2
11		13
12		10
13		14
14		9
15		7
16		10
17		12
18		16
19		2
20		0
21		0
22		0
23		0

접근 방법

우선 이 문제에서 사용한 기법에 대해 생각해보며 정리하려고 한다. 전에 프로그래머스 코테에서 시간별 카운팅을 못해서 틀렸던 경험이 생각났고, 앞으로의 코테를 준비하기 위해 이 문제를 풀어보았다.

• DATETIME 자료형에서 시간만 꺼내는 함수는 HOUR()이다.
• COUNT()함수를 사용할 때에는 GROUP BY 절을 잘 정의해 주는 것이 중요하다.
  • GROUP BY를 시간으로 하게 되면 그 시간에서의 COUNT()의 인자를 카운팅한다.
  • GROUP BY를 COUNT()의 인자로 하게 되면 그 인자가 몇개인지를 카운팅한다.
• CASE WHEN (조건) THEN (결과) WHEN (조건) THEN (결과), ... , ELSE (결과) END를 이용하여 조건절을 이용할 수 있다.
  • END를 뺴먹지 않도록 주의하자!
• WITH RECURSIVE (테이블명) AS (SELECT ... FROM ... WHERE ...) 재귀 테이블을 활용하여 반복문을 구현할 수 있다.

처음에는 간단하게 생각하고 문제에 접근하였다. 그래서 재귀 테이블을 사용하지 않고, 각 시간별 카운팅만 조회하였다.

SELECT HOUR(DATETIME) AS "HOUR", COUNT(ANIMAL_ID) AS "COUNT"
FROM ANIMAL_OUTS
GROUP BY HOUR
ORDER BY HOUR;

결과 값이 존재하는 시간만 조회되었다. 모든 시간에 대한 값을 조회하는 방법에 대해 찾아본 결과 재귀 테이블을 활용할 수 있다는 사실을 알게 되었다. 재귀 테이블을 사용하는 방법은 다음과 같다.

WITH RECURSIVE "테이블명" AS(SELECT ... FROM ... WHERE ...)

이러한 형태로 선언한 후에 테이블명을 이용하면 바로 사용 가능하다.

WITH RECURSIVE TIME AS
(SELECT 0 AS HOUR UNION ALL SELECT HOUR+1 FROM TIME WHERE HOUR<23)
SELECT A.HOUR AS HOUR, CASE WHEN B.COUNT IS NULL THEN 0 ELSE B.COUNT END AS COUNT
FROM TIME AS A
LEFT JOIN (SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
          FROM ANIMAL_OUTS
          GROUP BY HOUR) AS B
          ON A.HOUR=B.HOUR
ORDER BY A.HOUR

재귀 테이블을 정의할 때, 0을 먼저 HOUR로 넣어주고, UNION ALL을 통해 이후에 조회되는 값들을 붙여준다. UNION ALL 뒤에는 재귀 형식으로 TIME의 HOUR+1을 TIME에 넣게 된다. 이때 HOUR은 23까지만 생성된다. 이 과정을 통해 0시부터 23시까지의 HOUR컬럼을 생성하게 된다.

TIME테이블과 ANIMAL_OUTS에서 시간별 카운팅한 결과 테이블을 LEFT JOIN하여 TIME의 HOUR과 조인 테이블의 HOUR을 연결한다. 이렇게 되면 COUNT 값이 존재하지 않는 로우에는 값이 보여지지 않게 되므로 CASE WHEN을 통해 COUNT가 NULL일 경우 0을 보여주도록 작성하였다.