SQL에서의 CTE: 예제와 함께 완벽한 가이드

SQL CTE 생성 방법

CTE를 생성할 때, CTE 정의를 시작하기 위해 WITH 키워드를 사용합니다. CTE의 일반적인 구문은 다음과 같습니다:

WITH cte_name (column1, column2, ...)
AS (
    -- CTE를 정의하는 쿼리
    SELECT ...
    FROM ...
    WHERE ...
)
-- 주 쿼리
SELECT ...
FROM cte_name;

Where:

• WITH: CTE(공통 테이블 표현식) 정의를 시작하며, 이후의 이름이 임시 결과 집합을 나타낸다는 것을 나타냅니다.

• cte_name: 이 이름은 CTE에 할당되어 주 쿼리에서 참조하기 위한 것입니다.

• 옵션 컬럼 목록 (column1, column2, …): CTE의 결과 집합을 위한 열 이름을 지정합니다. 이는 열 이름을 조정해야 할 때 유용합니다.

• CTE를 정의하는 쿼리: 데이터를 선택하고 임시 결과 집합을 구성하는 내부 쿼리입니다.

• 주 쿼리: CTE를 이름으로 참조하여 테이블처럼 사용합니다.

다음은 계층적 접근 방식을 사용하여 CTE를 생성하는 예제를 살펴 봅시다. Employees 테이블이 있다고 가정하고, 연봉이 $50,000 이상인 직원을 선택하는 CTE를 만들고 싶다고 가정합시다.

단계 1: 기본 쿼리 작성

기본 SELECT 쿼리를 작성하여 시작합니다:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

단계 2: WITH 키워드를 사용하여 쿼리를 래핑하여 CTE 생성

CTE에 이름을 지정하기 위해 WITH 키워드를 사용합니다.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

단계 3: 주 쿼리에서 CTE 사용

마지막으로, 위에서 정의한 CTE 이름을 호출하여 SELECT 문에서 CTE를 참조합니다.

-- 공통 테이블 표현식(CTE) 정의
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- CTE를 사용하여 고수입 직원 선택
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

위 단계를 요약하면, WITH 키워드를 사용하여 HighEarningEmployees라는 CTE를 정의했습니다. 내부 쿼리를 사용하여 임시 데이터 집합을 생성했습니다. 주 쿼리는 HighEarningEmployees를 참조하여 지정된 열 EmployeeID, FirstName, LastName을 표시합니다.

SQL CTE의 유용성

위의 예시에서 단순한 쿼리조차 같은 결과를 얻을 때도 왜 CTE를 사용하는지 궁금할 수 있습니다. 다음은 그 이유입니다:

복잡한 쿼리를 단순화

CTE는 복잡한 SQL 문을 더 작고 관리하기 쉬운 부분으로 나누어 코드를 읽기 쉽고 작성하고 유지하기 쉽도록 만듭니다. 

세 개의 테이블이 있다고 가정해보겠습니다: 주문, 고객, 제품. 2024년에 구매한 각 고객이 생성한 총 수익을 찾고 싶습니다. CTE를 사용하지 않고 쿼리를 작성하면 지저분하고 이해하기 어렵습니다.

-- 고객 이름과 주문으로부터 총 수익 선택
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- 고객 및 제품 테이블을 결합
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

CTE를 사용하면 로직을 더 읽기 쉬운 형식으로 분리할 수 있습니다:

-- CTE 정의
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
--주 쿼리
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

코드 재사용성

CTE는 동일한 결과 집합이 쿼리의 서로 다른 부분에서 재사용되도록 허용함으로써 중복을 피할 수 있습니다. 동일한 데이터 집합을 기반으로 하는 여러 계산 또는 작업이 있을 경우 CTE에서 한 번 정의하고 필요할 때 참조할 수 있습니다.

전자 상거래 데이터베이스에서 각 제품 카테고리의 평균 및 총 매출을 계산해야 한다고 가정해보십시오. CTE를 사용하여 한 번 계산을 정의하고 후속 쿼리에서 재사용할 수 있습니다.

-- 각 카테고리의 총 매출 및 평균 매출을 계산하는 CTE 정의
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- CTE에서 카테고리, 총 매출 및 평균 매출을 선택
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

기타 응용 프로그램

쿼리를 간소화하고 코드 재사용성을 높이는 것 외에도 CTE에는 다른 용도가 있습니다.CTE의 가능한 모든 사용 사례를 자세히 다룰 수는 없습니다. 데이터 조작(SQL) 코스는 계속 연습하고 싶다면 좋은 옵션입니다. 그러나 여기서 주요 다른 이유 중 일부를 문서화하겠습니다:

• 쿼리 조직화 및 가독성: CTE는 쿼리를 논리적이고 연속적인 단계로 나누어 SQL 코드의 가독성을 향상시킵니다. 쿼리 프로세스의 각 단계는 자체 CTE로 나타낼 수 있어 전체 쿼리를 더 쉽게 따를 수 있습니다.
• 계층적 데이터 탐색: CTE는 조직 구조, 부모-자식 관계 또는 중첩된 수준을 포함하는 모든 데이터 모델과 같은 계층적 관계를 탐색하는 데 도움이 될 수 있습니다. 재귀 CTE는 계층적 데이터를 쿼리하는 데 유용합니다. 왜냐하면 단계적으로 수준을 탐색할 수 있기 때문입니다.
• 다중 수준 집계: CTE는 월별, 분기별, 연도별 등 다양한 수준에서 판매 수치를 계산하는 등 다중 수준의 집계를 수행하는 데 도움이 될 수 있습니다. CTE를 사용하여 이러한 집계 단계를 분리하면 각 수준이 독립적으로 논리적으로 계산됨이 보장됩니다.
• 여러 테이블에서 데이터 결합: 여러 CTE를 사용하여 서로 다른 테이블에서 데이터를 결합할 수 있으며, 최종 결합 단계를 보다 구조화된 방식으로 만들 수 있습니다. 이 접근 방식은 복잡한 조인을 단순화하고 소스 데이터가 논리적으로 구성되어 가독성이 향상되도록 보장합니다.

고급 SQL CTE 기술

CTE는 다양한 유형의 사용 사례에 유용하고 다양한 SQL 기술을 지원하여 다양한 고급 응용을 지원합니다. 다음은 CTE의 고급 응용 사례 중 일부입니다.

단일 쿼리에서 여러 CTE 사용

하나의 쿼리에서 여러 개의 CTE를 정의할 수 있으며, 복잡한 변환 및 계산이 가능합니다. 이 방법은 문제가 여러 단계의 데이터 처리를 필요로 할 때 유용하며, 각 CTE가 고유한 단계를 나타냅니다.

예를 들어, Sales라는 테이블에 판매 데이터가 있다고 가정하고 각 제품의 총 매출을 계산하고, 평균 이상의 총 매출을 갖는 제품을 식별하며, 이러한 제품을 총 매출에 따라 순위를 매기고자 합니다.

WITH ProductSales AS (
    -- 단계 1: 각 제품의 총 매출 계산
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- 단계 2: 모든 제품의 평균 총 매출 계산
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- 단계 3: 평균 이상의 총 매출을 갖는 제품 필터링
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- 단계 4: 높은 매출 제품 순위 매기기
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

위의 예에서;

• 첫 번째 CTE(ProductSales)는 각 제품의 총 매출을 계산합니다.

• 두 번째 CTE(AverageSales)는 모든 제품의 평균 총 매출을 계산합니다.

• 세 번째 CTE (HighSalesProducts)는 총 매출이 평균을 초과하는 제품을 필터링합니다.

• 최종 쿼리는 총 판매량을 기준으로 이러한 제품들을 순위 매깁니다.

UPDATE, DELETE 및 MERGE 문에서의 CTE

UPDATE, DELETE 및 MERGE 작업에 통합되면 CTE는 복잡한 필터 또는 계층적 데이터를 처리할 때 데이터 조작 작업을 간단하게 만들어줄 수 있습니다.

UPDATE 문과 함께 CTE 사용하기

우리가 Employees 테이블과 EmployeeSalary 열이 있다고 가정해봅시다. 회사에서 5년 이상 근무한 모든 직원들에게 10%의 인상을 해주고 싶습니다.

-- 5년 이상 전에 채용된 직원을 찾기 위한 CTE 정의
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- CTE에서 식별된 장기 근로자들에게 10% 인상된 급여를 업데이트
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployees는 5년 이상 근무한 직원들을 식별합니다. UPDATE 문은 이 CTE를 사용하여 급여를 선택적으로 인상합니다.

CTE를 DELETE 문과 함께 사용하기

이제 Products라는 테이블이 있다고 가정하고, 지난 2년간 판매되지 않은 모든 제품을 삭제하려고 합니다. CTE를 사용하여 제품을 필터링할 수 있습니다:

-- 지난 2년간 판매되지 않은 제품 식별을 위한 CTE 정의
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- DATEADD를 사용하여 2년 전 이후에 판매된 제품 찾기
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- 주 테이블에서 오래된 제품 식별 후 삭제
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

CTE OldProducts는 지난 두 해 동안 판매되지 않은 제품을 식별하며, DELETE 문은 이 CTE를 사용하여 해당 제품을 삭제합니다.

CTE를 MERGE 문과 함께 사용하기

SQL의 MERGE 문을 사용하면 소스 테이블의 데이터를 기준으로 대상 테이블에서 조건부 업데이트, 삽입 또는 삭제가 가능합니다. 다음 예제에서 CTE MergedInventory는 새로운 재고 데이터와 기존 재고 데이터를 결합합니다. 그런 다음 MERGE 문은 CTE 데이터를 기반으로 기존 제품의 수량을 업데이트하거나 새 제품을 삽입합니다.

-- 새로운 및 기존 인벤토리 데이터를 병합하기 위한 CTE
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- LEFT JOIN을 사용하여 현재 인벤토리에 없어도 모든 새 데이터를 포함시킵니다
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- 준비된 데이터를 인벤토리 테이블에 병합합니다
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- 기존 제품을 새 수량으로 업데이트합니다
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- 인벤토리에 제품이 없다면 새 제품을 삽입합니다
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

재귀 공통 테이블 표현식(CTEs)

재귀 CTE는 고급 및 반복 작업을 수행하는 데 도움이 됩니다.

재귀 CTE 소개

재귀 CTE는 자체를 참조하여 정의 내에서 반복 작업을 수행할 수 있는 특수한 유형의 CTE로, 계층적이거나 트리 구조 데이터(조직도, 디렉토리 구조 또는 제품 구성품 등)를 처리하는 데 이상적입니다. 재귀 CTE는 데이터를 반복적으로 처리하여 종료 조건이 충족될 때까지 단계별로 결과를 반환합니다.

앵커 및 재귀 멤버

재귀 CTE는 주로 두 부분으로 구성됩니다:

• 앵커 멤버: 재귀를 시작하는 기본 쿼리를 정의하는 부분입니다.
• 재귀 멤버: CTE 자체를 참조하여 “재귀” 작업을 수행할 수 있게 하는 부분입니다.

Employees 테이블이 있다고 가정해보겠습니다. 각 행에는 EmployeeID, EmployeeName, ManagerID가 포함되어 있습니다. 특정 매니저의 직간접적인 보고자를 찾고 싶다면, 최상위 매니저를 식별하는 앵커 멤버로 시작합니다. 앵커 멤버는 EmployeeID = 1인 직원부터 시작합니다.

재귀 멤버는 이전 반복에서 ManagerID가 이전 EmployeeID와 일치하는 직원을 찾습니다. 각 반복은 계층의 다음 수준을 검색합니다.

WITH EmployeeHierarchy AS (
    -- 앵커 멤버: 최상위 매니저 선택
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- 최상위 매니저부터 시작
    UNION ALL
    -- 재귀 멤버: 현재 매니저에 보고하는 직원 찾기
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

SQL의 CTE의 잠재적인 문제 또는 제한 사항

CTE의 기능과 제한 사항을 이해하는 것은 논리적이고 가독성 있는 쿼리를 작성하는 데 중요합니다. 다양한 데이터베이스에서 CTE 사용 시 발생할 수 있는 일부 제한 사항과 잠재적인 문제에 대해 살펴보겠습니다.

SQL Server 및 Azure의 제한 사항

SQL Server 또는 Azure Synapse Analytics와 함께 SQL CTE를 사용할 때 특정 환경에 따른 제한 사항이 있습니다. 이에는 다음이 포함됩니다:

• SQL Server: 재귀 CTE의 기본 최대 재귀 수준은 100이며, OPTION (MAXRECURSION) 힌트를 사용하여 수정할 수 있습니다. 이 제한을 조정하지 않고 초과하는 경우 오류가 발생합니다. CTE는 직접적으로 다른 CTE 내에 중첩되거나 다른 CTE 내에서 정의될 수 없습니다.

• Azure Synapse Analytics: CTE는 INSERT, UPDATE, DELETE, MERGE와 같은 특정 SQL 작업에 대해 제한적인 지원을 제공합니다. 또한 Azure Synapse Analytics 클라우드 환경에서는 재귀 CTE를 지원하지 않아 특정 계층적 데이터 작업을 수행하는 능력이 제한됩니다.

SQL Server 작업 중이라면, DataCamp은 도움이 될 많은 자료를 제공합니다. 먼저, 데이터 분석을 위한 SQL Server 기초를 마스터하기 위해 DataCamp의 Introduction to SQL Server 과정을 추천합니다. SQL Server 개발자 커리어 트랙인 SQL Server Developer을 시도해볼 수 있으며, 트랜잭션 및 에러 처리부터 시계열 분석까지 모든 것을 다룹니다. Hierarchical and Recursive Queries in SQL Server 과정은 CTE를 활용한 고급 쿼리 작성 방법을 비롯한 SQL Server에서의 고급 쿼리 작성에 대해 직접 다루고 있습니다.

기타 잠재적인 문제들

CTE는 복잡한 쿼리를 단순화하는 데 유용하지만, 알아두어야 할 몇 가지 흔한 함정이 있습니다. 이러한 함정은 다음과 같습니다:

• 재귀 CTE에서 무한 루프: 재귀 CTE의 종료 조건이 충족되지 않으면 쿼리가 무한히 실행되는 무한 루프가 발생할 수 있습니다. 재귀 CTE가 무한히 실행되는 것을 피하려면 OPTION (MAXRECURSION N) 힌트를 사용하여 재귀 반복의 최대 횟수를 제한하십시오. 여기서 N은 지정된 한도입니다.

• 성능 고려 사항: 재귀 CTE는 재귀 깊이가 높거나 대규모 데이터 세트가 처리될 때 리소스를 많이 사용할 수 있습니다. 성능을 최적화하려면 각 반복에서 처리되는 데이터를 제한하고 과도한 재귀 수준을 피하기 위해 적절한 필터링을 보장하세요.

CTE와 다른 기술의 사용 시기

CTE는 반복 작업, 파생 테이블, 뷰 및 임시 테이블을 포함하는 쿼리를 간소화하는 데 적합하지만, 파생 테이블, 뷰 및 임시 테이블도 유사한 목적으로 사용됩니다. 다음 표는 각 방법의 장단점과 각각을 사용해야 하는 시기를 강조합니다.

결론

CTE를 숙달하는 데는 연습이 필요합니다. 제안으로, 능숙한 데이터 분석가가 되기 위해 DataCamp의 SQL에서 데이터 분석가로의 초급 커리어 트랙을 시도해보는 것을 권장합니다. SQL에서 보고서 작성 과정도 복잡한 보고서 및 대시보드 작성에 능숙해지는 데 도움이 될 것입니다. 마지막으로, 비즈니스 문제 해결에 SQL을 사용하는 능력을 자랑하고 다른 전문가들 사이에서 두드러지게 나타내기 위해 SQL Associate 인증을 획득해야 합니다.