공통 테이블 식(CTE)
PostgreSQL의 CTE(WITH 쿼리)란 무엇인가요?
WITH로 작성하는 공통 테이블 식은 서브쿼리에 이름을 붙여 문의 나머지 부분에서 테이블처럼 사용할 수 있게 합니다. WITH RECURSIVE는 트리와 계층 구조를 처리합니다.
공통 테이블 식(CTE)은 WITH 절 안의 보조 문으로, 단일 쿼리를 위한 이름 있는 임시 결과를 정의합니다. 복잡한 쿼리를 읽기 쉬운 단계로 나누어 주며, 보조 문은 데이터를 삽입, 수정, 삭제하는 INSERT, UPDATE, DELETE 문일 수도 있습니다.
WITH RECURSIVE를 사용하면 CTE가 자신의 출력을 참조할 수 있으며, 이는 보고 라인이나 중첩 카테고리 같은 계층 구조를 탐색하는 표준적인 방법입니다. 재귀적이지 않고 부작용이 없으며 쿼리에서 한 번만 참조되는 CTE는 외부 쿼리에 합쳐져 함께 최적화됩니다. 별도로 평가하게 하려면 MATERIALIZED를, 합치기를 요청하려면 NOT MATERIALIZED를 추가하세요.
예제
이 예제는 한 직원에서 시작해 WITH RECURSIVE로 관리 체계를 따라 올라가며, 각 사람과 시작점으로부터의 거리를 반환합니다.
CREATE TABLE employees (
id int PRIMARY KEY,
name text NOT NULL,
manager_id int REFERENCES employees (id)
);
INSERT INTO employees VALUES (1, 'Ana', NULL), (2, 'Ben', 1), (3, 'Chen', 2), (4, 'Dee', 1);
WITH RECURSIVE chain AS (
SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 3
UNION ALL
SELECT e.id, e.name, e.manager_id, c.depth + 1
FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT name, depth FROM chain ORDER BY depth;확장 없이 기본 PostgreSQL 16 또는 17에서 실행되는 일반 SQL입니다. 버려도 되는 빈 데이터베이스에서 실행하세요.
출처
PostgreSQL 공식 문서(영문)입니다.