with

with절이란?

• Common Table Expression (CTE)을 정의하는 데 사용
• CTE는 서브쿼리와 비슷하지만 더 읽기 쉽고 복잡한 쿼리를 작성하고 유지 보수하는 데 도움이 됨
• CTE는 주로 임시 결과 집합을 정의하고 이를 주요 쿼리에서 여러 번 참조할 수 있도록 함
• CTE는 가독성을 높이고 쿼리 구조를 단순화하는 데 유용함

 

CTE 기본 구조

WITH cte_name AS (
    -- CTE 정의 (서브쿼리)
    SELECT ...
)
-- 주요 쿼리
SELECT ...
FROM cte_name

 

 

코드로 보는 예제

아래 예제는 CTE를 사용하여 직원 테이블에서 각 부서의 평균 급여를 계산하고, 이를 주요 쿼리에서 참조하여 평균 급여가 특정 값 이상인 부서만 선택하는 예제이다.

 

WITH DepartmentAverageSalaries AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT DepartmentID, AvgSalary
FROM DepartmentAverageSalaries
WHERE AvgSalary > 60000;

 

 

CTE의 주요 특징

1. 임시적: CTE는 쿼리가 실행되는 동안만 존재하며, 결과는 쿼리의 나머지 부분에서 참조가능
2. 재귀적 CTE: CTE는 재귀적으로 정의할 수도 있어 계층 구조나 반복 처리를 쉽게 수행가능
3. 가독성: CTE를 사용하면 복잡한 쿼리를 여러 부분으로 나눠서 쉽게 읽고 이해가능
4. 다중 참조: CTE는 쿼리 내에서 여러 번 참조할 수 있으므로 중복 코드를 줄이기 가능

코드를 통한 CTE 이해

-- CTE를 정의합니다. 'co'는 CTE의 이름입니다.
WITH co AS (
    -- CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 각 자동차의 대여 기록을 조회합니다.
    SELECT CAR_ID, 
        -- '2022-10-16'이 START_DATE와 END_DATE 사이에 있는지 확인합니다.
        -- 만약 그렇다면 1을 반환하고, 그렇지 않다면 0을 반환합니다.
        CASE 
            WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN 1
            ELSE 0 
        END AS coo
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

-- CTE를 사용하여 최종 결과를 선택합니다.
SELECT CAR_ID, 
    -- CTE의 결과를 사용하여 각 자동차의 대여 가능 여부를 결정합니다.
    CASE 
        WHEN SUM(coo) = 1 THEN '대여중' 
        ELSE '대여 가능' 
    END AS AVAILABILITY
FROM co
-- CAR_ID별로 그룹화하여 각 자동차의 대여 가능 여부를 계산합니다.
GROUP BY CAR_ID
-- 결과를 CAR_ID 내림차순으로 정렬합니다.
ORDER BY 1 DESC;

 

 

상세 설명:

1. CTE 정의 (WITH co AS):
   • CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 특정 날짜('2022-10-16')에 각 자동차(CAR_ID)가 대여 중인지 확인한다.
   • CASE 문을 사용하여 해당 날짜가 대여 기간(START_DATE와 END_DATE 사이)에 포함되면 1, 그렇지 않으면 0을 반환합니다.
   • 결과는 coo라는 열로 저장된다.
2. 주요 쿼리:
   • co라는 CTE를 참조하여 각 자동차의 대여 가능 여부를 계산한다.
   • SUM(coo)가 1이면 해당 날짜에 대여 중으로 간주하고, 0이면 대여 가능으로 간주한다.
   • 각 CAR_ID별로 그룹화(GROUP BY CAR_ID)하여 대여 가능 여부를 계산하고, CAR_ID 내림차순으로 정렬(ORDER BY 1 DESC)하여 결과를 출력한다.

비재귀적 CTE

 

비재귀적 CTE는 단순히 서브쿼리를 대신하는 역할을 합니다. 이전 예제들처럼, 특정 조건을 만족하는 데이터를 임시로 저장하고 이를 주요 쿼리에서 사용하는 경우

 

WITH SalesCTE AS (
    SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY SalesPersonID
)
SELECT SalesPersonID, TotalSales
FROM SalesCTE
WHERE TotalSales > 100000;

 

위 예제는 각 판매원의 총 판매 금액을 계산하고, 100,000 이상의 판매 금액을 기록한 판매원만 선택한다.

 

재귀적CTE 

재귀적 CTE는 자기 자신을 참조하여 반복 처리를 수행할 수 있다. 주로 계층 구조 데이터(예: 조직도, 트리 구조 데이터)를 처리하는 데 사용된다. 

 

밑에 있는 SQL문은 조직도에서 직원과 그 상사를 찾는 쿼리이다.

WITH EmployeeHierarchy AS (
    -- Anchor member: 상사가 없는 최고위 직원 찾기
    SELECT EmployeeID, ManagerID, EmployeeName, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive member: 상사가 있는 직원 찾기
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
-- 최종 결과 출력
SELECT EmployeeID, ManagerID, EmployeeName, Level
FROM EmployeeHierarchy
ORDER BY Level, ManagerID;

 

EmployeeHierarchy라는 재귀적 CTE를 사용하여 각 직원의 계층 수준을 계산하고, 전체 조직도를 계층 순으로 출력한다.