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

7.8.1. 선택inWITH

기본값선택inWITH복잡한 쿼리를 다음과 같이 분류하는 것입니다. 간단한 부품. 예는 다음과 같습니다:

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. 조금이다 이 방법을 따르는 것이 더 쉽습니다.

선택사항반복수정자 변경함께단순한 구문상의 편리함으로 인해 다른 방법으로는 불가능했던 일을 수행하는 기능으로 표준 SQL. 사용반복, a위드사설 토토는 자체 출력을 참조할 수 있습니다. 에이 매우 간단한 예는 1의 정수를 합산하는 사설 토토입니다. 100까지:

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

재귀의 일반적인 형태위드사설 토토는 항상 a비재귀 용어그러면유니온(또는유니온 모두), 그런 다음 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 하위_부분

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

재귀 검색_그래프(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 * 검색_그래프에서;

이 쿼리는 다음과 같은 경우 반복됩니다.링크관계에는 주기가 포함되어 있습니다. 왜냐하면 우리는 a를 요구하기 때문입니다."깊이"출력, 변경 중유니온 올에유니온그러겠다 루핑을 제거하지 마십시오. 대신 우리는 우리가 특정 경로를 따라가는 동안 다시 같은 행에 도달했습니다. 링크. 두 개의 열을 추가합니다경로그리고주기루프가 발생하기 쉬운 사설 토토에 대해:

재귀 검색_그래프(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어쨌든 사설 토토의 출력입니다.

유용한 속성위드사설 토토는 상위 실행마다 한 번만 평가됩니다. 사설 토토(부모가 두 번 이상 참조한 경우에도) 사설 토토 또는 형제위드사설 토토. 따라서, 여러 장소에서 필요한 값비싼 계산은 a 내에 배치됨WITH피할 쿼리 중복 작업. 또 다른 가능한 응용 프로그램은 원하지 않는 것을 방지하는 것입니다. 부작용이 있는 기능에 대한 다중 평가. 그러나 이 동전의 다른 측면은 최적화 프로그램이 푸시할 수 있는 능력이 적다는 것입니다. 상위 쿼리의 제한 사항을 aWITH사설 토토가 일반적인 하위 사설 토토보다 좋습니다.위드쿼리는 일반적으로 다음과 같이 평가됩니다. 상위 쿼리가 수행할 수 있는 행을 억제하지 않고 작성됨 나중에 폐기하십시오. (단, 위에서 언급했듯이 평가가 중단될 수 있습니다. 쿼리에 대한 참조가 제한된 수만 요구하는 경우 조기에 개 행.)

위의 예는 단지 보여줍니다.WITH존재 와 함께 사용됨선택, 하지만 부착 가능 같은 방법으로삽입, 업데이트또는삭제. 각각 경우에 따라 효과적으로 임시 테이블을 제공할 수 있습니다. 기본 명령에서 참조됩니다.

7.8.2. 의 데이터 수정 문WITH

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

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

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

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

데이터 수정 명령문위드보통은돌아오는 중절(참조스포츠 토토 사이트 : 문서 : 9.3 : 수정 된 행에서 데이터를 반환), 다음과 같이 위의 예. 의 출력입니다.돌아오는 중절,아님대상 테이블 데이터 수정 문은 임시 테이블을 형성합니다. 사설 토토의 나머지 부분에서 참조됩니다. 데이터를 수정하는 경우 진술서위드a가 부족함돌아오는 중절, 그러면 임시 테이블이 형성되지 않습니다. 나머지 쿼리에서는 참조할 수 없습니다. 그러한 그럼에도 불구하고 명령문은 실행됩니다. 특별히 유용하지 않은 예는 다음과 같습니다:

WITH 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에서 부분 선택);

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

데이터 수정 명령문WITH있습니다 정확히 한 번 실행되고, 항상 완료될 때까지 독립적으로 실행됩니다. 기본 쿼리가 해당 쿼리의 전부(또는 실제로 일부)를 읽는지 여부 출력. 이는 다음의 규칙과 다릅니다.선택에WITH: 명시된 대로 이전 섹션에서 a의 실행선택기본 사설 토토까지만 전달됩니다. 출력을 요구합니다.

다음의 하위 진술함께실행됩니다 서로 동시에 그리고 기본 쿼리와 동시에. 그러므로, 데이터 수정 문을 사용할 때WITH, 지정된 업데이트 순서 실제로 일어나는 일은 예측할 수 없습니다. 모든 명령문이 실행됩니다. 같은스냅샷(참조13장), 그래서 그들은 할 수 없습니다"참조"서로 목표 테이블에 미치는 영향. 이는 실제 상황의 예측 불가능성으로 인한 영향을 완화합니다. 행 업데이트 순서는 다음을 의미합니다.돌아오는 중데이터는 의사소통을 위한 유일한 방법입니다 서로 다른 변화함께하위문과 메인쿼리. 이에 대한 예는 다음과 같습니다. 에서

AS로 (
    UPDATE 제품 SET 가격 = 가격 * 1.05
    돌아오는 중 *
)
SELECT * FROM 제품;

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

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

외부선택다음을 반환합니다. 데이터가 업데이트되었습니다.

단일 명령문에서 동일한 행을 두 번 업데이트하려고 시도하는 것은 아닙니다. 지원됩니다. 수정 사항 중 하나만 발생하지만 그렇지 않습니다. 어떤 것을 확실하게 예측하기는 쉽습니다(때로는 불가능합니다). 이는 이미 업데이트된 행을 삭제하는 경우에도 적용됩니다. 동일한 명령문: 업데이트만 수행됩니다. 그러므로 당신은 일반적으로 단일 행에서 단일 행을 두 번 수정하려고 시도하지 마십시오. 성명. 특히 글쓰기를 피하세요함께동일한 행에 영향을 미칠 수 있는 하위 명령문 주요 진술이나 형제 하위 진술에 의해 변경되었습니다. 는 그러한 진술의 효과는 예측할 수 없습니다.

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