데이터베이스 with 쿼리 CTE 쿼리 사용하는 방법

 

 

 

WITH 절(공통 테이블 표현식, CTE)은 SQL에서 복잡한 쿼리를 간소화하고 가독성을 높이기 위해 사용됩니다.

 

이를 통해 서브쿼리를 정의하고 나중에 메인 쿼리에서 참조할 수 있습니다.

 

WITH 절을 사용하면 반복적으로 사용되는 서브쿼리를 한 번만 정의하여 재사용하거나, 단계적으로 쿼리를 작성할 수 있습니다.

 

1. 기본 구문

WITH cte_name AS ( SELECT column1, column2, ... FROM table_name WHERE condition ) 
SELECT column1, column2, ... 
FROM cte_name
WHERE condition;

 

 

• cte_name: 공통 테이블 표현식의 이름.
• AS: 공통 테이블 표현식의 본문을 정의.
• SELECT 문: WITH 절에서 정의한 결과를 사용하는 메인 쿼리

 

2. 예시

예시 1: 기본 사용

WITH Sales_CTE AS ( SELECT region, SUM(sales_amount) AS total_sales FROM Sales GROUP BY region ) 
SELECT region, total_sales 
FROM Sales_CTE 
WHERE total_sales > 10000;

 

 

• 설명:
  • Sales_CTE는 region 별로 sales_amount 합계를 구합니다.
  • 메인 쿼리에서는 이 결과를 이용하여 total_sales가 10,000 이상인 항목만 필터링합니다.

 

예시 2: 여러 CTE 사용

 

WITH Employee_CTE AS ( SELECT department_id, COUNT(*) AS employee_count FROM Employees GROUP BY department_id ), 
Department_CTE AS ( SELECT department_id, department_name FROM Departments ) 
SELECT d.department_name, e.employee_count 
FROM Department_CTE d JOIN Employee_CTE e ON d.department_id = e.department_id 
WHERE e.employee_count > 5;

 

 

 

• 설명:
  • Employee_CTE: 각 부서별 직원 수를 계산
  • Department_CTE: 부서 ID와 부서 이름을 선택
  • 메인 쿼리에서 두 CTE를 조인하여 직원 수가 5명 이상인 부서의 이름과 직원 수를 출력

예시 3: 재귀 CTE

WITH RECURSIVE OrgChart AS ( SELECT employee_id, manager_id, employee_name FROM Employees WHERE manager_id IS NULL -- Top-level manager 
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 employee_name 
FROM OrgChart;

 

 

 

• 설명:
  • 재귀 CTE를 사용하여 조직도(조직 계층)를 생성합니다.
  • 최상위 관리자를 기준으로 하위 직원들을 재귀적으로 조회합니다.

 

3. 장점

• 가독성: 복잡한 쿼리를 단계별로 분리하여 이해하기 쉽게 만듭니다.
• 재사용성: 서브쿼리를 여러 번 사용할 때, WITH로 정의한 CTE를 여러 번 참조할 수 있습니다.
• 간결성: 긴 쿼리를 간결하게 작성할 수 있습니다.
• 재귀적 표현: 재귀 CTE를 통해 계층 구조나 반복적인 데이터를 처리할 수 있습니다.
  
  

4. 주의사항

• CTE는 일시적으로 사용되며, 쿼리 실행 중에만 존재합니다. 데이터베이스에 저장되거나 다른 세션에서 사용할 수 없습니다.
• 일부 데이터베이스에서는 CTE 성능이 서브쿼리에 비해 다를 수 있으므로 성능 테스트가 필요합니다.
  
  

5. 지원하는 데이터베이스

• MySQL: 8.0 이상에서 지원.
• PostgreSQL: 지원.
• SQL Server: 2005 이상에서 지원.
• Oracle: 11g 이상에서 지원.
• CUBRID: 지원
  
  

6. 응용 예시

예시 4: 복잡한 필터링 및 집계

WITH FilteredOrders AS ( SELECT order_id, customer_id, order_date FROM Orders WHERE order_date >= '2024-01-01' ), 
AggregatedSales AS ( SELECT customer_id, COUNT(order_id) AS total_orders FROM FilteredOrders GROUP BY customer_id ) 
SELECT customer_id, total_orders 
FROM AggregatedSales 
WHERE total_orders > 5;

 

 

• 설명:
  • FilteredOrders는 2024년 이후의 주문을 필터링합니다.
  • AggregatedSales는 각 고객별 주문 수를 집계합니다.
  • 최종 쿼리에서는 주문 수가 5건 이상인 고객만 반환합니다.