PostgreSQL 쿼리의 마법사: CTE(Common Table Expressions) 완벽 이해

PostgreSQL에서 SQL 쿼리를 작성하는 일은 때때로 복잡한 미로를 헤매는 것처럼 느껴질 수 있습니다. 여러 테이블을 조인하거나 서브쿼리를 반복적으로 쌓아야 할 때, 코드가 길어지고 가독성이 떨어지기 마련이죠. 하지만 걱정 마세요! 이 문제를 혁신적으로 해결할 수 있는 강력한 무기가 있습니다. 바로 CTE(Common Table Expressions) 입니다. CTE를 활용하면 쿼리의 가독성, 유지보수성, 그리고 효율성을 한층 업그레이드할 수 있어요. 이 포스트에서는 CTE의 기본부터 실전 예시, 그리고 고급 팁까지 자세히 알아보겠습니다.

728x90

CTE란 무엇인가요?

CTE는 PostgreSQL(그리고 대부분의 현대 SQL 데이터베이스)에서 쿼리 구조를 더 모듈화하고 읽기 쉽게 만드는 기능입니다. 간단히 말해, 임시 결과 집합을 정의하는 방법으로, 메인 쿼리 내에서 여러 번 재사용할 수 있어요.

• WITH 절로 시작하며, 하나 이상의 명명된 쿼리를 정의합니다.
• 각 CTE는 메인 쿼리가 실행되는 동안만 존재하는 '가상 테이블'처럼 작동합니다.
• SELECT, INSERT, UPDATE, DELETE 문과 함께 사용할 수 있지만, 가장 흔한 용도는 복잡한 SELECT 쿼리입니다.

CTE의 핵심은 서브쿼리를 외부로 추출하여 쿼리를 계층적으로 만드는 데 있습니다. 이로 인해 코드가 더 직관적이고, 디버깅도 쉬워집니다. PostgreSQL 8.4 버전부터 지원되며, 표준 SQL이므로 다른 DBMS로 이식하기도 용이해요.

CTE 사용의 주요 이점

CTE는 단순한 문법적 편의가 아닙니다. 실제로 쿼리 개발과 유지보수에 큰 가치를 더해줍니다. 아래는 주요 이점입니다.

1. 향상된 가독성

복잡한 쿼리를 작은 '블록'으로 나누면, 전체 로직을 한눈에 파악할 수 있습니다. 예를 들어, 긴 서브쿼리를 CTE로 분리하면 마치 함수처럼 재사용 가능해지죠. 이는 팀 프로젝트에서 코드 리뷰를 촉진하고, 버그를 조기 발견하는 데 큰 도움이 됩니다. 실제로, CTE를 사용한 쿼리는 초보자도 쉽게 이해할 수 있어 교육 효과도 높아요.

2. 재사용성

하나의 CTE를 메인 쿼리 내에서 여러 번 호출할 수 있습니다. 서브쿼리를 반복 작성할 필요가 없어 코드 중복이 사라지죠. 만약 로직이 변경되면 CTE 정의 한 곳만 수정하면 되므로, 유지보수 비용이 절감됩니다. 특히 대규모 데이터 분석 쿼리에서 빛을 발합니다.

3. 재귀 쿼리 지원

CTE의 진짜 마법은 재귀(Recursive) 기능입니다. WITH RECURSIVE 키워드를 사용하면 쿼리가 자신을 반복 호출할 수 있어요. 이는 계층적 데이터(예: 조직도, 카테고리 트리, 그래프 구조)를 처리할 때 필수적입니다. 일반 루프나 프로시저 없이도 순수 SQL로 복잡한 재귀 로직을 구현할 수 있죠. PostgreSQL은 이 기능을 매우 효율적으로 최적화해 성능 저하를 최소화합니다.

추가 팁: CTE는 쿼리 실행 계획을 분석할 때도 유용합니다. EXPLAIN ANALYZE와 함께 사용하면 각 CTE의 비용을 개별적으로 확인할 수 있어요.

기본적인 CTE 정의 구문

CTE의 구조는 간단합니다. 아래는 기본 템플릿입니다:

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

• cte_name: CTE의 별칭(이름).
• 괄호 안: CTE를 생성하는 SELECT 쿼리.
• 아래 SELECT: CTE를 참조하는 메인 쿼리.

여러 CTE를 정의할 수도 있어요. 쉼표(,)로 구분하면 됩니다:

WITH cte1 AS (...),
     cte2 AS (...)
SELECT * FROM cte2;

실제 예시: CTE의 강력함

이론만으로는 부족하죠? 실제 시나리오에서 CTE가 어떻게 빛나는지 두 가지 예시로 확인해 보세요.

예시 1: 복잡한 조인 단순화

employees 테이블(직원: id, name, department_id)과 departments 테이블(부서: id, department_name)이 있다고 가정합니다. 부서에 5명 이상의 직원이 있는 모든 직원의 이름과 부서 이름을 찾는 쿼리를 작성해 보죠.

CTE 없이 (서브쿼리 중첩):

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 5
);

이 쿼리는 작동하지만, 중첩으로 인해 로직이 숨겨져 가독성이 떨어집니다.

CTE 사용:

WITH dept_counts AS (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING COUNT(*) > 5
)
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id IN (SELECT department_id FROM dept_counts);

dept_counts CTE가 부서 필터링을 담당하니 메인 쿼리가 훨씬 명확해집니다. 재사용성을 위해 CTE를 더 확장할 수도 있어요(예: COUNT(*)를 추가로 반환).

예시 2: 재귀 쿼리 – 조직 차트 탐색

employees 테이블에 id, name, manager_id 열이 있다고 해요. 최상위 관리자부터 모든 하위 직원을 재귀적으로 가져오는 조직 차트를 만들어보죠.

WITH RECURSIVE org_chart AS (
    -- Anchor: 최상위 관리자 (manager_id가 NULL)
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: 하위 직원 재귀 호출
    SELECT e.id, e.name, e.manager_id, o.level + 1
    FROM employees e
    INNER JOIN org_chart o ON o.id = e.manager_id
)
SELECT id, name, manager_id, level
FROM org_chart
ORDER BY level, id;

• Anchor 부분: 재귀의 시작점(최상위 관리자).
• Recursive 부분: 이전 결과를 조인해 자신을 호출.
• 추가로 level 열을 더해 깊이를 표시했습니다.

이 쿼리는 무한 루프를 방지하기 위해 PostgreSQL의 기본 제한(최대 100,000 반복)을 활용합니다. 계층 데이터 처리에 필수적이에요!

고급 팁: CTE를 더 효과적으로 활용하기

• 성능 최적화: CTE는 뷰처럼 물리화되지 않지만, 복잡한 경우 임시 테이블로 대체 고려. MATERIALIZED 키워드(PostgreSQL 12+)로 영속화할 수 있습니다.
• 다중 CTE 체이닝: CTE1의 결과를 CTE2에서 사용해 복잡한 파이프라인 구축.
• 에러 핸들링: 재귀 CTE에서 순환 참조(예: A가 B를, B가 A를 관리)를 방지하려면 CYCLE 절 사용.
• 대안 비교: 서브쿼리 vs. CTE – 가독성 우선이라면 CTE, 간단한 경우 서브쿼리.

결론

Common Table Expressions(CTE)는 PostgreSQL 쿼리의 '마법 지팡이'입니다. 복잡한 로직을 단순화하고, 재귀를 통해 고급 데이터 탐색을 가능하게 하며, 장기적으로 코드 품질을 높여줍니다. 초보자라면 기본 예시부터, 중급자라면 재귀와 최적화를 도전해 보세요. CTE를 마스터하면 SQL이 더 즐거워질 거예요! 지금 당장 PostgreSQL 콘솔을 열고 실험해 보는 건 어떨까요?