CTEs(Common Table Expressions) 개념 및 사용법

 

개요

DBT 프로젝트 진행하면서 CTEs를 많이 활용하게 될 것 같다. 아무래도 DBT라는 툴 자체가 Transform에 맞춰진 툴이기 때문에, 깔끔하게 데이터를 정리하는 것이 매우 강조되는 것 같다. 그래서 SQL query에서는 CTE가 이에 제격인 셈이 아닐까 싶다. 유데미 강의를 따라가면서 Airbnb DBT 프로젝트를 진행할 예정인데, CTEs를 이번 기회에 확실히 정리하고 가면 좋을 것 같아서 포스트해본다.

 

CTEs 예시 (강의자료 발췌)

 

CTEs 개념

한국말로는 공통 테이블 표현식이라고 하는데, Common Table Expression; CTE는 SQL 쿼리에서 읽기 쉽고 유지 관리하기 용이한 쿼리 작성을 돕는 기능이다. 일시적으로 이름을 붙이는 결과 집합이라고 정의되어 있는데, 쉽게 보면 조회하는 쿼리를 하나의 임시 테이블 형태로 만드는 느낌인 것 같다.

• 정의: CTE는 일시적인, 이름이 붙은 결과 집합
• 특징: CTE는 쿼리의 SELECT, INSERT, UPDATE, DELETE, 또는 MERGE 문이 실행되는 동안 메모리에 유지
• 문법: 
   

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

• WITH 절은 CTE를 정의할 것임을 데이터베이스에 알리는 역할.
• cte_name은 CTE에 주는 이름으로, 후속 쿼리에서 참조 가능
• SELECT 문은 실제로 데이터를 반환하는 쿼리로, CTE 내에서 정의된 일시적인 테이블을 참조함
• FROM cte_name에서 CTE를 참조하여 데이터를 로드

 

CTE 사용 예시

예를 들어, Airbnb에서 추출한 원시 데이터가 있고 이 데이터를 변환하려고 한다고 가정하고 CTE를 짜보면 이렇다.

WITH raw_listings AS (
    SELECT *
    FROM source_listings
)
SELECT id AS listing_id, other_column1, other_column2
FROM raw_listings;

 

 

1. 단계 1: CTE를 생성함. 이 CTE를 raw_listings라고 명명

WITH raw_listings AS (
    SELECT *
    FROM source_listings
)

 

2. 단계 2: 내부 쿼리에서는 source_listings 테이블의 모든 열을 선택

SELECT *
FROM source_listings

 

 

3. 단계 3: 외부 쿼리에서는 raw_listings CTE에서 특정 열만 선택하고, id 열의 이름을 listing_id로 변경하여 더 명확하게 

SELECT id AS listing_id, other_column1, other_column2
FROM raw_listings;

 

CTE의 장점

• 가독성: CTE는 쿼리 구조를 단순화하고 가독성을 높임
• 유지 관리 용이: 복잡한 쿼리를 더 이해하기 쉽고 유지 관리하기 용이
• 재사용성: CTE를 사용하면 쿼리를 여러 부분으로 나누어 복잡한 논리를 간단한 논리 블록으로 나눌 수 있음
• 비용 효율성: CTE는 메타데이터에 정의를 저장하지 않기 때문에 저장 공간을 절약

 

포스팅 자료 출처

본 포스팅은 유데미(Udemy) 사이트의 "The Complete dbt (Data Build Tool) Bootcamp: Zero to Hero" 강의의 내용을 발췌하여 작성했습니다. (광고 X)