7.8. 함께롤 토토(공통 테이블 표현)

7.8.1. 선택에WITH

기본값선택에WITH복잡한 것을 분해하는 것입니다 더 간단한 부분에 대한 쿼리. 예는 다음과 같습니다:

regional_sales AS 사용(
        SELECT 지역, SUM(금액) AS total_sales
        주문에서
        지역별 그룹
     ), top_regions AS(
        지역 선택
        Regional_sales에서
        WHERE total_sales  (SELECT SUM(total_sales)/10 FROM Regional_sales)
     )
지역 선택,
       제품,
       SUM(수량) AS 제품_단위,
       SUM(금액) AS 제품_판매
주문에서
WHERE 지역 IN(top_regions에서 지역 선택)
지역, 제품별로 그룹화;

제품별 판매 합계를 상단에만 표시합니다. 판매 지역. 그만큼함께절 정의 두 개의 보조 문이 명명됨regional_sales그리고top_regions, 여기서 출력은regional_sales다음에서 사용됨top_regions및 출력top_regions기본에서 사용됩니다선택질의. 이 예는 다음과 같을 수 있습니다. 없이 작성됨함께, 하지만 우리는 두 가지 수준의 중첩된 하위가 필요합니다.선택s. 이것을 따라하는 것이 조금 더 쉽습니다. 방법.

선택사항반복수식자 변경사항WITH단순한 구문에서 그렇지 않은 일을 수행하는 기능으로의 편리함 그렇지 않으면 표준 SQL에서 가능합니다. 사용반복, 아위드질의 자체 출력을 참조할 수 있습니다. 매우 간단한 예는 다음과 같습니다. 1부터 100까지의 정수를 합산하는 쿼리:

재귀적 t(n) AS 사용(
    가치 (1)
  유니온 올
    n < 100인 t WHERE에서 n+1 선택
)
SELECT 합계(n) FROM t;

재귀의 일반적인 형태WITH롤 토토는 항상 a비재귀 용어그러면유니온(또는유니온 올), 그런 다음재귀 용어, 여기서는 재귀 용어에는 쿼리 자체에 대한 참조가 포함될 수 있습니다. 산출. 이러한 쿼리는 다음과 같이 실행됩니다.

위의 예에서 작업 테이블에는 단 하나의 각 단계의 행은 1부터 100까지의 값을 갖습니다. 연속적인 단계로. 100번째 스텝에서는 출력이 없습니다. 때문에어디에서절 등 롤 토토가 종료됩니다.

재귀 쿼리는 일반적으로 다음을 처리하는 데 사용됩니다. 계층적 또는 트리 구조 데이터. 유용한 예는 다음과 같습니다 모든 직접 및 간접 하위 부분을 찾는 쿼리 제품, 즉시 표시되는 테이블만 제공됨 포함:

재귀적 include_parts(하위 부품, 부품, 수량) AS(
    SELECT sub_part, 부품, 수량 FROM 부품 WHERE 부품 = '우리_제품'
  유니온 올
    SELECT p.sub_part, p.part, p.수량
    FROM include_parts pr, 부품 p
    여기서 p.part = pr.sub_part
  )
SELECT sub_part, SUM(수량)을 total_수량으로 선택
포함된 부품에서
GROUP BY 하위_부분

재귀 쿼리로 작업할 때 다음 사항이 중요합니다. 쿼리의 재귀 부분이 결국에는 튜플을 반환하지 않으면 쿼리가 무기한 반복됩니다. 때로는 다음을 사용하여유니온대신유니온 올이 작업을 수행할 수 있는 방법은 다음과 같습니다. 이전 출력 행과 중복되는 행을 삭제합니다. 하지만, 종종 주기에는 완전히 출력 행이 포함되지 않습니다. 중복: 한 개 또는 몇 개만 확인해야 할 수도 있습니다. 이전에 동일한 지점에 도달했는지 확인하는 필드입니다. 그만큼 그러한 상황을 처리하는 표준 방법은 다음을 계산하는 것입니다. 이미 방문한 값의 배열입니다. 예를 들어, 테이블을 검색하는 다음 쿼리그래프사용링크필드:

재귀 검색_그래프(id, 링크, 데이터, 깊이) AS(
        SELECT g.id, g.link, g.data, 1
        FROM 그래프 g
      유니온 올
        SELECT g.id, g.link, g.data, sg.length + 1
        FROM 그래프 g, search_graph sg
        어디에서 g.id = sg.link
)
SELECT * 검색_그래프에서;

이 쿼리는 다음과 같은 경우 반복됩니다.링크관계에는 주기가 포함되어 있습니다. 왜냐하면 우리는"깊이"출력, 그냥 작고 보기 어리석은 동물유니온 올에유니온루핑을 제거하지 못할 것입니다. 대신에 같은 행에 다시 도달했는지 여부를 인식해야 합니다. 특정 링크 경로를 따라가는 동안. 두 개의 열을 추가합니다경로그리고주기루프가 발생하기 쉬운 롤 토토에 대해:

재귀 검색_그래프(id, 링크, 데이터, 깊이, 경로, 주기) AS(
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          거짓
        FROM 그래프 g
      유니온 올
        SELECT g.id, g.link, g.data, sg.length + 1,
          경로 || g.id,
          g.id = 모두(경로)
        FROM 그래프 g, search_graph sg
        여기서 g.id = sg.link AND NOT 순환
)
SELECT * 검색_그래프에서;

주기를 방지하는 것 외에도 배열 값은 종종 그 자체로 유용합니다."경로"특정 행에 도달하기 위해 사용됩니다.

둘 이상의 필드가 필요한 일반적인 경우 주기를 인식하려면 행 배열을 사용하십시오. 을 위한 예를 들어 필드를 비교해야 하는 경우f1그리고f2:

재귀 검색_그래프(id, 링크, 데이터, 깊이, 경로, 주기) AS(
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          거짓
        FROM 그래프 g
      유니온 올
        SELECT g.id, g.link, g.data, sg.length + 1,
          경로 || 행(g.f1, g.f2),
          ROW(g.f1, g.f2) = 모두(경로)
        FROM 그래프 g, search_graph sg
        여기서 g.id = sg.link AND NOT 순환
)
SELECT * 검색_그래프에서;

확실하지 않을 때 롤 토토를 테스트하는 데 유용한 트릭 루프가 발생할 수 있는 경우 a를 배치하는 것입니다.제한상위 쿼리에서. 예를 들어 이 쿼리는 영원히 반복됩니다. 없이제한:

재귀적 t(n) AS 사용(
    1개를 선택하세요
  유니온 올
    t에서 n+1 선택
)
t LIMIT 100에서 n을 선택하세요.

이것은 작동하는 이유는 다음과 같습니다.포스트그레SQL의 구현은 평가만 수행합니다. a의 행만큼WITH그대로 쿼리하세요 실제로 상위 쿼리에 의해 가져옵니다. 이 트릭을 사용하면 다른 시스템이 작동할 수 있으므로 프로덕션은 권장되지 않습니다. 다르게. 그리고 보통은 아우터로 만들면 잘 안되더라구요 쿼리 재귀 쿼리 결과를 정렬하거나 일부 결과에 결합 왜냐하면 그러한 경우 외부 쿼리는 일반적으로 모두 가져오려고 시도합니다.WITH롤 토토의 어쨌든 출력합니다.

유용한 속성WITH롤 토토 실행마다 한 번만 평가된다는 것입니다. 상위 롤 토토가 두 번 이상 참조된 경우에도 상위 롤 토토 또는 형제WITH롤 토토. 따라서 비용이 많이 드는 계산이 필요합니다. 여러 장소를 a 내에 배치할 수 있습니다.WITH중복된 작업을 피하기 위한 쿼리입니다. 또 다른 가능한 적용은 원치 않는 다중화를 방지하는 것입니다. 부작용이 있는 기능의 평가. 그러나 다른 이 동전의 측면은 최적화 프로그램이 푸시할 수 있는 능력이 적다는 것입니다. 상위 쿼리의 제한 사항을 a위드일반 하위 롤 토토보다 더 나은 롤 토토입니다. 그만큼함께쿼리는 일반적으로 평가됩니다 상위 쿼리 행을 억제하지 않고 작성된 대로 나중에 폐기할 수도 있습니다. (단, 위에서 언급한 바와 같이 평가는 쿼리에 대한 참조가 행 수가 제한되어 있습니다.)

위의 예는 단지 보여줍니다.함께함께 사용 중선택, 하지만 그럴 수도 있어요 같은 방식으로 첨부됨삽입, 업데이트, 또는삭제. 각각의 경우에 효과적으로 제공됩니다. 메인에서 참조할 수 있는 임시 테이블 명령.

7.8.2. 의 데이터 수정 문WITH

당신은 데이터 수정문을 사용할 수 있습니다(삽입, 업데이트또는삭제) in위드. 이를 통해 여러 가지 작업을 수행할 수 있습니다. 동일한 쿼리에서 다른 작업을 수행합니다. 예는 다음과 같습니다:

moved_rows AS(
    제품에서 삭제
    어디
        "날짜" = '2010-10-01' AND
        "날짜" < '2010-11-01'
    돌아오는 중 *
)
products_log에 삽입
SELECT * 이동된 행에서;

이 쿼리는 효과적으로 행을 다음에서 이동합니다.제품에제품_로그. 그만큼삭제inWITH삭제 의 지정된 행제품, 해당 내용을 반환합니다.돌아오는 중절; 그런 다음 기본 쿼리 해당 출력을 읽고 삽입합니다.제품_로그.

위 예의 좋은 점은 다음과 같습니다.함께절이 다음에 첨부되어 있습니다.삽입, 하위가 아님-선택내부삽입. 이는 데이터 수정이 필요하기 때문에 필요합니다. 명령문은에서만 허용됩니다.WITH최상위 명령문에 첨부된 절. 하지만, 정상위드가시성 규칙이 적용되므로 를 참조하는 것이 가능하다.WITH하위문의 출력선택.

데이터 수정 명령문WITH보통은돌아오는 중절(참조윈 토토 : 문서 : 9.2 : 수정 된 행에서 데이터를 반환), 다음과 같이 위의 예. 의 출력입니다.돌아오는 중절,아님대상 테이블 임시 테이블을 형성하는 데이터 수정 문 나머지 롤 토토에서 참조할 수 있습니다. 만약 데이터 수정 문함께부족 에이돌아오는 중절, 그러면 no를 형성합니다. 임시 테이블이므로 나머지 부분에서는 참조할 수 없습니다. 질문. 그럼에도 불구하고 그러한 명령문은 실행됩니다. 에이 특별히 유용하지 않은 예는 다음과 같습니다.

AS로 (
    foo에서 삭제
)
막대에서 삭제;

이 예는 테이블에서 모든 행을 제거합니다.foo그리고바. 그만큼 클라이언트에 보고된 영향을 받은 행의 수는 다음에서 제거된 행 포함바.

데이터 수정 문의 재귀적 자기 참조는 허용되지 않습니다. 어떤 경우에는 이 문제를 해결하는 것이 가능합니다. 재귀의 출력을 참조하여 제한 사항함께, 예:

재귀적 include_parts(하위_부분, 부분) AS(
    SELECT sub_part, 부품 FROM 부품 WHERE 부품 = 'our_product'
  유니온 올
    SELECT p.sub_part, p.part
    FROM include_parts pr, 부품 p
    여기서 p.part = pr.sub_part
  )
부품에서 삭제
  WHERE 부분 IN(included_parts에서 부분 선택);

이 쿼리는 다음의 모든 직접 및 간접 하위 부분을 제거합니다. 제품입니다.

데이터 수정 명령문위드정확히 한 번 실행되고 항상 완료됩니다. 기본 쿼리가 모두(또는 실제로) 읽는지 여부와 관계없이 any)의 출력. 이 내용은 본 내용과 다르다는 점에 유의하세요. 에 대한 규칙선택inWITH: 이전 섹션에서 설명한 대로, a의 실행선택만 운반됩니다 기본 롤 토토가 출력을 요구하는 한.

다음의 하위 진술함께있습니다 메인 쿼리와 동시에 실행됩니다. 따라서 다음에서 데이터 수정 문을 사용할 때WITH, 지정된 업데이트 순서 실제로 일어나는 일은 예측할 수 없습니다. 모든 진술은 동일하게 실행됨스냅샷(참조사설 토토 : 문서 : 9.2 : 동시성 제어), 그래서 그들은 할 수 없습니다"참조"서로가 대상 테이블. 이는 다음의 효과를 완화시킵니다. 행 업데이트의 실제 순서를 예측할 수 없음을 의미합니다. 저것돌아오는 중데이터는 다음을 수행할 수 있는 유일한 방법입니다. 서로 다른 변경 사항을 전달합니다.위드하위문과 메인쿼리. 안 이에 대한 예는 다음과 같습니다.

와 함께 (
    UPDATE 제품 SET 가격 = 가격 * 1.05
    돌아오는 중 *
)
SELECT * FROM 제품;

바깥쪽선택다음을 반환할 것입니다. 해당 조치 이전의 원래 가격업데이트, 안에 있는 동안

와 함께(
    UPDATE 제품 SET 가격 = 가격 * 1.05
    돌아오는 중 *
)
t에서 * 선택;

바깥쪽선택다음을 반환합니다. 데이터가 업데이트되었습니다.

단일 명령문에서 동일한 행을 두 번 업데이트하려고 시도하는 것은 지원되지 않습니다. 수정 중 하나만 발생하지만 신뢰성있게 예측하는 것은 쉽지 않으며 때로는 불가능합니다. 어느 것. 이는 이미 삭제된 행을 삭제하는 경우에도 적용됩니다. 동일한 명령문에서 업데이트됨: 업데이트만 수행됩니다. 그러므로 일반적으로 단일 항목을 수정하려고 시도하는 것을 피해야 합니다. 단일 문에서 두 번 행. 특히 글쓰기를 피하세요함께영향을 줄 수 있는 하위 설명 기본 명령문이나 형제에 의해 변경된 동일한 행 하위 진술. 그러한 진술의 효과는 다음과 같습니다. 예측 가능합니다.

현재 데이터 수정 대상으로 사용되는 모든 테이블은 진술서함께이 없어야 합니다. 조건부 규칙이나또한규칙, 도 아니고대신다음으로 확장되는 규칙 여러 진술.