[SQL] CTE (공통테이블 표현식)

CTE

 

CTE는 쿼리를 통해 만들어낸 임시 Table이다.

 

CTE의 기본 문법은 아래와 같다.

WITH 테이블 이름 AS (테이블 만들 쿼리문)

 

CTE의 경우 WITH와 함께 사용된다. 임시 테이블을 가지고 우리가 원하는 결과를 얻기 위한 쿼리문을 가지고 작성하기 때문이다.

 

예를 들어. 아래와 같은 세개의 Table이 존재한다고 하자.

• 첫번째 Table: freelancer
  • id : 프리랜서의 아이디
  • first_name : 프리랜서의 이름
  • last_name : 프리랜서의 성
  • country : 프리랜서의 근무지
  • pay_rate : 시급
  • job_type_id : 직종 아이디 (job_position 테이블의 foreign key)
• 두번째 Table: hours_worked
  • id : hours_worked 테이블 내 데이터의 고유 아이디 
    
  • date : 일한 날짜
    
  • hours : 일한 시간
    
  • freelancer_id : 프리랜서의 아이디 (freelancer 테이블의 foreign key입니다)

• 세번째 Table: job_position
  • id : 직종 아이디
    
  • position_type : 직종 이름

 

우리는 위 Table에서 모든 프리랜서의 아이디, 이름, 시급, 그들의 직종, 그 직종의 평균 시급에 대해 알아보기 위해 쿼리를 날리려고 한다. 해당 쿼리는 아래와 같다.

SELECT f.id, f.first_name, f.last_name, f.pay_rate, 
       j.position_type, sq.average_job_rate
FROM freelancer AS f
LEFT JOIN job_position AS j
	   ON f.job_type_id = j.id
LEFT JOIN 
	  (SELECT j.id,
      		  AVG(f.pay_rate) AS average_job_rate
       FROM freelancer AS f
       LEFT JOIN job_position AS j
       		  ON f.job_type_id = j.id
       GROUP BY j.id) AS sq 
       		  ON j.id = sq.id;

 

위 쿼리문을 통해 우리가 원하는 결과를 얻었다. 그러나, 쿼리문에 대한 가독성이 매우 떨어짐을 확인할 수 있다.

 

논리적인 흐름을 유지하기 위해선 코드의 가독성은 필수적이다. 그런데 우리가 작성한 서브쿼리가 가독성을 매우 떨어뜨리는 것처럼 보인다. 여기서 CTE를 사용하여 서브쿼리 테이블을 따로 생성하고 쿼리에 대한 가독성을 향상시킬 수 있다.

 

아래는 CTE를 활용한 쿼리다. 

WITH average_rate_per_job AS(
  SELECT
   j.id,
   AVG(f.pay_rate) AS average_job_rate,
  FROM freelancer AS f
  LEFT JOIN job_position AS j
     ON f.job_type_id = j.id
  GROUP BY j.id)
 
 
SELECT f.id, f.first_name, f.last_name, f.pay_rate,
	  j.position_type, a.average_job_rate
FROM freelancer AS f
LEFT JOIN job_position AS j
   ON f.job_type_id = j.id
LEFT JOIN average_rate_per_job AS a
   ON f.job_type_id = a.id;

 

서브쿼리를 임시 테이블로 저장하여 사용하니 본 쿼리가 훨씬 가독성이 있게 보인다. 임시 테이블에게 준 이름은 average_rate_per_job이다. (이때 테이블 명은 왠만해선 소문자로 한다.) 그 이름 다음엔 AS, 괄호 안에는 해당 쿼리의 결과로 임시 테이블을 생성해 달라고 요청하는 것이다. 

 

사실 난 지금까지 서브쿼리를 이용하는 것을 애용했지만, 만들었던 쿼리를 다시 사용할 때 코드를 다시 뜯어보기까지 시간이 꽤 오래걸렸던 것 같다. 두 개의 테이블을 조인할 때까지는 어떻게 할만하지만 세 개 이상 테이블을 한꺼번에 조인할 때는 명확한 논리 전개를 위해 CTE가 필수적임을 깨달았다. 특히 recursive 쿼리에 CTE가 매우 중요한 역할을 하기에 CTE를 사용하는 법을 익혀두는 것은 필요한 것 같다.

---

Reference

https://kimsyoung.tistory.com/entry/SQL-CTE%EC%9D%98-%EB%AA%A8%EB%B2%94-%EC%82%AC%EB%A1%80%EB%8A%94-%EB%AC%B4%EC%97%87%EC%9D%BC%EA%B9%8C%EC%9A%94

SQL CTE를 잘 활용하려면?