[Programmers] WITH RECURSIVE(재귀) 쿼리 계층구조 - 입양 시각 구하기(2) [MySQL 풀이]

WITH 구문 

• 가상의 임시 테이블 저장할 때 사용 
• SQL 쿼리를 작성할 때, 서브쿼리가 중첩되는 경우 가독성을 고려해서 WITH 구문으로 임시 테이블을 만들 때 주로 사용합니다. 

 

WITH RECURSIVE 구문 

• 재귀(반복) 쿼리를 이용하여 INSERT 삽입 기능을 하지 않아도 가상 테이블을 생성할 수 있습니다. 
• 쿼리 작성 예시) 

WITH RECURSIVE Table AS (
	SELECT 초기값 AS 컬럼명
    UNION ALL
    SELECT 컬럼명 계산식 
    FROM Table
    WHERE 제어문
)

위와 같이 쿼리를 작성할 수 있는데요,

초기값으로 1을 가지면서 5까지의 값을 가지는 테이블을 생성해보는 쿼리는 다음과 같습니다. 

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 
  FROM cte 
  WHERE n < 5
)
SELECT * 
FROM cte;

결과 값: 

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

참고 자료: 

MySQL :: MySQL 8.0 Reference Manual :: 13.2.20 WITH (Common Table Expressions)

 

---

활용 문제 풀이

프로그래머스 - 입양 시각 구하기(2) Level 4 

문제 설명

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

---

처음 시도한 풀이 (CASE WHEN 구문을 활용한 노가다)

-- 구할 값: 0시부터 23시까지, 각 시간대별로 입양 건 수
-- 정렬 조건: 시간대 순(오름차순)

SELECT CASE 
            WHEN DATE_FORMAT(datetime, '%H') = 0 THEN 0
            WHEN DATE_FORMAT(datetime, '%H') = 1 THEN 1
            WHEN DATE_FORMAT(datetime, '%H') = 2 THEN 2
            WHEN DATE_FORMAT(datetime, '%H') = 3 THEN 3
            WHEN DATE_FORMAT(datetime, '%H') = 4 THEN 4
            WHEN DATE_FORMAT(datetime, '%H') = 5 THEN 5
            WHEN DATE_FORMAT(datetime, '%H') = 6 THEN 6
            WHEN DATE_FORMAT(datetime, '%H') = 7 THEN 7
            WHEN DATE_FORMAT(datetime, '%H') = 8 THEN 8
            WHEN DATE_FORMAT(datetime, '%H') = 9 THEN 9
            WHEN DATE_FORMAT(datetime, '%H') = 10 THEN 10
            WHEN DATE_FORMAT(datetime, '%H') = 11 THEN 11
            WHEN DATE_FORMAT(datetime, '%H') = 12 THEN 12
            WHEN DATE_FORMAT(datetime, '%H') = 13 THEN 13
            WHEN DATE_FORMAT(datetime, '%H') = 14 THEN 14
            WHEN DATE_FORMAT(datetime, '%H') = 15 THEN 15
            WHEN DATE_FORMAT(datetime, '%H') = 16 THEN 16
            WHEN DATE_FORMAT(datetime, '%H') = 17 THEN 17
            WHEN DATE_FORMAT(datetime, '%H') = 18 THEN 18
            WHEN DATE_FORMAT(datetime, '%H') = 19 THEN 19
            WHEN DATE_FORMAT(datetime, '%H') = 20 THEN 20
            WHEN DATE_FORMAT(datetime, '%H') = 21 THEN 21
            WHEN DATE_FORMAT(datetime, '%H') = 22 THEN 22
            WHEN DATE_FORMAT(datetime, '%H') = 23 THEN 23
            END AS hour
     , CASE 
            WHEN COUNT(*) = 0 THEN 0
            ELSE COUNT(*)
            END AS count
FROM animal_outs
GROUP BY hour
ORDER BY hour

처음에는 SQL 쿼리를 작성할 때, 반복문이 없다고 생각해서 노가다로 해결하려 했지만 

'입양 건수가 0건'인 경우에는 값이 출력되지 않아서 난항을 겪었습니다. 

 

그래서, 다음과 같이 해결했습니다. 

1. WITH RECURSIVE 구문으로 시간대 테이블 생성 (0 ~ 23)

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

2.  시간대별 입양 건수가 존재하는 테이블과 LEFT JOIN 하여 해결 

-- 구할 값: 0시부터 23시까지, 각 시간대별로 입양 건 수
-- 정렬 조건: 시간대 순(오름차순)

WITH RECURSIVE time AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 
    FROM time 
    WHERE hour < 23
), animal AS (
    SELECT HOUR(datetime) AS hour
         , COUNT(*) AS count
    FROM animal_outs AS a 
    GROUP BY hour
    ORDER BY hour
)

SELECT time.hour
     , CASE WHEN animal.count IS NULL THEN 0 ELSE animal.count END AS count
FROM time
    LEFT JOIN animal ON time.hour = animal.hour
GROUP BY time.hour
ORDER BY time.hour

• NULL 값이 존재하기 때문에 LEFT JOIN을 시켰습니다. 
• 또한, 입양 건수가 존재하지 않는 경우(NULL) 0을 출력하는 조건문을 사용하여 0시부터 23시까지 모든 시간대별로 입양 건수를 집계하는 쿼리를 완성할 수 있었습니다. 

 

---

프로그래머스 SQL 고득점 kit 문제를 풀면서 코딩테스트 연습을 하고 있는데, 

개인적으로 LEVEL 2는 별로 막힘이 없는데, LEVEL 3, 4 정도의 난이도는 상당한 시간을 고민하고 문제를 해결해야 하는 것 같습니다.. 

갈 길이 멀지만, 앞으로 배울 것도 참 많아 보입니다. 

 

다음 글에서 뵙겠습니다!