[MySQL] CTE Common Table Expression 임시 테이블

CTE

CTE는 Common Table Expression의 약자로 단일 쿼리 내부에서 임시로 결과를 저장해 놓고, 해당 쿼리 내에서 반복적으로 사용가능한 임시 결과 집합 테이블이다. 임시로 쿼리 결과를 저장해 놓고 여러번 참조해서 사용하는 용도로 사용한다. 메인 쿼리내에서 정의되어 사용된다는 점이 서브 쿼리와 비슷하지만 재사용이 가능하다는 점이 서브쿼리와 차이가 있다. 

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

 

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

 

 

기본 문법

CTE의 기본문법이다. WITH와 AS를 사용해 cte_name에 맵핑할 쿼리를 작성한 후 cte_name으로 지정한 테이블을 조회한다. 

WITH 테이블명 AS (SELECT ...)

WITH
	cte1 AS (SELECT a, b FROM table1_name),
    cte2 AS (SELECT c, d FROM table2_name)
SELECT b, d FROM cte1 JOIN cte 2
WHERE cte1.a = cte2.c;

 

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

ex) 

WITH kor_restaurants AS (SELECT * FROM restaurants WHERE rest_type = 'kor')

SELECT rest_name, rest_id FROM kor_restaurants

 

데이터베이스 사용자에게 view를 생성할 권한이 없을 수도 있기 때문에 권한을 요구하지 않는 CTE가 사용이 편리하다. 그리고 생성된 임시 테이블을 메인 쿼리에서 여러번 참조할 수 있기 때문에 더 빠르고 효율적이다. 

하지만 CTE를 무분별하게 사용할 경우 quer performance가 오히려 더 떨어질 수도 있다. Query optimizer가 excution plan을 계산할 때 CTE의 최적화를 고려하지 않기 때문에 CTE를 생성할 때 필요한 데이터를 미리 필터링하는 등 최적화를 고려한 쿼리 작성이 필요하다. 

 

컬럼명 지정 

테이블명  옆에 컬럼명을 새로 지정해서 사용할 수 있다. 

WITH hourl_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 FORM bike)

 

 

여러 테이블 정의

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

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

이렇게 하면 hourly_bike와 monthly_bike두개의 CTE, 즉 임시 테이블이 생성된다.