[PostgreSQL] WITH 문법 상황별 사용 예제

PostgreSQL에서 WITH 문법은 **공통 테이블 표현식(CTE, Common Table Expression)**을 정의할 때 유용하게 사용됩니다. CTE를 사용하면 복잡한 쿼리를 가독성 있게 작성할 수 있으며, 중복된 서브쿼리를 줄여 성능을 개선할 수 있습니다. 이번 글에서는 WITH 문법을 상황별로 어떻게 활용할 수 있는지 다양한 예제를 통해 설명하겠습니다.

---

1. 기본 사용법: 단순한 CTE 사용

WITH 구문은 서브쿼리를 메인 쿼리 전에 미리 정의하고, 그 결과를 메인 쿼리에서 참조할 수 있게 합니다. 이를 통해 복잡한 쿼리를 분리하고, 코드 가독성을 높일 수 있습니다.

예제: 특정 사용자의 최근 주문을 조회하는 쿼리

WITH recent_orders AS (
    SELECT order_id, user_id, order_date
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT *
FROM recent_orders
WHERE user_id = 123;

 

여기서 recent_orders는 최근 30일 동안의 주문 내역을 저장하는 임시 테이블 역할을 합니다. 이후 메인 쿼리에서 특정 user_id의 데이터를 쉽게 조회할 수 있습니다.

---

2. 여러 CTE 사용하기

PostgreSQL에서는 여러 개의 CTE를 동시에 정의할 수 있습니다. 이 경우 각 CTE는 쉼표로 구분하여 작성하며, 메인 쿼리에서 순차적으로 사용할 수 있습니다.

예제: 최근 주문 내역과 배송 정보 조인하기

WITH recent_orders AS (
    SELECT order_id, user_id, order_date
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
shipping_info AS (
    SELECT order_id, shipping_date, shipping_status
    FROM shipping
    WHERE shipping_status = 'SHIPPED'
)
SELECT o.order_id, o.order_date, s.shipping_date, s.shipping_status
FROM recent_orders o
JOIN shipping_info s ON o.order_id = s.order_id;

 

이 예제에서는 recent_orders와 shipping_info라는 두 개의 CTE를 사용해 주문 정보와 배송 상태를 결합하여 조회합니다.

---

3. 재귀적 CTE 사용

PostgreSQL의 CTE는 재귀적으로 사용될 수도 있습니다. 이를 통해 계층적 데이터 구조를 처리하거나 특정 조건을 만족할 때까지 반복 연산을 수행할 수 있습니다.

예제: 계층 구조를 가진 직원 조직도 조회하기

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

 

이 예제에서는 employee_hierarchy라는 재귀적 CTE를 통해 각 직원의 레벨을 계산하고, 직원 간의 계층 구조를 조회합니다. UNION ALL을 사용하여 재귀적으로 상위 관리자부터 하위 직원까지의 계층을 쌓아나갑니다.

---

4. CTE를 통한 데이터 업데이트

CTE는 INSERT, UPDATE, DELETE와 같은 데이터 변경 작업에도 사용할 수 있습니다. 이를 통해 데이터 변경 작업을 분리하여 더 명확하게 수행할 수 있습니다.

예제: 최근 1년 동안 활동하지 않은 사용자의 상태를 업데이트하기

WITH inactive_users AS (
    SELECT user_id
    FROM users
    WHERE last_login < CURRENT_DATE - INTERVAL '1 year'
)
UPDATE users
SET status = 'INACTIVE'
WHERE user_id IN (SELECT user_id FROM inactive_users);

 

여기서는 inactive_users라는 CTE를 사용해 1년 이상 활동하지 않은 사용자를 조회하고, 메인 쿼리에서 해당 사용자들의 status를 INACTIVE로 업데이트합니다.

---

5. CTE를 이용한 데이터 집계와 필터링

CTE는 데이터를 미리 집계하거나 특정 조건으로 필터링할 때 유용합니다. 이를 통해 쿼리의 구조를 단순화하고, 최종적으로 필요한 데이터만을 메인 쿼리에서 쉽게 처리할 수 있습니다.

예제: 월별 매출을 집계하여 특정 월 매출이 평균보다 높은 월만 조회하기

WITH monthly_sales AS (
    SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total_sales
    FROM orders
    GROUP BY 1
),
average_sales AS (
    SELECT AVG(total_sales) AS avg_sales
    FROM monthly_sales
)
SELECT month, total_sales
FROM monthly_sales
WHERE total_sales > (SELECT avg_sales FROM average_sales);

 

이 예제에서는 월별 매출을 집계한 monthly_sales와 평균 매출을 계산한 average_sales라는 두 개의 CTE를 사용하여, 평균보다 매출이 높은 달만을 조회하고 있습니다.

---

 

PostgreSQL의 WITH 문법은 쿼리의 가독성을 높이고 복잡한 쿼리를 쉽게 관리할 수 있게 해주는 강력한 도구입니다. 상황에 따라 단순 조회, 다중 CTE 결합, 재귀적 조회, 데이터 갱신, 집계 등 다양한 방식으로 활용할 수 있습니다.