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

문제

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

 

내가 푼 쿼리

단순하게 생각하면, 아래와 같이 접근하면 된다.

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

하지만 이렇게 풀면, 집계 함수에 포함되지 않은 시간이 출력되지 않기 때문에 문제를 풀 수 없다. 즉, 입양이 0건인 시간대가 출력되지 않는 것이 문제이다.

WITH HOUR_TABLE AS(
    SELECT
        0 AS HOUR
UNION
    SELECT
        1 AS HOUR
UNION
    SELECT
        2 AS HOUR
UNION
    SELECT
        3 AS HOUR
UNION
    SELECT
        4 AS HOUR
UNION
    SELECT
        5 AS HOUR
UNION
    SELECT
        6 AS HOUR
UNION
    SELECT
        20 AS HOUR
UNION
    SELECT
        21 AS HOUR
UNION
    SELECT
        22 AS HOUR
UNION
    SELECT
        23 AS HOUR
), ANIMAL_OUTS_DATE AS(
SELECT
    ANIMAL_ID,
    HOUR(DATETIME) AS HOUR
FROM ANIMAL_OUTS
)

SELECT
     SUB.HOUR,
     COUNT(ANIMAL_ID) AS "COUNT"
FROM(
SELECT
    HOUR(DATETIME) AS HOUR
FROM ANIMAL_OUTS
UNION
SELECT
    HOUR
FROM HOUR_TABLE) SUB
LEFT JOIN ANIMAL_OUTS_DATE
ON SUB.HOUR = ANIMAL_OUTS_DATE.HOUR
GROUP BY HOUR
ORDER BY HOUR;

 

문제를 해결하기 위해 테이블에서 입양이 발생하지 않은 시간을 확인하고, 해당 시간을 별도의 테이블로 만들어 문제를 해결했다. 정답은 맞지만, 어떤 의미로는 하드 코딩으로 풀었고 굉장히 비효율적이라는 생각이 들었다. 더 효율적인 쿼리를 구성하기 위해 구글링을 해보자.

 

재귀 함수

-- 출처 : MySQL 공식문서

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  -- Anchor
  SELECT 1, 0, 1 
  UNION ALL
  -- 재귀 멤버 (재귀 부분)
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

 

구글링 결과, MySQL 공식문서에서 recursive common table expressions 라는 개념을 확인했다. 즉, 자기 자신을 참조하여 반복적으로 데이터를 생성하거나 계층적으로 데이터를 처리하는 기능이다. 작동 원리를 정리해보자.

 

Anchor : 재귀 처리를 시작하기 위한 초기 값으로, 한 번 실행되어 기본 결과 집합을 생성

재귀 멤버 : Anchor 또는 이전 재귀 단계에서 생성된 결과를 기반으로 새로운 행을 생성, 재귀 멤버 내부의 WHERE 조건을 통해 더 이상 새로운 행이 생성되지 않을 때 재귀가 종료된다.

 

1. Anchor 멤버가 실행되어 초기 집합(예: 1)이 생성
2. 재귀 멤버가 실행되어 이전 결과(예: 1)를 이용해 1을, 그 다음 2를, ... 식으로 결과 집합을 확장
3. WHERE 조건(예: n< 10)이 더 이상 참이 아닐 때, 재귀가 중지되고 모든 결과가 UNION(혹은 UNION ALL)되어 최종 결과 집합을 반환

 

수정된 쿼리

WITH RECURSIVE HOUR_TABLE (HOUR) AS(
    SELECT 
        0 
    UNION ALL
    SELECT
        HOUR+1
    FROM HOUR_TABLE
    WHERE HOUR < 23
),
ANIMAL_OUTS_HOUR_TABLE AS(
    SELECT
        ANIMAL_ID,
        HOUR(DATETIME) AS HOUR
    FROM ANIMAL_OUTS
)

SELECT
    H.HOUR,
    COUNT(A.ANIMAL_ID) AS "COUNT"
FROM HOUR_TABLE H
LEFT JOIN ANIMAL_OUTS_HOUR_TABLE A
ON H.HOUR = A.HOUR
GROUP BY H.HOUR
ORDER BY H.HOUR;

같은 결과를 도출하지만, 재귀 CTE를 활용하여 가독성과 효율성이 크게 향상되었음을 확인할 수 있다. 추가적인 정보는 아래 첨부한 공식 문서를 참고해보시면 도움이 될 것이라 생각한다.

 

MySQL :: MySQL 8.4 Reference Manual :: 15.2.20 WITH (Common Table Expressions)