PostgreSQL - WITH 절: 초보자를 위한 친절한 가이드

안녕하세요, 데이터베이스 열정가 되고자 하는 여러분! 오늘 우리는 PostgreSQL의 세계로 흥미로운 여행을 떠나게 될 것입니다. 특히 강력한 WITH 절에 대해 탐구해보겠습니다. 프로그래밍에 처음이라도 걱정하지 마세요; 여러분의 친절한 안내자로서 단계별로 설명해드리겠습니다. 커피 한 잔을 들고, 함께 빠져보겠습니다!

WITH 절이란?

WITH 절, 즉 공통 테이블 표현식(CTEs)은 PostgreSQL에서 우리가 더 큰 쿼리 내에서 보조적인 문장을 작성할 수 있게 해주는 마법의 도구 상자입니다. 이를 통해 임시로 명명된 결과 집합을 만들어 SELECT, INSERT, UPDATE, DELETE, 또는 MERGE 문에서 참조할 수 있습니다.

WITH 절을 사용하는 이유는 무엇인가요?

1. 가독성 향상
2. 복잡한 쿼리 단순화
3. 재귀 쿼리 가능

간단한 예제로 시작해보겠습니다:

WITH employee_salaries AS (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
)
SELECT department, avg_salary
FROM employee_salaries
WHERE avg_salary > 50000;

이 예제에서 우리는 각 부서의 평균 급여를 계산하는 임시 결과 집합 employee_salaries를 만들고, 이 결과 집합을 사용하여 평균 급여가 $50,000 이상인 부서를 찾습니다.

기본 문법과 사용법

WITH 절의 기본 문법은 다음과 같습니다:

WITH cte_name AS (
CTE_query_definition
)
SELECT * FROM cte_name;

여기서 cte_name은 여러분이 Common Table Expression에 부여하는 이름이고, CTE_query_definition은 결과 집합을 정의하는 쿼리입니다.

다른 예제를 보겠습니다:

WITH high_value_orders AS (
SELECT customer_id, SUM(order_total) as total_value
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 1000
)
SELECT c.customer_name, h.total_value
FROM customers c
JOIN high_value_orders h ON c.customer_id = h.customer_id;

이 쿼리에서 우리는 $1000 이상의 고액 주문을 가진 고객을 식별하고, 이 정보를 고객 테이블과 결합하여 그들의 이름을 얻습니다.

여러 개의 CTE

WITH 절의 멋진 점 중 하나는 단일 쿼리에서 여러 개의 CTE를 정의할 수 있다는 것입니다. 주 쿼리에 대한 여러 명의 도우미를 가지는 것과 같습니다!

WITH
top_products AS (
SELECT product_id, SUM(quantity) as total_sold
FROM order_items
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 5
),
product_revenue AS (
SELECT product_id, SUM(quantity * price) as revenue
FROM order_items
GROUP BY product_id
)
SELECT p.product_name, t.total_sold, r.revenue
FROM products p
JOIN top_products t ON p.product_id = t.product_id
JOIN product_revenue r ON p.product_id = r.product_id;

이 쿼리는 첫 번째로 상위 5개 판매 제품을 식별하고, 모든 제품의 수익을 계산한 다음, 제품 이름과 결합합니다.

재귀 WITH

이제 더 고급 영역으로 나아가겠습니다: 재귀 CTE. 이 것은 계층적이거나 트리 구조의 데이터를 다루는 데 특히 유용합니다.

재귀 CTE의 문법은 다음과 같습니다:

WITH RECURSIVE cte_name AS (
non_recursive_term
UNION [ALL]
recursive_term
)
SELECT * FROM cte_name;

수열의 숫자를 생성하는 고전적인 예제를 보겠습니다:

WITH RECURSIVE number_sequence AS (
SELECT 1 as n
UNION ALL
SELECT n + 1
FROM number_sequence
WHERE n < 10
)
SELECT * FROM number_sequence;

이 쿼리는 1에서 10까지의 숫자 시퀀스를 생성합니다. 비재귀 항목은 1로 시작하고, 재귀 항목은 이전 숫자에 1을 더하여 10에 도달할 때까지 반복합니다.

더 실질적인 예제: 조직 계층 구조

employees 테이블이 employee_id, name, manager_id 열을 가지고 있다고 가정해 봅시다. 우리는 재귀 CTE를 사용하여 전체 조직 계층 구조를 표시할 수 있습니다:

WITH RECURSIVE org_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, oh.level + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT employee_id, name, level
FROM org_hierarchy
ORDER BY level, employee_id;

이 쿼리는 최고 관리자(manager_id가 NULL인 경우)에서 시작하여 각 관리자 아래 있는 모든 직원을 재귀적으로 찾아 레벨을 할당합니다.

좋은 관행과 팁

1. 이름: CTE에 명확하고 설명적인 이름을 선택하세요.
2. 복잡성: 복잡한 쿼리를 더 작은, 관리 가능한 CTE로 나눕시다.
3. 성능: CTE는 가독성을 향상시키지만, 과도한 사용은 성능에 영향을 줄 수 있습니다.
4. 재귀: 재귀 CTE 사용 시 무한 루프를 피하십시오.

WITH 절과 함께 사용되는 일반 방법

WITH 절과 함께 사용되는 일반 방법을 요약한 표입니다:

방법	설명	예제
SELECT	CTE에서 데이터检索	SELECT * FROM cte_name
JOIN	CTE와 다른 테이블 결합	SELECT * FROM table JOIN cte_name ON ...
UNION	여러 CTE의 결과 결합	WITH cte1 AS (...), cte2 AS (...) SELECT * FROM cte1 UNION SELECT * FROM cte2
INSERT	CTE를 사용하여 데이터 삽입	WITH cte AS (...) INSERT INTO table SELECT * FROM cte
UPDATE	CTE를 사용하여 데이터 업데이트	WITH cte AS (...) UPDATE table SET ... FROM cte WHERE ...
DELETE	CTE를 사용하여 데이터 삭제	WITH cte AS (...) DELETE FROM table USING cte WHERE ...

실습을 통해 완성도를 높이세요! 자신의 PostgreSQL 환경에서 이 개념들을 실험해보세요. 얼마 지나지 않아 데이터베이스 마에스트로의 풍격을 갖춘 복잡한 쿼리를 작성할 수 있을 것입니다!

Happy querying, and may your data always be well-structured and your queries optimized!

Credits: Image by storyset