SQL로 CTE를 표현하는 WITH 활용하기

SQL에서 CTE(Common Table Expression) 표현법인 WITH 구문 사용법을 알아본다.

테스트를 위해서 Bike Sharing Demand를 활용하였다. bike 테이블에는 컬럼이 12개, 행이 10886개가 존재한다.

CTE 간단 설명

CTE(Common Table Expression)는 서브쿼리로 쓰이는 파생테이블(derived table)과 비슷한 개념으로 사용된다.

CTE와 비교대상으로는 VIEW가 있다. VIEW는 만들기 위해 권한이 필요하고 사전에 정의를 해야한다. 반면, CTE는 권한이 필요 없고 하나의 쿼리문이 끝날때까지만 지속되는 일회성 테이블이다.

CTE는 주로 복잡한 쿼리문에서 코드의 가독성과 재사용성을 위해 파생테이블 대신 사용하기에 유용하다.

CTE에는 재귀적 CTE와 비재귀적 CTE가 있지만 여기서는 다루지 않는다.

참고 : MySQL은 8.0부터 지원한다.

CTE 활용 예시

CTE 기본 활용

사용방법 : WITH 테이블명 AS (SELECT ...)

시계열 데이터의 경우, timstamp 타입에서 연,월,일 등을 따로 컬럼으로 추출하여 활용하는 것이 분석에 도움이 될 수 있다.

미리 추출한 테이블을 CTE로 정의하니 서브쿼리를 활용하는 것보다 SQL문이 깔끔해졌다. 내가 필요한 컬럼과 데이터를 미리 정의해 놓고 활용할 수 있어서 효율적인 쿼리를 작성하는 데도 유용하다.

WITH hourly_bike AS(
	SELECT EXTRACT(YEAR FROM datetime) as year,
		EXTRACT(MONTH FROM datetime) as month,
		EXTRACT(DAY FROM datetime) as day,	
		EXTRACT(HOUR FROM datetime) as hour, count FROM bike)

SELECT month, SUM(count) as toal_count FROM hourly_bike GROUP BY month ORDER BY month;

컬럼명 지정 가능

테이블명 옆에 컬럼명을 새로 지정하여 사용할 수 있다. 가독성이 더 높을 수 있다.

WITH hourly_bike(year, month, day, hour, count) AS(
	SELECT EXTRACT(YEAR FROM datetime),
		EXTRACT(MONTH FROM datetime),
		EXTRACT(DAY FROM datetime),	
		EXTRACT(HOUR FROM datetime), count FROM bike)

여러 테이블 정의

여러 개의 테이블을 정의하려면 WITH .. AS ( ) 뒤에 ,를 붙이고 새로운 테이블을 정의하면 된다.

WITH hourly_bike(year, hour, count) AS(
        SELECT EXTRACT(YEAR FROM datetime),
	        EXTRACT(HOUR FROM datetime),count FROM bike), 
    monthly_bike(year, month, count) AS(
        SELECT EXTRACT(YEAR FROM datetime),
	        EXTRACT(MONTH FROM datetime), count FROM bike )

SELECT * FROM monthly_bike LIMIT 5;

CTE 사용 시 주의할 점

MySQL 8과 PostgreSQL에서는 CTE를 Materializing한다. 테이블이 cache처럼 임시로 저장된다는 의미이다.

( PostgreSQL 12부터는 Materializing을 선택 가능 )

이로 인해 CTE를 무분별하게 사용할 경우, Query performance가 오히려 더 떨어질 수 있다. Subquery 형태로 사용하는 것이 나을 수도 있다.

Query Optimizer가 execution plan을 계산할 때 CTE의 최적화를 고려하지 않고 결과를 그대로 이용한다. 예로, 설정한 index가 CTE에서는 적용되지 않게 된다.

CTE를 생성할 때 필요한 데이터를 미리 필터링하는 등 최적화를 고려한 쿼리가 필요할 수 있다.

참고 : Oracle에서는 기본적으로 Materialzing을 하지 않아 CTE도 Query Optimizing에 관여한다.

References :

• PostgreSQL CTE

• Be careful with CTE in PostgreSQL