오라클 WITH 절로 복잡한 쿼리 간소화하기

 

---

1. WITH 절이란?

WITH 절은 SQL에서 **공통 테이블 표현식(CTE, Common Table Expression)**을 정의할 때 사용됩니다.

• 복잡한 쿼리의 가독성을 높이고, 중복 서브쿼리를 제거하여 쿼리를 간소화하는 데 유용합니다.
• 일시적으로 정의된 테이블처럼 사용되며, 쿼리의 나머지 부분에서 참조할 수 있습니다.

---

2. WITH 절의 특징

1. 임시 테이블 정의
   • CTE는 쿼리가 실행되는 동안만 유효한 임시 테이블입니다.
2. 계층적 쿼리 가능
   • CTE 내부에서 다른 CTE를 참조하거나, 재귀적으로 정의할 수 있습니다.
3. 가독성 향상
   • 복잡한 서브쿼리를 별도의 블록으로 분리하여 쿼리 구조를 명확히 합니다.
4. 성능 최적화
   • 동일한 서브쿼리를 반복 실행하지 않고 한 번 계산한 결과를 재사용합니다.

---

3. WITH 절 사용법

기본 구조

WITH cte_name AS (
    -- 쿼리 정의
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

---

4. WITH 절을 활용한 간단한 예제

(1) 직원 데이터에서 부서별 평균 급여 조회

테이블: employees

employee_id department_id name salary

1	10	홍길동	5000000
2	10	이몽룡	4500000
3	20	성춘향	4800000
4	20	춘향이	5500000

WITH 절을 이용한 쿼리

WITH department_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN department_avg_salary d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

결과

name salary avg_salary

춘향이

5500000

5150000

---

5. 복잡한 쿼리에서의 WITH 절 활용

(1) 판매 데이터에서 월별 매출과 최고 매출 조회

테이블: sales

sale_id sale_date product_id amount

1	2024-01-10	101	20000
2	2024-01-15	102	30000
3	2024-02-05	101	15000
4	2024-02-20	103	40000

WITH 절을 이용한 쿼리

WITH monthly_sales AS (
    SELECT TO_CHAR(sale_date, 'YYYY-MM') AS sale_month, 
           SUM(amount) AS total_sales
    FROM sales
    GROUP BY TO_CHAR(sale_date, 'YYYY-MM')
),
max_sales AS (
    SELECT MAX(total_sales) AS highest_sales
    FROM monthly_sales
)
SELECT m.sale_month, m.total_sales, x.highest_sales
FROM monthly_sales m, max_sales x
WHERE m.total_sales = x.highest_sales;

결과

sale_month total_sales highest_sales

2024-02

55000

55000

---

6. 재귀적 WITH 절 사용

재귀 CTE를 이용한 계층적 데이터 조회
테이블: employees

employee_id name manager_id

1	홍길동	NULL
2	이몽룡	1
3	성춘향	2
4	춘향이	2

WITH 절을 이용한 재귀 쿼리

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;

결과

employee_id name manager_id level

1	홍길동	NULL	1
2	이몽룡	1	2
3	성춘향	2	3
4	춘향이	2	3

---

7. WITH 절의 장점

1. 가독성 향상
   • 복잡한 서브쿼리를 간소화하여 쿼리 구조를 명확하게 표현.
2. 재사용성 증가
   • 동일한 계산 결과를 여러 번 사용해야 하는 경우 효율적.
3. 성능 최적화
   • 중복 계산 방지로 성능 향상.
4. 유지보수 용이
   • 논리적으로 구분된 쿼리 블록 덕분에 수정이 간편.

---

8. 결론

WITH 절은 복잡한 SQL 쿼리를 간단하고 이해하기 쉽게 만드는데 매우 유용합니다.

• 데이터 가공, 집계, 계층적 구조 분석 등 다양한 상황에서 사용 가능하며,
• 성능 최적화와 가독성 향상에 기여합니다.
  복잡한 데이터를 다루는 쿼리를 작성할 때 WITH 절을 활용하여 더 효율적이고 유지보수 가능한 코드를 작성하세요.