문제
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 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 조건을 통해 더 이상 새로운 행이 생성되지 않을 때 재귀가 종료된다.
- Anchor 멤버가 실행되어 초기 집합(예: 1)이 생성
- 재귀 멤버가 실행되어 이전 결과(예: 1)를 이용해 1을, 그 다음 2를, ... 식으로 결과 집합을 확장
- 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)
15.2.20 WITH (Common Table Expressions) A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times. The following disc
dev.mysql.com
'코딩 테스트 > SQL' 카테고리의 다른 글
| [MYSQL] 물고기 종류 별 대어 찾기 (0) | 2025.03.29 |
|---|---|
| [MYSQL] 상품을 구매한 회원 비율 구하기 (0) | 2025.02.27 |
| [MYSQL] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (0) | 2025.02.04 |
| [MYSQL] 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 (0) | 2024.11.04 |
| [MySQL] 프로그래머스 - 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기 (0) | 2024.06.25 |