CTE: 장점과 단점, 그리고 가독성 문제에 대한 고찰

CTE: 장점과 단점, 그리고 가독성 문제에 대한 고찰

2024. 7. 16. 23:20ㆍ카테고리 없음

💡 본 글은 회사에서 CTE(Common Table Expression) 를 처음보고, 이에 대해 조사해본 글입니다!

💡 CTE 문법은 ANSI 표준 중 일부라, 대부분의 DBMS 에서 지원하지만,
내부적으로 동작을 하는 방식은 DBMS 마다 다르니, 참고하세요!
(ex. Postgre 에서 CTE는 무조건 Using Temp 지만, MySQL 은 Execution Plan 에 따라 다릅니다! )

CTE 의 기본 개념

A common table expression (CTE) is a named temporary result set
that exists within the scope of a single statement and that can be referred to later within that statement,
possibly multiple times.

[출처] : MySQL 공식 문서

CTE 는 SQL에서 일시적은 결과 집합을 정의, 이를 사용할 수 있게 하는 기능입니다
주로 쿼리를 더 읽기 쉽게, 복잡한 쿼리를 단계별로 분리하는 데 사용됩니다
WITH 키워드를 사용하여 정의됩니다.

CTE 사용 예시

간단한 예시를 통해 CTE 의 사용법을 보겠습니다

WITH SalesCTE AS (
    SELECT date, SUM(price) AS total_sales
    FROM sales
    GROUP BY date
)
SELECT *
FROM SalesCTE
WHERE total_sales > 100;

위 쿼리에서는 SalesCTE라는 이름의 CTE를 정의하고,
이를 이용해 total_sales가 100보다 큰 행을 선택합니다.

Recursive CTE 예시

CTE 는 본인 스스로를 참조하는 재귀 참조가 가능합니다.
아래 간단한 예시를 통해 보시죠!

WITH RECURSIVE OrgChart AS (
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    INNER JOIN OrgChart o ON e.manager_id = o.employee_id
)
SELECT * FROM OrgChart;

이 쿼리는 직원 테이블을 이용해 조직도의 계층 구조를 재귀적으로 생성합니다

CTE 장점

가독성: 복잡한 쿼리를 더 읽기 쉽고, 논리적으로 분리하여 작성
재사용성: 동일한 CTE를 여러 번 참조
재귀적 사용 가능: 재귀 CTE를 사용하여 계층적 데이터를 쉽게 처리

이렇게 장점이 있고, 찾았었는데,,, 사실은 단점에 대해 쓰고 싶어 이 글을 쓰게 됐습니다 ....😂

CTE 단점

성능 문제: 일부 DBMS에서는 동일한 CTE를 여러 번 참조할 때마다 재평가될 수 있어 성능이 저하될 수 있습니다.
제한된 사용 범위: CTE는 단일 SQL 문 내에서만 존재하며, 다른 쿼리에서 참조할 수 없습니다.
메모리 사용: 큰 데이터셋을 처리할 때 메모리 사용량이 증가할 수 있습니다.

찾아보면, GPT 를 검색해보면 위와 같은 이유들이 나옵니다.
하지만 제가 말하고 싶었던 것은 장점중에서 '가독성' 이었습니다.

왜 가독성이 떨어지나요??

회사가 업력이 8년 정도 되다보니, 코드에 1,300줄 짜리 레거시 SQL 들이 종종 보입니다 😅
그래서 다들 이런 Query 들을 마주할 때마다 고통스러워 하는 데, 한 분이 이를 CTE 를 사용해서
가독성과 성능을 끌어올리신 케이스가 있습니다.

물론 성능이야 2배정도 빨라졌기에 엄청난 성능 개선이라고 볼 수 있습니다.
하지만 해당 Query 에는 CTE 가 10개 정도가 있었고, 처음 읽는 제 입장에서는
그렇게 많은 CTE 가 있다보니, 가독성이 좋지 못한 Query 라고 받아들여졌습니다

(아래 쿼리는 일일이 읽지 마시고, 대충 읽으셔도 됩니다!!!!!!!!!!!!)

WITH 
-- 첫 번째 CTE: 일별 판매 합계 계산
DailySales AS (
    SELECT 
        date,
        SUM(sales_amount) AS total_sales
    FROM 
        sales
    GROUP BY 
        date
),

-- 두 번째 CTE: 월별 판매 합계 계산
MonthlySales AS (
    SELECT 
        DATE_TRUNC('month', date) AS month,
        SUM(total_sales) AS monthly_sales
    FROM 
        DailySales
    GROUP BY 
        DATE_TRUNC('month', date)
),

-- 세 번째 CTE: 고객별 월별 구매 횟수 계산
CustomerMonthlyPurchases AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', purchase_date) AS month,
        COUNT(*) AS purchase_count
    FROM 
        purchases
    GROUP BY 
        customer_id, 
        DATE_TRUNC('month', purchase_date)
),

-- 네 번째 CTE: 고객별 총 구매액 계산
CustomerTotalPurchases AS (
    SELECT 
        customer_id,
        SUM(purchase_amount) AS total_purchases
    FROM 
        purchases
    GROUP BY 
        customer_id
),

-- 다섯 번째 CTE: 특정 상품의 일별 판매량 계산
ProductDailySales AS (
    SELECT 
        date,
        product_id,
        SUM(quantity) AS total_quantity
    FROM 
        product_sales
    WHERE 
        product_id = 123
    GROUP BY 
        date, 
        product_id
),

-- 여섯 번째 CTE: 특정 상품의 월별 판매량 계산
ProductMonthlySales AS (
    SELECT 
        DATE_TRUNC('month', date) AS month,
        SUM(total_quantity) AS monthly_quantity
    FROM 
        ProductDailySales
    GROUP BY 
        DATE_TRUNC('month', date)
),

-- 일곱 번째 CTE: 고객별 특정 상품의 월별 구매량 계산
CustomerProductMonthlyPurchases AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', purchase_date) AS month,
        SUM(quantity) AS product_quantity
    FROM 
        product_purchases
    WHERE 
        product_id = 123
    GROUP BY 
        customer_id, 
        DATE_TRUNC('month', purchase_date)
),

-- 여덟 번째 CTE: 특정 카테고리의 상품별 판매량 계산
CategoryProductSales AS (
    SELECT 
        product_id,
        SUM(sales_amount) AS category_sales
    FROM 
        product_sales
    WHERE 
        category_id = 456
    GROUP BY 
        product_id
),

-- 아홉 번째 CTE: 고객별 특정 카테고리의 총 구매액 계산
CustomerCategoryPurchases AS (
    SELECT 
        customer_id,
        SUM(purchase_amount) AS category_purchases
    FROM 
        product_purchases
    WHERE 
        category_id = 456
    GROUP BY 
        customer_id
),

-- 열 번째 CTE: 최종 결과 집합 생성
FinalResult AS (
    SELECT 
        c.customer_id,
        c.name,
        cm.monthly_sales,
        cp.total_purchases,
        pm.product_quantity,
        cc.category_purchases
    FROM 
        customers c
    LEFT JOIN MonthlySales cm ON DATE_TRUNC('month', c.signup_date) = cm.month
    LEFT JOIN CustomerTotalPurchases cp ON c.customer_id = cp.customer_id
    LEFT JOIN CustomerProductMonthlyPurchases pm ON c.customer_id = pm.customer_id
    LEFT JOIN CustomerCategoryPurchases cc ON c.customer_id = cc.customer_id
)
SELECT 
    *
FROM 
    FinalResult;

실제 Query 와는 다르지만, 비슷합니다! ㅠㅠㅠㅠ
위 Query 는 다음과 같은 문제들이 있습니다.

가독성 저하:
- 10개의 CTE가 연속으로 정의되어 있어, 각 CTE가 어떤 데이터를 생성하는지 파악하기 어려울 수 있습니다.
- 각 CTE의 목적을 이해하기 위해 전체 쿼리를 여러 번 읽어야 할 수 있습니다.
관리의 어려움:
- 쿼리에 변경이 필요할 때, 여러 CTE를 수정해야 하므로 유지보수가 어려워질 수 있습니다.
- CTE 간의 의존성이 많아, 하나의 CTE를 수정하면 다른 여러 CTE에 영향을 미칠 수 있습니다.

(이때 매 참조마다 CTE 는 재평가되어, 계속 쿼리가 실행된다는 의견이 있는데,
MySQL 은 보통 이 정도 복잡하면 내부적으로 임시 테이블을 만들게 되어 재평가 하지 않습니다!)

그래서 이 '가독성' 이라는 키워드도 남발되면 단점으로 보인다는 글을 쓰고 싶었습니다! ㅋㅋ

마무리

긴 글이었는데, 정리해보겠습니다!

1. CTE는 복잡한 쿼리를 더 읽기 쉽게 만들고, 논리적으로 분리하여 작성할 수 있는 강력한 도구

2.특히, 동일한 중간 결과를 여러 번 참조하거나, 재귀적 데이터를 처리하는 데 유용하다,

3. 하지만, CTE를 과도하게 사용하면
1. 가독성이 떨어지고,
2. 유지보수성이 낮아질 수 있으며,
3. 일부 DBMS에서는 성능 문제를 일으킬 수 있다.

이번 글을 통해 CTE의 장단점과 실무에서 발생할 수 있는 가독성 문제를 이해하고,
보다 효율적인 SQL 쿼리 작성에 도움이 되기를 바랍니다.

728x90

저작자표시 비영리

블로그이름을고민하다보니이거제목의길이가어디까지일지궁금하게되어이렇게해봤습니다