CTE

사내에서 대시보드 관련 집계 쿼리를 짜면서 단순한 테이블 조회가 아닌 필요한 데이터를 뽑아 그 내에서 또 추출하는 작업을 진행했다. 이를 진행하면서 서브쿼리가 아닌 CTE를 활용했는데 이를 정리하려고 한다.

 

정의

CTE, Common Table Expression의 줄임말이다. 파생 테이블이라는 개념으로 생각하면 된다. CTE를 비교하려면 서브쿼리보다는 데이터베이스의 뷰와 비교를 해볼 수 있는데 뷰는 사전에 정의해서 권한을 부여하고 작성하는 반면에 CTE는 1회성으로 권한이 필요하지 않고 쿼리문이 끝날 때까지 지속되는 일회성 테이블이다.

 

활용 예시

사내에서 작성했던 코드말고 내가 자주했던 게임의 데이터를 서버장으로부터 받았던 적이 있어 이를 가지고 비교해보겠다.

 

WITH map_players_counts AS (
	SELECT
		map,
		COUNT(*) AS total_players
	FROM
		playertimes
	WHERE
		style = 0
	GROUP BY
		map
),
rank_records AS (
	SELECT
		RANK() OVER (PARTITION BY p.map ORDER BY p.time ASC) AS ranking,
		u.name,
		mpc.total_players,
		p.time,
		p.map,
		p.jumps,
		p.strafes,
		p.sync
	FROM 
		playertimes p
	LEFT JOIN
		map_players_counts mpc ON p.map = mpc.map
	LEFT JOIN 
		users u ON u.auth = p.auth 
	WHERE 
		p.style = 0
)
SELECT
	r.ranking,
	r.total_players,
	r.name,
	r.map,
	r.time,
	r.jumps,
	r.strafes,
	r.sync
FROM 
	rank_records r
WHERE 
	r.name = 'opacity'

해당 쿼리는 'opacity' 라고 하는 유저가 자신의 클리어했던 정보를 조회하는 쿼리이다. 단순히 클리어 기록뿐 아니라 해당 맵의 등수와 전체 클리어한 수까지도 조회하기 위해서는 부가적인 쿼리 조회가 필요했다.

 

위에 보이는 WITH ... AS ()가 CTE 사용하는 방법이다. 임시로 사용할 테이블의 이름을 적고 AS 뒤에 그 쿼리문을 적으면 된다. 방법은 어렵지 않다.

 

주의할 점

CTE를 사용하지 않고 서브쿼리로 작성해보면 가독성이 조금 떨어지는 편이다. 또한, 쿼리의 속도에서도 차이가 날 수 있다. 단순히 가독성이 좋다는 이유로 무분별하게 사용하면 오히려 서브쿼리 때보다 속도가 떨어질 수도 있기에 알맞은 방식으로 진행하는 것이 좋다.

 

참고로 CTE는 mysql 8버전 이상부터 가능하다.