7.8. 함께스포츠 토토(공통 테이블 표현)

함께작성 방법을 제공합니다 더 큰 규모의 하위 스포츠 토토선택질의. 흔히 Common이라고 불리는 하위 스포츠 토토 테이블 표현식 또는CTEs, 할 수 있어요 단지 목적으로만 존재하는 임시 테이블을 정의하는 것으로 생각하십시오. 이 쿼리. 이 기능의 용도 중 하나는 복잡한 문제를 분석하는 것입니다. 더 간단한 부분에 대한 쿼리. 예는 다음과 같습니다:

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에서 지역 선택)
지역, 제품별로 그룹화;

상위 판매량에만 제품별 판매량 합계를 표시합니다. 지역. 이 예는 없이 작성되었을 수 있습니다.함께, 하지만 두 가지 수준의 중첩이 필요했을 것입니다. 하위 SELECT. 이 방법을 따르면 조금 더 쉽습니다.

선택사항반복수식자 변경사항위드단순한 구문에서 그렇지 않은 일을 수행하는 기능으로의 편리함 표준 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쿼리는 일반적으로 다음과 같습니다. 상위 행을 억제하지 않고 명시된 대로 평가됩니다. 나중에 쿼리가 삭제될 수 있습니다. (하지만 위에서 언급했듯이 쿼리에 대한 참조가 있으면 평가가 일찍 중지될 수 있습니다. 제한된 수의 행만 요구합니다.)