PostgreSQL에서 WITH 절을 사용하는 방법(재귀 쿼리 포함)

PostgreSQL WITH 절이란 무엇인가요?

PostgreSQL에서 WITH 절은 공통 테이블 표현식(CTE, Common Table Expression)을 정의할 때 사용됩니다. CTE는 SQL 쿼리 내에서 임시 결과 집합을 정의하여 가독성을 높이고 복잡한 쿼리를 단순화합니다. 특히, 반복되는 서브쿼리를 피할 수 있어 쿼리 성능을 개선하는 데 도움이 됩니다.

왜 PostgreSQL WITH 절을 사용해야 하나요?

1. 가독성 향상: 복잡한 쿼리를 이해하기 쉽게 만듭니다.
2. 재사용성: 동일한 쿼리를 여러 번 사용할 때 유용합니다.
3. 성능 향상: 서브쿼리의 반복 실행을 피할 수 있습니다.

PostgreSQL WITH 절 사용 방법

PostgreSQL에서 WITH 절을 사용하는 기본 구문은 다음과 같습니다:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

예제 1: 기본적인 WITH 절 사용

다음은 직원 테이블에서 부서별 평균 급여를 계산하는 예제입니다.

WITH dept_avg AS (
    SELECT dept_no, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_no
)
SELECT e.emp_no, e.emp_name, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.dept_no = d.dept_no;

이 예제에서 dept_avg라는 CTE를 정의하고, 이를 메인 쿼리에서 조인하여 사용합니다.

예제 2: 재귀적 CTE 사용

재귀적 CTE는 자기 자신을 참조하여 반복 작업을 수행할 때 유용합니다. 다음은 계층 구조를 가진 조직도에서 직원의 상위 관리자들을 찾는 예제입니다.

WITH RECURSIVE managers AS (
    SELECT emp_no, emp_name, manager_no
    FROM employees
    WHERE manager_no IS NULL
    UNION ALL
    SELECT e.emp_no, e.emp_name, m.manager_no
    FROM employees e
    JOIN managers m ON e.manager_no = m.emp_no
)
SELECT * FROM managers;

예제 3: 다중 CTE 사용

여러 CTE를 동시에 정의하고 사용하는 것도 가능합니다.

WITH sales AS (
    SELECT emp_no, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY emp_no
),
employees_sales AS (
    SELECT e.emp_no, e.emp_name, s.total_sales
    FROM employees e
    JOIN sales s ON e.emp_no = s.emp_no
)
SELECT * FROM employees_sales;

결론

PostgreSQL의 WITH 절은 복잡한 쿼리를 단순화하고 가독성을 높이며 성능을 향상시키는 강력한 도구입니다. CTE를 사용하면 재사용 가능한 쿼리 블록을 정의할 수 있어, 특히 복잡한 데이터 분석 작업에서 매우 유용합니다. PostgreSQL에서 WITH 절을 활용하여 더 효율적이고 깔끔한 쿼리를 작성해보세요.

---

이 블로그 글을 통해 PostgreSQL에서 WITH 절을 효과적으로 사용하는 방법을 이해하고, 데이터베이스 쿼리를 더 효율적으로 작성하는 데 도움이 되길 바랍니다.