7.8. with스포츠 토토 (공통 표 표현)

with작성 방법을 제공합니다 더 큰 곳에서 사용하기위한 하위 스포츠 토토select스포츠 토토. 하위 스포츠 토토는 종종 공통이라고합니다 테이블 표현 또는CTEs, can 단지 존재하는 임시 테이블을 정의하는 것으로 생각하십시오. 이 쿼리. 이 기능을 한 번 사용하는 것은 복잡한 것입니다. 더 간단한 부품으로 쿼리합니다. 예는 다음과 같습니다.

greal_sales as (
        Total_sales로 영역, 합 (금액)을 선택하십시오
        주문에서
        지역별 그룹
     ), top_regions as (
        지역을 선택하십시오
        regional_sales에서
        여기서 total_sales (select sum (total_sales)/regional_sales에서 10)
     ))
지역 선택,
       제품,
       sum (수량)으로 product_units,
       sum (금액)은 product_sales입니다
주문에서
지역 (Top_regions에서 지역 선택)
지역별 그룹, 제품;

상위 판매만으로 제품 당 총 판매량을 표시하는 지역. 이 예는없이 쓰여졌을 수 있습니다.with그러나 우리는 두 가지 레벨의 중첩이 필요했을 것입니다 하위 선택. 이런 식으로 따르는 것이 조금 더 쉽습니다.

선택 사항재귀수정 자 변화with단순한 구문에서 그렇지 않은 일을 달성하는 기능으로 편의성 표준 SQL에서 가능합니다. 사용재귀, awith스포츠 토토 자체 출력을 참조 할 수 있습니다. 매우 간단한 예는이 스포츠 토토입니다 정수를 1 ~ 100에서 요약하려면 :

재귀 t (n) as (
    값 (1)
  모두
    n <100에서 n+1을 선택하십시오
))
t;에서 합 (n)을 선택하십시오.

재귀의 일반적인 형태with스포츠 토토는 항상입니다.비 수거 용어, 그 다음에Union(또는연합 모두), A재귀 용어, 여기 재귀 용어 만 쿼리의 참조를 포함 할 수 있습니다. 자신의 출력. 이러한 쿼리는 다음과 같이 실행됩니다.

위의 예에서 작업 테이블에는 단일 행이 있습니다. 각 단계에서, 그것은 1에서 100의 값을 취합니다. 연속적인 단계. 100 단계에는 출력이 없습니다 의여기서조항 및 스포츠 토토 종료.

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

재귀 포함 포함 _parts (sub_part, part, rotity) as (
    sub_part, part, part에서 part = 'our_product'를 선택하십시오.
  모두
    p.sub_part, p.part, p.quantity를 선택하십시오
    포함 _parts pr, parts p
    여기서 p.part = pr.sub_part
  ))
sub_part, sum (수량)을 total_quantity로 선택하십시오
conlude_parts에서
sub_part의 그룹

재귀 쿼리로 작업 할 때는 확실합니다. 쿼리의 재귀 부분이 결국 반환 될 것입니다. 튜플, 그렇지 않으면 쿼리가 무기한으로 루프됩니다. 때때로, 사용Union대신Union All행을 폐기하여이를 달성 할 수 있습니다 이전 출력 행을 복제합니다. 그러나 종종주기가 발생합니다 완전히 복제 된 출력 행을 포함하지 않습니다. 동일한 지 확인하려면 하나 또는 몇 개의 필드 만 확인해야합니다. 포인트는 전에 도달했습니다. 취급을위한 표준 방법 이러한 상황은 이미 방문한 배열을 계산하는 것입니다. 값. 예를 들어, 검색하는 다음 쿼리를 고려하십시오 테이블그래프a 사용링크필드 :

recursive search_graph (id, link, data, depth) as (
        G.ID, G.Link, G.Data, 1을 선택하십시오
        그래프 g에서
      모두
        G.ID, G.Link, G.Data, Sg.Depth + 1을 선택하십시오
        그래프 G, search_graph sg에서
        여기서 g.id = sg.link
))
sexect *에서 search_graph;

이 스포츠 토토는이면 루프됩니다.링크관계에는주기가 포함됩니다. 우리가 필요하기 때문에"깊이"출력, 바로 변경Union AlltoUnion루핑을 제거하지 마십시오. 대신 우리는 여부를 인식해야합니다 우리는 특정한 것을 따르는 동안 다시 같은 행에 도달했습니다. 링크의 경로. 두 개의 열을 추가합니다PATHand사이클TO 루프가 발생하기 쉬운 스포츠 토토 :

recursive search_graph (id, 링크, 데이터, 깊이, 경로, 사이클)
        G.ID, G.Link, G.Data, 1, 1,
          배열 [g.id],
          거짓
        그래프 g에서
      모두
        G.ID, G.Link, G.Data, Sg.Depth + 1,
          경로 || g.id,
          g.id = 모든 (경로)
        그래프 G, search_graph sg에서
        여기서 g.id = sg.link 및 순환이 아닙니다
))
sexect *에서 search_graph;

사이클을 방지하는 것 외에도 배열 값은 종종 유용합니다. 그 자체로"Path"특정 행에 도달하기 위해 촬영.

일반적인 경우 둘 이상의 필드가 필요한 경우 사이클을 인식하기 위해 점검하고 행을 사용하십시오. 예를 들어, 필드를 비교해야한다면F1andF2:

recursive search_graph (id, 링크, 데이터, 깊이, 경로, 사이클)
        G.ID, G.Link, G.Data, 1, 1,
          배열 [행 (G.F1, G.F2)],
          거짓
        그래프 g에서
      모두
        G.ID, G.Link, G.Data, Sg.Depth + 1,
          경로 || 행 (G.F1, G.F2),
          행 (G.F1, G.F2) = 모든 (경로)
        그래프 G, search_graph sg에서
        여기서 g.id = sg.link 및 순환이 아닙니다
))
sexect *에서 search_graph;

확실하지 않은 경우 스포츠 토토 테스트에 유용한 트릭 그들이 루프가 a를 배치하려면Limit부모 스포츠 토토에서. 예를 들어,이 스포츠 토토는 영원히 루프됩니다 없이Limit:

재귀 t (n) as (
    선택 1
  모두
    t에서 n+1을 선택하십시오
))
t 한계 100에서 n을 선택하십시오;

이것은 작동하기 때문에PostgreSQL의 구현은 만 평가됩니다 많은 행의 awith실제로 부모 쿼리에 의해 가져 왔습니다. 이 트릭을 사용합니다 다른 시스템이 작동 할 수 있으므로 생산은 권장되지 않습니다 다르게. 또한 외부를 만들면 일반적으로 작동하지 않습니다. 쿼리는 재귀 쿼리의 결과를 정렬하거나 일부에 가입합니다. 기타 테이블.

유용한 속성with스포츠 토토입니다 부모의 실행 당 한 번만 평가됩니다. 부모가 두 번 이상 참조하더라도 스포츠 토토 스포츠 토토 또는 형제with스포츠 토토. 따라서, 여러 곳에서 필요한 값 비싼 계산은 A 내에 배치with피하기위한 스포츠 토토 중복 작업. 또 다른 가능한 응용 프로그램은 방지하는 것입니다 부작용을 가진 함수의 원치 않는 다중 평가. 그러나이 동전의 다른 쪽은 최적화가 부모 스포츠 토토에서 제한을 a로 푸시 할 수 없습니다.with일반 하위 스포츠 토토보다 스포츠 토토. 그만큼with일반적으로 스포츠 토토가 될 것입니다 언급 된대로 평가, 부모가 줄을 억제하지 않고 스포츠 토토는 나중에 버릴 수 있습니다. (그러나 위에서 언급했듯이 스포츠 토토에 대한 참조가 있으면 평가가 일찍 중지 될 수 있습니다. 제한된 수의 행만 요구합니다.)