[MySQL] 임의의 가상 테이블 만들기 (WITH, WITH RECURSIVE), CTE

WITH 문과 WITH RECURSIVE는 테이블을 직접 만들 수 없는 상황에서 쿼리 안에 가상의 테이블을 만들어 JOIN 등을 쉽게 하도록 한다. 

 

현업에서 WITH 문을 자주 썼는데, CTE(Common Table Expression)이라고 부르는 것은 오늘 처음 알았다.

 

가상의 테이블은 남발하면 쿼리의 작동 속도를 늦춰 권장하지 않는 방법이라고 적는 글도 왕왕 보았지만, 전에 있던 회사에서는 테이블이 이미 너무 많은 상황 + 스키마 정리가 되어있지 않아서 WITH문을 사용한 임시 테이블 생성 및 SQL을 통한 TABLE 전처리 (Python Pandas가 아닌 SQL로도 할 수 있다는 것을 처음 알았다.)를 곧잘 사용했다.

 

주로, 엄청 많이 쓰일 것 같지는 않지만 가상의 테이블이 필요할 때 (자주 사용하면 아예 테이블로 만드는 것이 낫다)

임의의 정보를 생성하여 JOIN 해야할 때 

 

이렇게 두 가지 상황에서 많이 쓰이는 것 같다.

 

그리고 오늘 내가 소개할 방법도 프로그래머스 SQL 코딩 테스트 입양 시각 구하기(2) 를 기초로 한다.

 

시간대를 추출하여 그룹핑 하는 것을 어렵지 않으나, 입양이 이뤄지지 않은 시간대를 어떻게 처리하느냐가 관건이었다.

 

Programmers에서 진행했으므로 MySQL을 사용해서 쿼리를 작성하였다.

 

---

기본 Syntax 소개

 

1. WITH문

: 서브 쿼리에 이름을 붙여 테이블로 만들었다고 생각하면 편할 것 같다.

 

Syntax:

WITH 테이블 이름  AS (

#이하 서브 쿼리 영역

SELECT

FROM)

 

2. WITH RECURSIVE 

: RECURSIVE 즉, 자기 자신을 참조(재귀)하여 반복문처럼 사용 가능, 재귀함수처럼 사용되기 때문에 재귀를 멈춰줄 조건절이 필요하다. 주로 WHERE을 사용한다고는 하지만 조건인 만큼 CASE WHEN도 사용할 수 있을 것 같다. 

 

Syntax:

WITH RECURSIVE 테이블 이름 AS(

SELECT

UNION ALL

SELECT

FROM 

WHERE

) #반복쿼리 (반복해서 실행할 영역) 

 

출처: https://mine-it-record.tistory.com/447 [나만의 기록들:티스토리])

---

개인적으로 가장 정석적이고 가장 합리적이라고 생각했던 쿼리를 가져와서 약간 고쳤다. (By 류연식 님)

윈도 함수 ROW_NUMBER PARTITION OVER을 + LIMIT로 순위를 가져와서 숫자만 있는 테이블을 생성하는 것까지는 생각이 닿았지만, 왠지 실제 쿼리를 돌려보니 순위 값이 모두 1로만 나와서 (ROW_NUMBER는 순위 중복 허용을 안 하는데 왜 그렇게 나왔는 지는 모르겠다 ㅜ) 어떻게 타개할까 보던 중 가장 내가 해석 가능한, 사용 가능한 수준의 쿼리를 발견했다.

 

원문에서는 WITH RECURSIVE를 사용했지만, ROW_NUMBER를 매기는 마당에 재귀 할 것이 없다고 생각해서 WITH문으로 바꿔버림.

WITH TIMETABLE AS #TIMETABLE이라는 가상의 테이블 생성
(
SELECT ROW_NUMBER() OVER(ORDER BY DATETIME)-1 AS HOUR #윈도 함수를 통해 순위(숫자) 생성, 시작값이 1이므로 -1을 붙여줘서 0시~23시를 표현할 수 있게 되었다!
FROM ANIMAL_OUTS 
LIMIT 24 #값은 24개까지만 
)

위의 쿼리를 돌리면 HOUR 이라는 컬럼에 0 ~ 23까지의 값을 가진 테이블이 생성된다.

이후는 쉽다. LEFT JOIN으로 시간대를 붙여주면 끝.

값이 없는 시간대는 자동으로 0이 출력된다.

 

SELECT HOUR, COUNT(ANIMAL_ID) AS COUNT
FROM TIMETABLE
LEFT JOIN ANIMAL_OUTS
ON DATE_FORMAT(DATETIME, '%H') = TIMETABLE.HOUR
GROUP BY HOUR
ORDER BY HOUR

여기서 원래는 HOUR(DATETIME) 컬럼을 통해 JOIN으로 붙여줄 생각이었지만, ON에서 DATE_FORMAT을 사용해 시간대를 떼어낸 쿼리가 더 효율적이라는 판단이 들어 ON 절에서 만져줬다.

 

RECURSIVE를 사용할 만큼 복잡한 케이스는 아니게 되었지만, SQL에서 반복문처럼 사용할 수 있는 함수를 알게 된 만큼 다른 케이스에서 사용할 수 있었으면 좋겠다. (진지하게 노가다로 0부터 23까지 원 컬럼 테이블을 만들까도 생각했음)