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

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

문제 설명

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

---

문제풀이

• ANIMAL_OUTS 테이블에서
• DATETIME 컬럼의 HOUR 를 기준으로 GROUP BY
• HOUR 함수를 사용해 DATETIME 컬럼의 시간만 조회, 그리고 DATETIME 에 대해 그 갯수를 조회하고
• 마지막으로 HOUR(DATETIME) 기준으로 오름차순 정렬!

코드

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

결과:

HOUR(DATETIME)	COUNT
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

결과를 보면 알 수 있듯이 값이 존재하는 시간에 대해서만 COUNT 해서 문제의 의도와는 다르게 출력되는 것을 확인할 수 있다. 따라서, 존재하지 않는 시간에 대해 0 으로 나타나도록 수정을 해야한다.

그렇다면 없는 값들을 포함해서 COUNT 를 할 수 있는 방법은 뭐가 있을까? 이에 대한 답으로 두가지 방법을 찾을 수 있었다.

방법1 - 사용자 정의 변수를 사용:

전체 코드:

SET @hour := -1;

SELECT (@hour := @hour + 1) AS HOUR,
    (SELECT COUNT(*)
     FROM ANIMAL_OUTS
     WHERE HOUR(DATETIME) = @hour) AS COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23
GROUP BY HOUR
ORDER BY HOUR;

설명:

1. 변수 초기화

   SET @hour := -1;

   • @hour 라는 사용자 정의 변수를 -1 로 초기화한다. (각 시간대를 나타내기 위해 사용된다.)

2. 메인쿼리

   SELECT (@hour := @hour + 1) AS HOUR,
       (SELECT COUNT(*)
        FROM ANIMAL_OUTS
        WHERE HOUR(DATETIME) = @hour) AS COUNT
   FROM ANIMAL_OUTS
   WHERE @hour < 23
   GROUP BY HOUR
   ORDER BY HOUR;

   • ANIMAL_OUTS 테이블에서
   • @hour 이 23 미만인 조건으로
   • HOUR 를 기준으로 GROUP BY
   • @hour 변수를 1씩 증가시키며, HOUR 컬럼으로 SELECT
   • 서브쿼리:

     (SELECT COUNT(*)
      FROM ANIMAL_OUTS
      WHERE HOUR(DATETIME) = @hour)

     • ANIMAL_OUTS 테이블에서
     • DATETIME 의 시간대가 현재 @hour 와 같은 행에 대해
     • 그 갯수를 COUNT 한다.

위 쿼리문의 핵심은 SET 명령과 사용자 정의 변수를 사용하는 것이다.

• SET: SET 명령어는 변수의 값을 설정하는 데 사용.
• 사용자 정의 변수: 사용자 정의 변수는 @ 기호로 시작하며, 사용 전에 반드시 초기화할 필요는 없지만, 초기화하지 않은 변수를 사용하면 NULL 값을 반환한다고 한다.

방법2 - 재귀적으로 시간대를 생성

전체 코드:

WITH RECURSIVE hours AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1
    FROM hours
    WHERE hour < 23
)
SELECT 
    h.hour AS HOUR,
    COALESCE(COUNT(a.ANIMAL_ID), 0) AS COUNT
FROM hours h
    LEFT JOIN ANIMAL_OUTS a 
    ON HOUR(a.DATETIME) = h.hour
GROUP BY h.hour
ORDER BY h.hour;

설명:

1. 재귀 CTE(Common Table Expression) 정의:

   WITH RECURSIVE hours AS (
       SELECT 0 AS hour
       UNION ALL
       SELECT hour + 1
       FROM hours
       WHERE hour < 23
   )

   • Anchor member:

     SELECT 0 AS hour

     • 시간대 0 을 시작점으로 설정.
   • Recursive member:

     SELECT hour + 1
     FROM hours
     WHERE hour < 23

     • 이전 시간대에서 1을 더한 값을 선택하여 hour 가 23 보다 작을 때까지 반복.

   이 재귀 CTE 는 0 부터 23 까지의 숫자를 생성하여 hours 라는 임시 테이블에 저장한다.

1. 메인쿼리

   SELECT 
       h.hour AS HOUR,
       COALESCE(COUNT(a.ANIMAL_ID), 0) AS COUNT
   FROM hours h
       LEFT JOIN ANIMAL_OUTS a 
       ON HOUR(a.DATETIME) = h.hour
   GROUP BY h.hour
   ORDER BY h.hour;

   • CTE 와 LEFT JOIN:

     FROM hours h
     LEFT JOIN ANIMAL_OUTS a ON HOUR(a.DATETIME) = h.hour

     • hours CTE 와 ANIMAL_OUTS 테이블을 hour 를 기준으로 조인.
   • COUNT 및 COALESCE:

     COALESCE(COUNT(a.ANIMAL_ID), 0) AS COUNT

     • 각 시간대에 대한 동물 ID 의 갯수를 세고, COALESCE 함수를 사용해 NULL 값은 0 으로 대체한다.
   • 그룹화 및 정렬:

     GROUP BY h.hour
     ORDER BY h.hour

     • 마지막으로 시간대별로 그룹화하고, 시간대 순으로 정렬한다.

이 쿼리문의 핵심은 WITH RECURSIVE 개념

• WITH RECURSIVE: 공통 테이블 표현식(CTE)의 일종으로, 재귀적인 쿼리를 작성할 때 사용한다. CTE는 쿼리 내에서 임시 결과 집합을 정의하고 이를 반복적으로 참조할 수 있는 구문이고, RECURSIVE 키워드를 사용하면 CTE 를 재귀적으로 호출할 수 있다고 한다.

개인적으로는 두 번째 방법이 조금 더 가독성이 좋아보이고 이해하기 쉬운것 같으나, 조금 더 고민이 필요할 거 같다. 간단한 문제라고 생각했는데, 생각보다 복잡하고 새로운 개념들에 대한 공부가 필요했던 문제였다.