[PostgreSQL] WITH 문의(Query)

WITH는, 보다 큰 SELECT 쿼리에 사용되는 서브쿼리를 작성하는 방법을 제공합니다. 서브 질의는 종종 공통 테이블 식 즉 CTE라고 합니다만, 그 질문에만 존재하는 임시 테이블을 정의하는 것입니다. 이 특성 중 1 개의 사용법은 복잡한 질문을 단순한 부품으로 분해하는 것입니다. 예입니다.

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_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product; 

이 예에서는 WITH 없이도 쓸 수 있지만, 두 계층의 중첩된 하위 SELECT를 필요로 합니다. 이 방법에 따르는 것이 다소 관리하기 쉬울 수 있습니다.

옵션 RECURSIVE 한정자는 WITH를, 단순히 구문상의 편리성을 높일뿐만 아니라 표준 SQL에서는 불가능한 기능을 제공합니다. 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 문의 일반 형식은, 항상 비재귀 표현 (non - recursive term), 그리고 UNION (또는 UNION ALL), 그리고 반복적인 표현에 한하여 그 질문 자신의 출력 참조를 포함할 수 있는 재귀 표현 (recursive term)입니다. 이러한 질문은 다음과 같이 실행됩니다.

 

 재귀 쿼리 평가

1. 비 재귀 표현을 평가합니다. UNION (그러나 UNION ALL이 아닌 경우)에 대해 중복 행을 삭제합니다. 그 재귀 쿼리의 결과 남아있는 모든 행을 포함하여, 동시에 그들을 임시 작업 테이블에 넣습니다.

2. 작업 테이블이 비어 있지 않은면 이 단계를 반복합니다.

   1. 재귀 자체 참조에 대한 작업 테이블의 실행 내용을 대체하고, 재귀 표현을 평가합니다. UNION (그러나 UNION ALL이 아닌)에 대해 중복 행 및 이전 결과 행과 중복 행을 삭제합니다. 그 재귀 쿼리의 결과가 남아있는 모든 행을 포함하여, 동시에 그들을 임시 중앙 테이블에 놓습니다.

   2. 중간 테이블 내용으로 작업 테이블의 내용을 교체하여, 중간 테이블을 비웁니다.

주의 : 엄밀하게 이 단계는 반복에 있어서 재귀는 아닙니다만, RECURSIVE은 SQL 표준위원회에서 선정된 용어입니다.

위의 예제에서 작업 테이블은 각각​​의 단계에서 단순히 한 행으로, 계속 작업하여 1에서 100 사이에서 값을 얻습니다. 100 번째 작업에서 WHERE절을 사용하여 문의가 종료됩니다.

재귀 문의는 계층 또는 트리 구조 데이터를 해결하기 위해 일반적으로 사용됩니다. 실제적인 예는, 직접 내포하는 테이블 1 개만 할당되며, 모든 제품의 직간접 부속을 찾아내는 문의입니다.

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_quantity
FROM included_parts
GROUP BY sub_part 

재귀 쿼리를 처리하는 경우, 쿼리의 재귀 부분이 어떠한 튜플을 반환하지 않던가, 쿼리가 무한 루프하지 않도록 주의합니다. 가끔은, UNION ALL 대신에 UNION을 사용하여 중복 마지막 출력 행을 삭제하여 이것을 수행할 수 있습니다. 그러나 어떤 주기는 종종 완전히 중복한 출력 행을 대상으로 하지 않습니다. 동일한 수행위치가 이미 도달했는지 여부를 보기 위해, 단 1 개의 또는 몇 개의 필드를 검사할 필요가 있을 지도 모릅니다. 이러한 상황을 처리 표준 기술은, 이미 순회한 값의 배열을 계산하는 것입니다. 예를 들어, 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 관계가 cycle을 포함하는 경우 반복합니다. "depth"출력이 필요하므로, UNION ALL을 UNION으로 변경하여 그룹화를 제거하지 않도록 합니다. 대신 특정 link의 path를 따라가는 동안 동일한 열에 도달했는지 여부를 알 필요가 있습니다. 루프나 쿼리에 path와 cycle 2 열을 추가합니다.

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          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; 

순회의 방지는 제외하고서라도, 배열 값은 "path"가 어떤 특정 행에 도달했는지를 표시하는 공정에서 자주 이용 가치가 있습니다.

어떤 cycle을 확인하기 위하여 검사하지 않으면 안되는 1 개 이상의 필드가 필요로 하는 일반적인 경우, 행 배열을 사용합니다. 예를 들어 f1과 f2의 필드를 비교할 필요가 있는 경우 다음과 같이합니다.

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          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; 

Tip : cycle을 확인하기 위해 검사할때 단지 1 개의 필드만 필요로 하는 일반적인 경우, ROW () 구문을 제거합니다. 이제 복합 형식 배열 대신 간단한 배열을 얻을 수있어 효율도 높아집니다.

Tip : 재귀적으로 쿼리 평가 알고리즘은, 횡형 검색 순서로 출력을 생성합니다. 이렇게 만들어진 "path"열을 외부 쿼리에서ORDER BY하여, 종형 검색 순서로 결과를 볼 수 있습니다.

루프를 돌지 확신이 없는 쿼리를 테스트하는 유익한 비결로 부모 질문에 LIMIT를 배치합니다. 예를 들어, 다음 질문(쿼리)은 LIMIT 없이는 영원히 반복합니다.

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

이것이 작동하는 것은 PostgreSQL의 구현이, 실제로 부모 문의로 추출하는 것과 같은 수의 WITH 질문 행만 평가하기 때문입니다. 이 방법을 실제 환경에서 사용하는 것은 권장되지 않습니다. 그 이유는 다른 시스템에서 다른 작동을 할 수 있기 때문입니다. 동시에 만약 외부 질문을 재귀적으로 쿼리의 결과를 정렬하거나, 또는 그들을 다른 테이블과 조인하는 것과 같이 작성을 하면 작동하지 않습니다.

유용한 WITH 문의의 특성은, 부모 문의 또는 형제 WITH 문의에 1 회 이상 방문하는 경우에도 부모 질문 실행에서 단 1 회만 평가되는 것입니다.따라서 여러 위치에 필요한 고가의 계산은, 중복 작업을 방지하기 위해 WITH 문의에 배치 할 수 있습니다. 그 밖에 있을 수 있는 응용 프로그램으로는, 원하지 않는 부작용이 있는 함수의 다중 평가를 피하는 것입니다. 그러나 최적화 능력은 부모 질문에서 제약을 일반 서브쿼리가 아니라, WITH 문의에 집어 넣기에는 부족한 면이 있습니다. WITH 문의는 일반적으로 부모 질문이 나중에 삭제할 행도 억제하지 않고 쓰여진대로 평가합니다. (그러나 위에서 언급했듯이, 질문에 대한 참조가 제한된 수의 행만 요청하면 평가는 조기에 중지합니다.)

펌 : http://kngt13.blog.me/80129274430

[출처] [PostgreSQL] 7.8. WITH 문의|작성자 카빙