문서:13:7.8. 쿼리(공통 테이블 식)

WITH에서SELECT의 기본 값은 복잡한 쿼리를 더 간단한 부분으로 나누는 것입니다. 예는 다음과 같습니다:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

상위 판매 지역에서만 제품별 판매 합계를 표시합니다. WITH절은regional_sales와top_regions이라는 두 개의 보조 문을 정의하며,regional_sales의 출력은top_regions에서 사용되고top_regions의 출력은 기본SELECT쿼리에서 사용됩니다. 이 예제는WITH없이 작성되었을 수 있지만 중첩 된 하위SELECT의 두 가지 수준이 필요했습니다.

선택적RECURSIVE수정자 변경WITH단순한 구문 편의에서 하지 않는 것 들을 수행 하는 기능으로 표준 데이터베이스. RECURSIVE를 사용하면WITH쿼리가 자체 출력을 참조할 수 있습니다. 이 쿼리는 1 에서 100 까지의 정수를 합산하는 매우 간단한 예입니다:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

재귀WITH쿼리의 일반적인 형태는 항상 비 재귀 용어,UNION(또는UNION ALL),재귀 용어 만 쿼리 자체 출력에 대한 참조를 포함 할 수있는 재귀 용어입니다. 이러한 쿼리는 다음과 같이 실행됩니다:

위의 예에서 작업 테이블은 각 단계에서 단일 행만 가지며 연속 단계에서 1 에서 100 사이의 값을 취합니다. 100 번째 단계에서는WHERE절 때문에 출력이 없으므로 쿼리가 종료됩니다.

재귀 쿼리는 일반적으로 계층 구조 또는 트리 구조 데이터를 처리하는 데 사용됩니다. 이 쿼리는 제품의 모든 직접 및 간접 하위 부분을 찾는 데 유용한 예로서 즉각적인 포함을 보여주는 테이블 만 제공합니다:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

재귀 쿼리를 사용할 때는 쿼리의 재귀 부분이 결국 튜플을 반환하지 않거나 쿼리가 무한정 반복되는지 확인해야 합니다. 때로는UNION ALL대신UNION를 사용하면 이전 출력 행과 중복되는 행을 삭제하여이를 수행 할 수 있습니다. 그러나 순환에는 완전히 중복된 출력 행이 포함되지 않는 경우가 많습니다. 이러한 상황을 처리하는 표준 방법은 이미 방문한 값의 배열을 계산하는 것입니다. 예를 들어link필드를 사용하여graph테이블을 검색하는 다음 쿼리를 생각해 보십시오:

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;

이 쿼리는link관계에 사이클이 포함된 경우 반복됩니다. “깊이”출력이 필요하기 때문에UNION ALL를UNION로 변경해도 루핑이 제거되지 않습니다. 대신 우리는 링크의 특정 경로를 따르는 동안 동일한 행에 다시 도달했는지 여부를 인식해야합니다. 루프가 발생하기 쉬운 쿼리에path와cycle의 두 열을 추가합니다:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

사이클을 방지하는 것 외에도 배열 값은 특정 행에 도달하기 위해 취한”경로”를 나타내는 것처럼 자체적으로 유용합니다.

주기를 인식하기 위해 둘 이상의 필드를 검사해야 하는 일반적인 경우에는 행 배열을 사용합니다. 예를 들어,f1및f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

쿼리를 반복할 수 있는지 확실하지 않은 경우 쿼리를 테스트하는 데 유용한 트릭은 부모 쿼리에LIMIT을 배치하는 것입니다. 예를 들어,이 쿼리는 루프없이 영원히LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

실제로 부모 쿼리에서 가져온 만큼WITH쿼리의 행만 계산하기 때문입니다. 다른 시스템이 다르게 작동 할 수 있기 때문에 생산에서이 트릭을 사용하는 것은 권장되지 않습니다. 또한 외부 쿼리가 재귀 쿼리의 결과를 정렬하거나 다른 테이블에 조인하는 경우 외부 쿼리가 일반적으로WITH쿼리 출력을 모두 가져 오려고 시도하기 때문에 일반적으로 작동하지 않습니다.

WITH쿼리의 유용한 속성은 일반적으로 부모 쿼리 또는 형제WITH쿼리에서 두 번 이상 참조되는 경우에도 부모 쿼리 실행 당 한 번만 평가된다는 것입니다. 따라서 중복 작업을 피하기 위해 여러 위치에서 필요한 값 비싼 계산을WITH쿼리 내에 배치 할 수 있습니다. 또 다른 가능한 응용 프로그램은 부작용 함수의 원치 않는 여러 평가를 방지하는 것입니다. 그러나 이 동전의 다른 측면은 최적화 프로그램이 상위 쿼리의 제한을 곱하기 참조WITH쿼리로 밀어 넣을 수 없다는 것입니다. 곱하기 참조WITH쿼리는 부모 쿼리가 나중에 버릴 수 있는 행을 표시하지 않고 작성된 것으로 평가됩니다. (그러나 위에서 언급 한 바와 같이 쿼리에 대한 참조가 제한된 수의 행만 요구하는 경우 평가가 일찍 중지 될 수 있습니다.2359>

그러나WITH쿼리가 비 재귀적이고 부작용이 없는 경우(즉,휘발성 함수를 포함하지 않는SELECT)부모 쿼리로 접혀 두 쿼리 수준의 공동 최적화를 허용 할 수 있습니다. 기본적으로 부모 쿼리가WITH쿼리를 한 번만 참조하지만WITH쿼리를 두 번 이상 참조하는 경우에는 이 문제가 발생합니다. MATERIALIZED을 지정하여WITH쿼리를 강제로 별도 계산하거나NOT MATERIALIZED를 지정하여 부모 쿼리에 강제로 병합하여 이 결정을 재정의할 수 있습니다. 후자의 선택은WITH쿼리의 중복 계산을 위험하지만WITH쿼리의 각 사용에WITH쿼리의 전체 출력 중 작은 부분 만 필요한 경우 여전히 순 절감 효과를 줄 수 있습니다.

이러한 규칙의 간단한 예는

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

이WITH쿼리는 접혀서

SELECT * FROM big_table WHERE key = 123;

특히key에 인덱스가 있으면key = 123를 갖는 행만 가져 오는 데 사용됩니다. 반면에

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

에서는WITH쿼리가 구체화되어big_table의 임시 복사본을 생성한 다음 인덱스의 혜택 없이 자체적으로 조인됩니다. 이 쿼리는

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

로 작성된 경우 훨씬 효율적으로 실행되므로 상위 쿼리의 제한이big_table의 검사에 직접 적용될 수 있습니다.

NOT MATERIALIZED가 바람직하지 않을 수 있는 예는

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

여기서WITH쿼리의 구체화는very_expensive_function가 두 번이 아니라 테이블 행당 한 번만 평가되도록 합니다.

위의 예제는SELECT와 함께 사용되는WITH만 보여 주지만INSERT,UPDATE또는DELETE와 같은 방식으로 첨부 할 수 있습니다. 각각의 경우에 주 명령에서 참조 할 수있는 임시 테이블을 효과적으로 제공합니다.