가독성 향상을 위한 공통 테이블 표현식을 사용한 SQL 간소화

소개

데이터베이스 관리의 복잡한 세계에서 SQL 쿼리는 데이터를 추출, 조작 및 변환하는 생명선입니다. 데이터 볼륨이 증가하고 비즈니스 로직이 더욱 정교해짐에 따라 쿼리는 종종 작성, 디버깅 및 궁극적으로 이해하기 부담스러운 광범위하고 다층적인 구조로 발전합니다. 이러한 복잡성은 개발 속도를 저해할 뿐만 아니라 오류 가능성을 높이고 협업을 상당한 어려움으로 만듭니다. 다행히 SQL은 매우 강력한 기능인 공통 테이블 표현식(CTE)을 제공하며, 이는 이 문제에 대한 우아한 해결책을 제공합니다. CTE는 복잡한 로직을 이해하기 쉬운 명명된 하위 쿼리로 분해하여 불투명한 SQL을 명확하고 유지 관리 가능한 코드로 변환하여 생산성과 가독성을 크게 향상시킵니다. 이 기사에서는 CTE가 복잡한 SQL 쿼리의 괴물을 길들이는 데 어떻게 최고의 동맹이 될 수 있는지 살펴볼 것입니다.

공통 테이블 표현식 이해

실용적인 적용에 앞서 CTE가 무엇인지, 그리고 그 기본 원리를 명확히 이해해 봅시다.

공통 테이블 표현식(CTE)이란 무엇인가요?

공통 테이블 표현식(CTE)은 단일 SQL 문(SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW) 내에서 참조할 수 있는 임시 명명된 결과 집합입니다. 이를 부분 쿼리의 수명에만 국한되는 정의된 하위 쿼리로 생각할 수 있습니다. 이러한 임시적 특성은 CTE가 스키마 개체로 저장되지 않고 주로 복잡한 쿼리 로직을 구성하는 데 사용됨을 의미합니다.

CTE의 주요 특징:

• 임시 및 명명됨: 각 CTE에는 임시 뷰와 마찬가지로 고유한 이름이 부여되지만, 즉시 쿼리의 범위 내에서만 존재합니다.
• 비재귀 및 재귀: CTE는 비재귀(이 기사의 초점, 순차 처리용)이거나 재귀(조직도 또는 재료 명세서와 같은 계층적 데이터 탐색용)일 수 있습니다.
• 가독성 향상: 복잡한 로직을 작고 명명된 블록으로 추상화함으로써 CTE는 쿼리를 훨씬 더 쉽게 읽고 이해할 수 있게 합니다.
• 단일 쿼리 내 재사용: 일단 정의되면 CTE는 동일한 상위 쿼리 내에서 여러 번 참조되어 코드 중복을 방지할 수 있습니다.
• 유지 관리 용이성 향상: 단순화된 구조로 인해 다른 부분에 영향을 주지 않고 복잡한 쿼리의 특정 부분을 수정하거나 디버깅하기가 더 쉬워집니다.

CTE의 구문:

하나 이상의 CTE를 정의하는 기본 구문은 다음과 같습니다.

WITH CTE_Name_1 AS (
    -- 첫 번째 하위 쿼리 정의
    SELECT column_a, column_b
    FROM table_x
    WHERE condition_1
),
CTE_Name_2 AS (
    -- 두 번째 하위 쿼리 정의(CTE_Name_1 참조 가능)
    SELECT column_c, column_d
    FROM CTE_Name_1
    WHERE condition_2
)
-- 하나 이상의 CTE를 사용하는 최종 쿼리
SELECT *
FROM CTE_Name_2
WHERE final_condition;

WITH 키워드 뒤에 CTE 이름, AS 및 괄호 안의 하위 쿼리가 오는 것을 주목하십시오. 여러 CTE는 쉼표로 구분됩니다.

CTE를 사용하는 이유? 실용적인 적용 및 예시

일반적인 복잡한 쿼리 시나리오를 다루는 실용적인 예시를 통해 CTE의 강력함을 설명해 봅시다.

예시 1: 복잡한 조인 분해

두 개 이상의 주문을 한 특정 지역에 거주하는 고객의 평균 주문 가치를 찾아야 하는 시나리오를 고려해 봅시다. CTE 없이는 중첩된 하위 쿼리 또는 깊게 연결된 조인이 필요할 수 있습니다.

CTE 없이:

SELECT
    c.Region,
    AVG(o.OrderValue) AS AverageOrderValue
FROM
    Customers c
JOIN
    Orders o ON c.CustomerID = o.CustomerID
WHERE
    c.CustomerID IN (
        SELECT CustomerID
        FROM Orders
        GROUP BY CustomerID
        HAVING COUNT(OrderID) > 2
    )
AND
    c.Region = 'North'
GROUP BY
    c.Region;

이 쿼리는 기능적이지만 인라인 하위 쿼리로 인해 따르기 어려울 수 있습니다.

CTE 사용:

WITH CustomersWithMultipleOrders AS (
    SELECT
        CustomerID
    FROM
        Orders
    GROUP BY
        CustomerID
    HAVING COUNT(OrderID) > 2
),
CustomersInNorthRegion AS (
    SELECT
        CustomerID,
        Region
    FROM
        Customers
    WHERE
        Region = 'North'
)
SELECT
    cnr.Region,
    AVG(o.OrderValue) AS AverageOrderValue
FROM
    Orders o
JOIN
    CustomersInNorthRegion cnr ON o.CustomerID = cnr.CustomerID
JOIN
    CustomersWithMultipleOrders cmos ON o.CustomerID = cmos.CustomerID
GROUP BY
    cnr.Region;

여기서는 CustomersWithMultipleOrders 및 CustomersInNorthRegion으로 로직을 명확하게 분리했습니다. 각 단계는 자체 설명적이므로 전체 쿼리가 훨씬 더 읽기 쉽고 문제가 발생했을 때 디버깅이 더 쉽습니다.

예시 2: 다단계 데이터 변환 단순화

시간이 지남에 따라 각 제품 범주에 대한 판매 누적 합계를 계산해야 한다고 가정해 봅시다.

CTE 없이 (주의 깊게 구성하지 않으면 종종 중첩된 하위 쿼리와 함께 창 함수 포함):

SELECT
    s.SaleDate,
    s.Category,
    s.SalesAmount,
    (SELECT SUM(s2.SalesAmount)
     FROM SalesData s2
     WHERE s2.Category = s.Category AND s2.SaleDate <= s.SaleDate) AS RunningTotal
FROM
    SalesData s
ORDER BY
    s.Category, s.SaleDate;

상관 하위 쿼리로 인해 데이터베이스 엔진이 이를 최적화하기 어렵고 비효율적일 수 있습니다.

CTE 및 창 함수 사용:

WITH DailyCategorySales AS (
    SELECT
        SaleDate,
        Category,
        SUM(SalesAmount) AS DailySales
    FROM
        SalesData
    GROUP BY
        SaleDate, Category
)
SELECT
    SaleDate,
    Category,
    DailySales,
    SUM(DailySales) OVER (PARTITION BY Category ORDER BY SaleDate) AS RunningTotalSales
FROM
    DailyCategorySales
ORDER BY
    Category, SaleDate;

이 예시에서는 DailyCategorySales CTE가 먼저 날짜 및 범주별 판매를 집계하여 명확한 중간 결과를 제공합니다. 그런 다음 최종 SELECT는 창 함수를 적용하여 누적 합계를 계산하며, 이는 이 명확한 기반을 기반으로 합니다. 이 접근 방식은 읽기 쉬울 뿐만 아니라 종종 성능도 더 좋습니다.

예시 3: 모듈성 및 디버깅 개선

필터링된 거래 세트를 기반으로 여러 지표를 계산하는 보고서를 고려해 봅시다. 필터링 로직이 복잡한 경우 각 지표에 대해 반복하면 오류와 유지 관리 문제가 발생할 수 있습니다.

CTE 없이 (반복적인 필터링):

SELECT
    (SELECT COUNT(TransactionID) FROM Transactions WHERE TransactionType = 'Purchase' AND Amount > 100 AND TransactionDate >= '2023-01-01') AS TotalPurchases,
    (SELECT SUM(Amount) FROM Transactions WHERE TransactionType = 'Purchase' AND Amount > 100 AND TransactionDate >= '2023-01-01') AS TotalPurchaseAmount,
    (SELECT AVG(Amount) FROM Transactions WHERE TransactionType = 'Purchase' AND Amount > 100 AND TransactionDate >= '2023-01-01') AS AveragePurchaseAmount;

CTE 사용 (중앙 집중식 필터링):

WITH FilteredTransactions AS (
    SELECT
        TransactionID,
        TransactionType,
        Amount
    FROM
        Transactions
    WHERE
        TransactionType = 'Purchase'
        AND Amount > 100
        AND TransactionDate >= '2023-01-01'
)
SELECT
    COUNT(TransactionID) AS TotalPurchases,
    SUM(Amount) AS TotalPurchaseAmount,
    AVG(Amount) AS AveragePurchaseAmount
FROM
    FilteredTransactions;

여기서 FilteredTransactions CTE는 공통 필터링 로직을 캡슐화합니다. 필터링 조건이 변경되면 CTE 정의 내에서 한 곳만 수정하면 되므로 유지 관리가 크게 단순화되고 불일치 가능성이 줄어듭니다. 또한 중간 디버깅 단계로 SELECT * FROM FilteredTransactions;를 사용할 수 있어 매우 유용합니다.

CTE는 언제 사용해야 하는가

• 쿼리가 복잡하고 여러 단계가 포함된 경우: 논리를 논리적이고 명명된 조각으로 분해합니다.
• 동일한 큰 쿼리 내에서 하위 쿼리를 여러 번 참조해야 하는 경우: 반복을 피하고 일관성을 향상시킵니다.
• 창 함수를 사용하는 경우: CTE는 데이터 준비 단계와 창 함수 적용을 명확하게 분리할 수 있습니다.
• 재귀 쿼리의 경우: 계층적 데이터 탐색에 필수적입니다.
• SQL 코드의 가독성 및 유지 관리성을 향상시킵니다.

제한 사항

• 범위: CTE는 임시적이며 정의된 쿼리의 지속 시간 동안만 존재합니다. 후속 독립 쿼리에서 참조할 수 없습니다.
• 성능: CTE는 가독성을 향상시키지만 본질적으로 성능을 향상시키지는 않으며, 일부 경우 잘못 구성된 CTE는 신중하게 조정된 하위 쿼리보다 약간 덜 최적화된 쿼리 계획으로 이어질 수 있습니다(현대 옵티마이저는 매우 훌륭함). 성능이 중요한 경우 항상 실행 계획을 분석하십시오.
• 인덱싱: CTE는 임시 결과 집합이므로 기본 테이블에 정의된 인덱스는 옵티마이저가 술어(predicate)를 전파하거나 작업을 푸시다운(push down)할 수 있다고 판단하지 않는 한 CTE 자체 내에서 직접 사용되지 않습니다.

결론

공통 테이블 표현식은 더 명확하고, 더 이해하기 쉬우며, 더 유지 관리 가능한 코드를 작성하려는 모든 SQL 개발자에게 필수적인 도구입니다. 복잡한 로직을 명명된 임시 결과 집합으로 모듈화할 수 있도록 함으로써 CTE는 복잡한 모놀리식 쿼리를 논리적이고 이해하기 쉬운 일련의 단계로 변환합니다. 이는 SQL의 가독성을 크게 향상시킬 뿐만 아니라 디버깅을 간소화하고, 협업을 촉진하며, 궁극적으로 생산성을 향상시킵니다. CTE를 사용하여 복잡한 SQL 쿼리를 길들이고 데이터베이스 상호 작용에 명확성을 가져오십시오.