[SQL][PGS] 입양 시각 구하기(2)

오늘은 프로그래머스 SQL문제에서 입양 시각 구하기(2) 에 대해서 리뷰해보려고 한다.

 

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

프로그래머스

 

문제 

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

NAMETYPENULLABLE

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문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

 

예시

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

 

첫 번째 풀이 - MySQL WITH RECURSIVE, LEFT JOIN

첫 번째로 문제를 어떻게 접근해야 할지 다음과 같이 생각을 했다. 

 

1. 0시부터 23시까지 고정적으로 출력해야 한다.
   • 예시에서 HOUR이 0일 때는 COUNT가 0 이어도 나와야 하는 것에서 유추했다.
2. 각 시간대별로 입양이 몇 건이나 발생했는지 조회
   • HOUR를 기준으로 GROUP BY를 걸어서 그룹화를 해야 한다.
3. 결과는 시간대 순으로 정렬(ASC)

 

이 문제의 키 포인트는 0 ~ 23시를 어떻게 만들어내는지가 중요한 문제 같았다.

MySQL에서는 변수를 사용한 방법도 있는데, 변수를 사용하는 방법보다는 WITH RECURSIVE 구문을 사용하는 게 익숙해서 해당 구문을 사용했다.

 

1. WITH RECURSIVE로 0  ~ 23 HOUR를 만들기 

MySQL의 WITH RECURSIVE 구문은 CTE(Common Table Expression)의 방법 중의 하나이다.

CTE란 테이블의 스키마를 하드 디스크에 저장하지 않고, 쿼리가 돌아가는 동안 메모리 상에서만 사용하고 사라지는 휘발성 구문이다.

 

첫 번째로 SELECT 0 AS HOUR로 한 번만 출력되는 0을 만들어 준다. 

 

RECURSIVE 구문에서 필수적인 예약어로 UNION ALL은 재귀적으로 반복될 하단의 출력문을 합쳐준다.

 

SELECT 1 + TB_HOUR.HOUR 는 첫 번째  SELECT문인 SELECT 0 AS HOUR에서 약어로 선언한 HOUR를 계속해서 1을 더해주는 재귀 구문이다.

 

WHERE TB_HOUR.HOUR <  23 에서는 TB_HOUR.HOUR가 <  23보다 작을 때까지 반복한다. 

SELECT절보다는 WHERE절이 먼저 들어오니 22 <  23인 구문까지 반복하고 SELECT 1 + 22로 23이 만들어지고 나서 다시 마지막 재귀 문인 WHERE 조건절에 23 < 23이 들어오면 break로 종료된다.

 

WITH RECURSIVE TB_HOUR AS 
(
    SELECT  0 AS HOUR         -- 첫 번째 출력(non - recursive)
    UNION   ALL
    SELECT  1 + TB_HOUR.HOUR  -- recursive
    FROM    TB_HOUR
    WHERE   TB_HOUR.HOUR < 23 -- break condition
)
SELECT	*
FROM	TB_HOUR

 

TB_HOUR에 출력되는 결과를 보면 다음과 같이 0 ~ 23시가 나온다.

 

 

0 ~ 23시의 고정된 시간만 출력이 가능하면 JOIN 구문은 단순하게 해결이 가능하다.

0 ~ 23시를 고정적으로 출력하기 위해서 TB_HOUR를 기준 테이블로 LEFT JOIN을 했다.

ANIMAL_OUTS 테이블과 HOUR가 같은 것들을 JOIN 하고 시간대별 입양 건수를 출력하는 것이기에 그룹화(GROUP BY)도 HOUR를 기준으로 했다. 

 

WITH RECURSIVE TB_HOUR AS 
(
    SELECT  0 AS HOUR
    UNION   ALL
    SELECT  1 + TB_HOUR.HOUR
    FROM    TB_HOUR
    WHERE   TB_HOUR.HOUR < 23
)

SELECT  T_H.HOUR, COUNT(B.ANIMAL_ID) AS COUNT
FROM    TB_HOUR T_H LEFT  JOIN ANIMAL_OUTS B
ON      T_H.HOUR = HOUR(B.DATETIME)
GROUP   BY T_H.HOUR
ORDER   BY T_H.HOUR ASC

 

두 번째 풀이 - Oracle의 CONNECT BY, LEFT JOIN

MySQL에서 WITH RECURSIVE 구문으로 재귀적으로 사용을 한다면 Oracle에서는 CONNECT BY구문을 통해서 계층형 쿼리를 통해서 사용한다.

 

여기서는 계층형 쿼리에서 대해서 상세하게 다루지는 않을 것이다.

CONNECT BY와 LEVEL에 대해서만 간략하게 알아본다.

CONNECT BY 절은 루트 노드에서 하위 노드를 생성하며 조회 결과가 없을 때까지 반복적으로 수행하는 구문이다.

LEVEL은 계층형 쿼리에서 지원해주는 가상 칼럼이다. 

MySQL과 동일하게 0 ~ 23의 데이터가 출력이 된다.

 

LEFT JOIN도 동일하다.

한 가지 다른 점은 MySQL에서는 날짜를 HOUR() 함수를 사용해서 시간만 가져올 수 있지만 Oracle에서는 지원되지 않는다... 벤더사마다 너무 다르다ㅠ

 

Oracle에서는 TO_CHAR(DATETIME, 'HH24')를 통해서 DATETIME에서 시간만 추출이 가능하다.

WITH TB_HOUR AS 
(
    SELECT  LEVEL - 1 AS HOUR
    FROM    DUAL
    CONNECT BY LEVEL <= 24
)
SELECT  T_H.HOUR
      , COUNT(B.ANIMAL_ID) AS COUNT
FROM    TB_HOUR T_H LEFT JOIN ANIMAL_OUTS B
ON      T_H.HOUR = TO_CHAR(B.DATETIME,'HH24')
GROUP   BY T_H.HOUR
ORDER   BY T_H.HOUR ASC

 

후기

MySQL에서는 WITH RECURSIVE, Oracle에서는 CONNECT BY와 LEVEL로 고정적인 데이터를 만들어서 해결해볼 수 있는 좋은 문제였다.