CTE와 Recursive 쿼리

CTE는 Common Table Expression으로 쿼리를 통해 만들어낸 임시적 데이터 세트이다.

CTE는 아래와 같이 작성한다.

WITH 테이블 이름 AS (
	테이블 쿼리
)

WITH와 함께 시작하여 사용된다. 이렇게 임시적으로 만든 테이블을 다른 쿼리들에서 가져와 사용할 수 있는 것이다.

그렇기 때문에 대부분의 경우 subQuery 문으로 대체 가능하다.

하지만 CTE를 사용한다면 훨씬 쿼리문을 훨씬 직관적으로 읽게 도와주게 된다.

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;

위처럼 SELECT Query 안에 또 SELECT 쿼리가 있는 경우 읽기가 매우 쉽지 않다.

이를 CTE를 사용해서 밖으로 SELECT 문을 빼내서 따로 보도록 하자.

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;

훨씬 쿼리문의 구조가 보기 편해졌다.

WITH 문을 먼저 본 후 average_rate_per_job이 어떤 정보가 있는 테이블인지에 대해서 알 수 있고, 그 다음 아래 SQL은 단순 JOIN 문이므로 간단히 해석할 수 있다.

또한 이러한 CTE는 재사용할 수 있다는 큰 장점을 가지고 있다. 미리 자주 사용하는 subQuery를 CTE로 만들어 두면 매번 subQuery 문을 작성할 필요 없이 미리 만들어둔 CTE를 가져다 쓰면 되기 때문에 훨씬 편리하다.

또한 CTE를 동시에 여러개를 만들수도 있다.

WITH total_average AS(
 SELECT 
   AVG(hours) AS daily_avg_total
 FROM hours_worked),

avg_per_freelancer AS(
 SELECT
  f.id,
  f.first_name,
  f.last_name,
  AVG(h.hours) AS daily_avg_freelancer
 FROM hours_worked AS h
 LEFT JOIN freelancer AS f
     ON h.freelancer_id = f.id
 GROUP BY f.id, f.first_name, f.last_name)

SELECT
  *,
  (daily_avg_freelancer - daily_avg_total) AS diff
FROM avg_per_freelancer, total_average
WHERE (daily_avg_freelancer - daily_avg_total) > 1;

위와같이 2개의 CTE를 만들 때에는 한번의 WITH문만 사용 후 ,로 이어서 작성하면 된다.

지금까지 CTE는 subquery의 대안책으로만 보이지만 CTE를 사용해야만 할 수 있는 것도 있다. 그것이 바로 Recursive 쿼리다.

Recursive Query

Recursive Query, 재귀 쿼리는 자기 자신을 참조해 실행하는 쿼리이다.자기 자신을 참조해 실행하여 부분적인 결과를 반환하고 최종 결과를 얻을 때까지 반복하는 우리가 일반 프로그래밍에서 자주 사용하던 재귀 함수와 거의 유사한 쿼리이다.

이는 트리 구조와 같은 계층적 데이터를 다룰때 유용하게 사용된다.

WITH RECURSIVE CTE_NAME AS
AS (
		-- 초기 쿼리 (재귀되지 않음)
		SELECT ...
		UNION ALL
		-- 재귀 쿼리 (재귀 됨)
		SELECT ...
		FROM CTE_NAME
		WHERE ...
)
SELECT * FROM CTE_NAME;

위와 같이 작성한다.

CTE에서 RECURSIVE가 추가된 형태로 작성하게 된다.

이때 CTE 쿼리문 안에 2개의 SELECT 문이 UNION ALL로 연결되어있는 형태로 되어있다.

첫번째 SELECT 문은 비 재귀 쿼리로 처음 한번만 실행된다. 두번째 SELECT 문은 재귀 쿼리로 재귀적으로 실행되는 쿼리이다.

부하직원을 전부 찾는 쿼리를 해보자.

WITH RECURSIVE employee_hierarchy AS (
    -- 초기 쿼리 (base case)
    SELECT employee_id, manager_id, full_name
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀 쿼리 (recursive case)
    SELECT e.employee_id, e.manager_id, e.full_name
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

위 쿼리는 manager_id가 없는 최상위 직원들의 부하 직원을 전부 찾는 쿼리이다.

 

 

Reference

• SQL CTE를 잘 활용하려면?
• MSSQL 계층형 쿼리, 트리구조로 실적 구하기(WITH, 재귀 CTE)