오늘 차, MySQL 문제를 풀어보다 생전 처음보는 문법을 쓰게 되는 것 같아 기록을 조금 더 디테일 하게 남겨보려고 한다.
문제와 테이블은 다음과 같다.
1. 입양 시각 구하기(2)
테이블 설명 : ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.
문제 설명 : 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
처음에는 생각보다 쉬울 거라는 생각에 저장된 데이터를 생각하지도 않고 다음과 같은 QUERY를 작성하고 답안 제출을 하였다.
SELECT
HOUR(DATETIME) AS HOUR,
COUNT(ANIMAL_ID)
FROM ANIMAL_OUTS
GROUP BY HOUR
ORDER BY HOUR하지만...결과는 역시 틀렸고 그 이유를 찾다보니...문제에서 원하는 답변은 저장되어 있지 않은 시간대에 대해서도 출력을 해야 되는 것 같았다. 즉, 보호소에서 0시에 입양된 동물이 없다고 하더라도 0시라는 HOUR 값과 그 시간 대 동물 입양 수인 0을 출력해야 된다는 얘기다...근데..저장되어 있는 값이 아닌데..어떻게 하지라는 생각에 구글링을 하다..한 번도 활용해보지 못한 문법이 나와 이번 기회에 공부 해볼까 한다.
WITH...EXPRESSION
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 discussion describes how to write statements that use CTEs.
쉽게 설명해, 하나의 전체적인 쿼리문 내에서 나중에 사용하기 위해 일시적으로 설정한 테이블이라고 생각하면 된다.
위 문제를 빌려와 설명하자면, 0~23시 까지의 시간 별 데이터가 없기에 일시적으로 0~23시가 포함되어 있는 테이블을 만들어줘야 한다.
-> 하지만 독단적으로 쓰이지는 않고, 테이블을 사용하려면 무조건 참조 값으로 사용 되어야 한다.
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...Notation을 짚고 넘어가자면..
- cte_name : 일시적으로 만들어진 테이블 명
- col_name : 열 이름
- subquery : 서브쿼리.
상기 문법으로 공식 문서에 있는 테이블을 하나 해석해보자.
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;위에서 보면 WITH~ SELECT b,d 전 까지가 WITH 절이라고 보면 되는데..임시 테이블을 만들 때에도 여전히 데이터는 필요하기에 서브 쿼리를 사용하여 이미 저장되어 있는 테이블을 가져와서 가상의 테이블을 만드는 것이다 (만드는 과정을 보면 서브쿼리랑 똑같은 역할을 하는 것 같다 하지만 가독성!! 하나 만큼은 서브쿼리 보다 나은 것 같다).
저렇게 WITH 절로 임시 테이블을 만들어 놓은 뒤, 바로 밑에 있는 SELECT문에서 위에서 만든 테이블을 참조해 사용했다.
생각해보면 간단하다. 서브쿼리를 위로 끌어다 언제든지 사용 할 수 있게 만들어 놓은 것이기 때문이다.
자 임시테이블을 만들 줄 알게 되었으면 우리는 임시 테이블에 데이터를 추가하기 위해서 다음의 문법을 WITH 절에 추가로 활용 할 것이다.
WITH RECURSIVE...EXPRESSION
RECURSIVE라는 표현 답게 임시 테이블 내에서 특정 패턴을 반복하여 데이터를 생성하는 것이이다.
다음의 예제를 보자
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;위에 써져 있는 걸 문장 그대로 해석해보면 cte내에 쓰이는 n 값잉 1부터 5미만이 될 때 까지 1씩 더해지면서 늘어나고 그 늘어난 값들이 1과 합쳐져서 하나의 테이블이 되는 것이다.
그렇기에 저 테이블 밑에 SELECT * FROM cte를 실행하면 다음과 같은 결과 값을 얻을 수 있다.
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+자 그러면 이제.. 주역 배우들은 다 모인 것 같으니 모든 개념을 짜집기 해서 답을 만들어 보자.
정답
WITH RECURSIVE first_table AS (
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR+1 FROM first_table WHERE HOUR < 23
) , second_table AS (
SELECT HOUR(DATETIME) AS HOUR
, COUNT(DISTINCT ANIMAL_ID) AS CNT
FROM ANIMAL_OUTS
GROUP BY HOUR
)
//// 위에가 WITH CLAUSE /// 밑이 테이블 조회 구문
SELECT first_table.HOUR
, CASE WHEN second_table.CNT IS NULL THEN 0 ELSE CNT END AS CNT
FROM first_table
LEFT JOIN second_table ON first_table.HOUR = second_table.HOUR
ORDER BY HOUR보면 구역을 잘 쪼개어 놨는데, 위에서 WITH RECURSIVE 문법을 활용해서 0~23까지의 데이터 테이블을 하나 만들어 주고 밑에서 쉽게 활용하기 위해서 서브쿼리 형태로 써도 되지만 조금 더 가독성을 좋게 하기 위해 내가 처음에 작성했던 코드를 가상의 테이블로 하나 짜주고 밑에서 LEFT JOIN (NULL 값도 출력해야 하기에) 두 개의 테이블을 붙여 결과 값을 출력했다.
알아두면 요긴 할 만한 WITH!!! 시간 날 때 (아마..안나겠지??) 기존에 만들어 두었던 서브쿼리들을 WITH 바꿔보는 것도 나쁘지 않을 것 같다 (정말?)
매번 같은 문법을 사용하다 이번에 새로운 것을 배우다 보니 역시 신박하고 재미있다.
