CTE (Common Table Expression) 란?

CTE (Common Table Expression)란? SQL에서 쿼리를 작성할 때, 복잡한 서브쿼리를 읽기 쉽게 표현하거나 재사용하기 위해 임시적인 이름이 부여된 결과 집합. CTE는 WITH 키워드를 사용하여 정의된다.

CTE를 활용하면 쿼리를 단순화하고, 가독성을 높이며, 재사용 가능성을 증가시킬 수 있다.

CTE 이름과 별칭에 대소문자가 구분되지 않으므로 컬럼과 다른 이름으로 적용해야됨.

 

 

CTE의 주요 특징

1. 임시적: CTE는 SQL 문이 실행되는 동안에만 존재합니다. (영구 저장되지 않음)
2. 가독성 향상: 복잡한 쿼리를 단계별로 작성할 수 있어 읽기 쉽습니다.
3. 재사용 가능: 정의한 CTE는 동일 쿼리 내에서 여러 번 참조할 수 있으며
4. 네이밍 가능: CTE는 이름을 부여하여 결과를 참조할 수 있다.

CTE 기본 문법

WITH cte_name AS (
       SELECT ...
       FROM ...
       WHERE ...
)
SELECT *
FROM cte_name;

• cte_name: CTE의 이름으로, 이후 쿼리에서 참조.
• 괄호 안의 쿼리: CTE에 저장할 SQL 결과.

CTE 활용 예제

1. 기본 사용

• 문제: 고객 테이블에서 1년 이상 활동한 고객의 ID와 이름을 조회한다고 가정.

WITH active_customers AS (
          SELECT customer_id, customer_name
          FROM customers
          WHERE last_activity_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
SELECT *
FROM active_customers;

• active_customers는 최근 1년 동안 활동한 고객의 ID와 이름을 저장한다.
            이 결과를 메인 쿼리에서 다시 사용.

2. CTE 재사용

• 매출 데이터에서 연간 매출과 평균 매출을 계산하는 쿼리:

WITH yearly_sales AS (
         SELECT
         EXTRACT(YEAR FROM sale_date) AS year,
         SUM(sale_amount) AS total_sales
FROM sales
GROUP BY year
)
SELECT
      year,
      total_sales,
      total_sales / 12 AS average_monthly_sales
FROM yearly_sales;

• yearly_sales CTE는 연도별 총 매출을 계산, 이후 이를 이용해 월평균 매출을 계산.

3. 다중 CTE

• 여러 CTE를 연결하여 복잡한 쿼리를 작성할 수 있다.

WITH orders_summary AS (

           SELECT customer_id, SUM(order_amount) AS total_order

           FROM orders

           GROUP BY customer_id

),

high_value_customers AS (

           SELECT customer_id

           FROM orders_summary

           WHERE total_order > 10000

)

SELECT *

FROM high_value_customers;

• orders_summary CTE는 고객별 총 주문 금액을 계산, high_value_customers는 총 주문 금액이 10,000 이상인 고객을 필터링.

CTE vs 서브쿼리

1. 가독성: CTE는 이름이 부여되므로, 복잡한 쿼리를 읽기 쉽다.
                서브쿼리는 코드가 길고 이해하기 어려울 수 있다.
2. 재사용성: CTE는 동일 쿼리에서 여러 번 참조 가능. 서브쿼리는 한 번 사용되고 끝남.
3. 계층화: CTE는 단계별로 쿼리를 계층화하여 작성할 수 있다.
               서브쿼리는 쿼리 내에 중첩되므로 복잡한 경우 디버깅이 어렵다.

CTE의 장점

1. 코드 가독성: 복잡한 쿼리를 단계별로 나누어 작성 가능.
2. 재사용성: 동일 쿼리 내에서 반복적으로 결과를 참조 가능.
3. 효율성: BigQuery와 같은 일부 시스템은 CTE를 최적화하여 실행 계획을 간소화.

 

CTE는 SQL 쿼리에서 복잡한 작업을 간단하고 구조적으로 처리할 수 있게 도와주며, 데이터 분석이나 비즈니스 로직이 복잡한 경우 특히 유용하며, 가독성과 재사용성을 크게 향상시킨다.