PostgreSQL WITH 절

PostgreSQL에서 WITH 문은 더 큰 쿼리에서 사용하기 위해 보조 문장을 작성하는 방법을 제공합니다.

WITH 문은 복잡한 대형 쿼리를 더 간단한 형태로 분할하여 읽기 쉽게 만드는 데 도움이 됩니다. 이러한 문장은 일반적인 테이블 표현식(CTE)으로도 불리며, 쿼리를 위해 존재하는 일시적인 테이블으로 사용할 수 있습니다.

WITH 절은 여러 번 실행되는 서브 쿼리를 특히 유용하며, 이름을 통해 여러 번 참조할 수 있습니다.

WITH 절은 사용하기 전에 먼저 정의해야 합니다.

문법

WITH 쿼리의 기본 문법은 다음과 같습니다：

WITH
　　　name_for_summary_data AS (
　　　　　　SELECT Statement)
　　　SELECT columns
　　　FROM name_for_summary_data
　　　WHERE conditions <=> (
　　　　　　SELECT column
　　　　　　FROM name_for_summary_data)
　　　[ORDER BY columns]

name_for_summary_data 는 WITH 절의 이름입니다name_for_summary_data 이미 있는 테이블 이름과 동일할 수 있으며, 우선순위가 있습니다.

WITH 절에서 데이터 INSERT, UPDATE 또는 DELETE 문을 사용할 수 있으며, 하나의 쿼리에서 여러 가지 다른 작업을 수행할 수 있습니다.

WITH RECURSIVE

WITH 절에서 자신의 출력 데이터를 사용할 수 있습니다.

공용 테이블 표현식 (CTE)는 중요한 장점을 가지고 있으며, 자신을 참조하여 재귀 CTE를 생성할 수 있습니다. 재귀 CTE는 초기 CTE를 반복적으로 실행하여 데이터 부분집합을 반환하고, 완전한 결과 집합을 얻을 때까지 반복하는 공용 테이블 표현식입니다.

온라인 예제

COMPANY 테이블 생성（COMPANY SQL 파일 다운로드 ），데이터 내용은 다음과 같습니다：

w3codeboxdb# select　*　from COMPANY;
　id　|　name　　|　age　|　address　　|　salary
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　|　California|　　20000
　　2　|　Allen　|　　25　|　Texas　　|　　15000
　　3　|　Teddy　|　　23　|　Norway　　|　　20000
　　4　|　Mark　　|　　25　|　Rich-Mond　|　　65000
　　5　|　David　|　　27　|　Texas　　|　　85000
　　6　|　Kim　　　|　　22　|　South-Hall|　　45000
　　7　|　James　|　　24　|　Houston　　|　　10000
(7　rows)

WITH 절을 사용하여 상단 테이블에서 데이터를 조회하겠습니다：

With CTE AS
(Select
　ID
,　NAME
,　AGE
,　ADDRESS
,　SALARY
FROM COMPANY　)
Select　*　From CTE;

결과는 다음과 같습니다：

id　|　name　　|　age　|　address　　|　salary
----+-------+-----+-----------+--------
　　1　|　Paul　　|　　32　|　California|　　20000
　　2　|　Allen　|　　25　|　Texas　　|　　15000
　　3　|　Teddy　|　　23　|　Norway　　|　　20000
　　4　|　Mark　　|　　25　|　Rich-Mond　|　　65000
　　5　|　David　|　　27　|　Texas　　|　　85000
　　6　|　Kim　　　|　　22　|　South-Hall|　　45000
　　7　|　James　|　　24　|　Houston　　|　　10000
(7　rows)

그래서 다음에 사용하겠습니다 RECURSIVE 고유 키워드와 WITH 절을 사용하여 쿼리를 작성하여 찾으십시오 SALARY(급여) 필드가 작습니다 20000 데이터를 계산하고 합을 구하십시오：

WITH RECURSIVE t(n) AS (
　　　VALUES (0)
　　　UNION　ALL
　　　SELECT　SALARY　FROM　COMPANY　WHERE　SALARY　<　20000
)
SELECT　sum(n)　FROM　t;

결과는 다음과 같습니다：

　sum
-------
　25000
(1　row)

아래에서 COMPANY 표와 유사한 COMPANY1 표에서 DELETE 문과 WITH 절을 사용하여 COMPANY 표에서 SALARY(급여) 필드가 30000 데이터에 이동시키고, 삭제된 데이터를 COMPANY1 표를 통해 COMPANY 표의 데이터를 COMPANY1 표에서：

CREATE　TABLE　COMPANY1(
　　　ID　INT　PRIMARY　KEY　　　　　NOT　NULL,
　　　NAME　　　　　　　　　　　TEXT　　　　NOT　NULL,
　　　AGE　　　　　　　　　　　　INT　　　　　NOT　NULL,
　　　ADDRESS　　　　　　　　CHAR(50),
　　　SALARY　　　　　　　　　REAL
);
WITH　moved_rows　AS　(
　　　DELETE　FROM　COMPANY
　　　WHERE
　　　　　　SALARY　>=　30000
　　　RETURNING　*
)
INSERT　INTO　COMPANY1　(SELECT　*　FROM　moved_rows);

결과는 다음과 같습니다：

INSERT　0　3

이제 CAMPANY 표와 CAMPANY1 표의 데이터는 다음과 같습니다：

w3codeboxdb=#　SELECT　*　FROM　COMPANY;
　id　|　name　　|　age　|　　address　　　|　salary
----+-------+-----+------------+--------
　　1　|　Paul　　|　　32　|　California　|　　20000
　　2　|　Allen　|　　25　|　Texas　　　　　　|　　15000
　　3　|　Teddy　|　　23　|　Norway　　　　　|　　20000
　　7　|　James　|　　24　|　Houston　　　　|　　10000
(4　rows)
w3codeboxdb=#　SELECT　*　FROM　COMPANY1;
　id　|　name　　|　age　|　address　|　salary
----+-------+-----+-------------+--------
　　4　|　Mark　　|　　25　|　Rich-Mond　　　|　　65000
　　5　|　David　|　　27　|　Texas　　　　　　　|　　85000
　　6　|　Kim　　　|　　22　|　South-Hall　　|　　45000
(3　rows)