[PostgreSQL] CTE: Common Table Expression

CTE (공통 테이블 표현식)

CTE는 "Common Table Expressions"의 약자로, 임시적으로 사용할 수 있는 쿼리 블록을 생성하는 기능입니다. 이를 사용하면 복잡한 쿼리를 더욱 가독성 있고 모듈화된 형태로 작성할 수 있습니다.

장점

1. 가독성 향상: CTE를 사용하면 쿼리의 가독성이 향상됩니다. 쿼리의 각 부분을 개별적으로 정의하고, 이름을 부여하여 해당 부분을 쉽게 식별할 수 있습니다. 이는 복잡한 쿼리를 더 이해하기 쉽게 만듭니다.
2. 코드 재사용: CTE를 사용하면 동일한 쿼리나 서브쿼리를 반복해서 작성할 필요가 없습니다. 한 번 정의된 CTE는 다른 부분에서 재사용할 수 있습니다. 이는 코드의 중복을 줄이고 유지 관리를 용이하게 만듭니다.
3. 쿼리 최적화: 데이터베이스 시스템은 CTE를 보다 효율적으로 처리하기 위해 쿼리 실행 계획을 최적화할 수 있습니다. CTE를 사용하면 데이터베이스 시스템이 쿼리를 더 효율적으로 처리할 수 있도록 도와줍니다.
4. 재귀 쿼리 지원: CTE는 재귀 쿼리를 지원합니다. 이를 통해 계층적 데이터를 처리하거나 트리 구조 데이터를 쿼리하는 등의 작업을 수행할 수 있습니다.
5. 디버깅 용이성: CTE를 사용하면 쿼리의 각 부분을 독립적으로 테스트하고 디버깅할 수 있습니다. 각 CTE를 개별적으로 실행하여 결과를 확인하고 필요에 따라 수정할 수 있습니다.
6. 가상 테이블 생성: CTE를 사용하면 가상의 테이블을 생성할 수 있습니다. 이를 통해 중간 결과를 임시적으로 저장하거나 여러 단계의 데이터 변형을 수행할 수 있습니다.

CTE는 복잡한 쿼리 작성 및 유지 관리를 간소화하고, 쿼리의 가독성과 이해도를 향상시키며, 쿼리 실행의 효율성을 높이는 데 매우 유용한 도구입니다.

WITH 키워드

CTE는 WITH 키워드를 사용하여 정의되며, 쿼리 내부에서 재사용되어야 하는 부분을 정의할 때 유용합니다. 일반적으로 CTE는 재귀적 쿼리나 여러 개의 쿼리에서 동일한 하위 집합을 사용해야 할 때 자주 사용됩니다.

WITH sales_over_1000 AS (
    SELECT * FROM sales WHERE amount > 1000
)
SELECT * FROM sales_over_1000;

위의 쿼리에서 WITH 키워드를 사용하여 sales_over_1000이라는 임시 테이블을 생성합니다. 이 테이블은 sales 테이블에서 amount가 1000보다 큰 모든 행을 포함합니다. 그리고 이후에 이 임시 테이블을 사용하여 결과를 선택합니다.

또한, CTE는 다음과 같이 여러 개의 CTE를 동시에 정의하고 사용할 수도 있습니다.

WITH 
    sales_over_1000 AS (
        SELECT * FROM sales WHERE amount > 1000
    ),
    high_value_customers AS (
        SELECT * FROM customers WHERE total_purchase > 5000
    )
SELECT * FROM sales_over_1000
UNION ALL
SELECT * FROM high_value_customers;

두 개의 CTE를 정의하고, 각각 sales_over_1000과 high_value_customers라는 임시 테이블을 생성합니다. 그리고 이후에 이 두 테이블을 사용하여 결과를 선택하고 있습니다.

CTE는 쿼리를 더 읽기 쉽고 유지 관리하기 쉽게 만들어 줍니다. 특히 동일한 부분을 반복적으로 사용해야 할 때 매우 유용합니다.

WITH + VALUE 구문

WITH 문과 VALUES 구문을 함께 사용하면 임시 데이터 세트를 정의하고 해당 데이터 세트를 쿼리에서 사용할 수 있습니다. VALUES 구문을 사용하여 하나 이상의 행을 정의하고, WITH 문을 사용하여 해당 행들을 임시 테이블처럼 참조할 수 있습니다.

WITH my_data AS (
    VALUES 
        (1, 'Alice'),
        (2, 'Bob'),
        (3, 'Charlie')
)
SELECT * FROM my_data;

위의 쿼리에서는 my_data라는 임시 데이터 세트를 정의하고 있습니다. 이 데이터 세트는 세 개의 행으로 구성되며, 각 행은 숫자와 이름의 쌍으로 구성됩니다. 이 데이터 세트는 VALUES 구문을 사용하여 정의되었습니다.

그런 다음, 이 데이터 세트를 쿼리에서 선택하여 사용합니다. 이 예제에서는 my_data 데이터 세트를 선택하여 모든 열을 반환합니다.

VALUES 구문은 괄호로 묶인 각 행을 나타내며, 각 행은 콤마로 구분된 값을 포함합니다. 이를 통해 여러 개의 행을 한 번에 정의할 수 있습니다.

WITH 문과 VALUES 구문을 함께 사용하면 쿼리 내에서 임시 데이터를 정의하고 해당 데이터를 다양한 방식으로 사용할 수 있습니다.