7.8. with롤 토토 (공통 표 표현)

7.8.1. selectinwith

기본 값selectinwith복잡한 내려야합니다 더 간단한 부품으로 롤 토토합니다. 예는 다음과 같습니다.

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절의 정의 두 개의 보조 진술이 지명Regional_salesandTOP_REGIONSRegional_salesTOP_REGIONS그리고의 출력TOP_REGIONS기본에서 사용됩니다select롤 토토. 이 예는 그럴 수 있습니다 없이 작성with그러나 우리는 가지고 있습니다 중첩 하위 두 가지 수준이 필요했습니다.selects. 이것을 따르기가 조금 더 쉽습니다 방법.

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

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

재귀의 일반적인 형태with롤 토토는 항상입니다.비 수거 용어,Union(또는Union All), 그런 다음 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;

이 롤 토토는이면 루프됩니다링크관계에는주기가 포함됩니다. 왜냐하면 우리는 a가 필요합니다."깊이"출력, 그냥 작고 보기 어리석은 동물Union AlltoUnion루핑을 제거하지 않습니다. 대신에 우리는 다시 같은 행에 도달했는지 여부를 인식해야합니다. 링크의 특정 경로를 따르는 동안. 두 개의 열을 추가합니다Pathand사이클루프가 발생하기 쉬운 롤 토토 :

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쿼리 그들은 실행 당 한 번만 평가됩니다. 부모 쿼리, 두 번 이상 참조하더라도 부모 쿼리 또는 형제 자매with롤 토토. 따라서 필요한 계산이 필요합니다 여러 장소를 내에 배치 할 수 있습니다.with중복 작업을 피하기위한 롤 토토. 또 다른 가능한 적용은 원치 않는 배수를 방지하는 것입니다 부작용을 가진 함수 평가. 그러나 다른 것 이 동전의 측면 부모 롤 토토에서 a 제한with일반 하위 롤 토토보다 롤 토토. 그만큼with롤 토토는 일반적으로 평가됩니다 작성대로, 부모가 롤 토토하는 행을 억제하지 않고 나중에 버릴 수 있습니다. (그러나 위에서 언급했듯이 평가 롤 토토에 대한 참조가 제한된 행 수.)

위의 예제는 전적으로 표시withselect이지만 가능합니다 같은 방식으로 첨부삽입, 업데이트또는삭제. 각각의 경우 효과적으로 제공합니다 메인에서 언급 할 수있는 임시 테이블 명령.

7.8.2. 의 데이터 수정 진술with

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

maze_rows as (
    제품에서 삭제하십시오
    어디
        "날짜" = '2010-10-01'및
        "날짜"< '2010-11-01'
    반환 *
))
Products_Log에 삽입하십시오
maze_rows에서 *를 선택하십시오;

이 롤 토토는 효과적으로 행을 움직입니다제품toProducts_Log. 그만큼삭제inwith삭제 지정된 행에서제품, 내용을 통해 내용을 반환반환절; 그리고 기본 쿼리 출력을 읽고 삽입합니다Products_Log.

위의 예의 미세점은입니다.with절에 첨부되어삽입, 서브select내에서삽입. 데이터 수정이기 때문에 필요합니다 진술은에서만 허용됩니다.with최상위 성명서에 첨부 된 조항. 하지만, 정상with가시성 규칙이 적용됩니다 를 언급 할 수 있습니다.withsub-의 명령문 출력select.

데이터 수정 문의with보통반환조항 (참조윈 토토 : 문서 : 9.2 : 수정 된 행에서 데이터를 반환) 위의 예. 그것은의 출력입니다.반환절,not대상 테이블 데이터 수정 진술은 임시 표를 형성합니다 나머지 롤 토토에서 참조 할 수 있습니다. 경우 a 의 데이터 수정 진술with부족 에이반환조항은 다음을 형성합니다 임시 테이블이며 나머지 부분에서는 참조 할 수 없습니다. 질문. 그럼에도 불구하고 그러한 진술은 실행됩니다. 에이 불가능한 예는 다음과 같습니다.

t as (
    foo에서 삭제하십시오
))
바에서 삭제;

이 예제는 테이블에서 모든 행을 제거합니다fooandbar. 그만큼 고객에게보고 된 영향을받는 행의 수는 에서 제거 된 행 포함bar.

데이터 수정 진술의 재귀 적 자기 참조는 다음과 같습니다 허용되지 않습니다. 어떤 경우에는 이것을 해결할 수 있습니다. 재귀의 출력을 참조하여 제한with, 예 :

재귀 포함 포함 _parts (sub_part, part) as (
    sub_part, part = 'our_product'부분에서 부품을 선택하십시오.
  모두
    p.sub_part, p.part를 선택하십시오
    포함 _parts pr, parts p
    여기서 p.part = pr.sub_part
  ))
부품에서 삭제하십시오
  PART (conture_Parts에서 부품을 선택);

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

데이터 수정 문의with정확히 한 번에 실행되며 항상 완료됩니다. 기본 쿼리가 모든 것을 읽는 지 (또는 실제로 그들의 출력의 모든. 이것은 다른 것과 다릅니다 규칙selectinwith: 이전 섹션에서 언급 한 바와 같이, A 실행select는 전달됩니다 기본 롤 토토가 출력을 요구하는 한.

하위 진술의withare 서로 동시에 그리고 메인 롤 토토와 함께 실행되었습니다. 따라서 데이터 수정 문을 사용할 때with, 지정된 업데이트 순서 실제로 발생하는 것은 예측할 수 없습니다. 모든 진술은 다음과 같습니다 동일하게 실행스냅 샷(참조사설 토토 : 문서 : 9.2 : 동시성 제어), 그렇지 않아"참조"서로의 영향에 미치는 영향 대상 테이블. 이것은 효과를 완화시킵니다 실제 행 업데이트 순서의 예측 불가능 성 및 수단 저것반환데이터는 유일한 방법입니다 다른 사이의 변화를 전달with하위 진술 및 기본 롤 토토. an 이것의 예는

t as (
    업데이트 제품 세트 가격 = 가격 * 1.05
    반환 *
))
선택 *에서 제품에서;

외부select행동 전의 원래 가격업데이트

t as (
    업데이트 제품 세트 가격 = 가격 * 1.05
    반환 *
))
선택 *에서 t; 선택

외부select업데이트 된 데이터.

단일 문서에서 같은 행을 두 번 업데이트하려고합니다. 지원되지 않습니다. 수정 중 하나만 발생하지만 확실하게 예측하는 것은 쉽지 않고 때로는 불가능합니다. 어느 쪽. 이것은 이미 한 행을 삭제하는데도 적용됩니다. 동일한 문서에서 업데이트 : 업데이트 만 수행됩니다. 따라서 일반적으로 단일 수정을 피해야합니다. 단일 진술로 두 번 행하십시오. 특히 글쓰기를 피하십시오with영향을 줄 수있는 하위 진술 메인 진술 또는 형제에 의해 동일한 행이 변경되었습니다. 하위 진술. 그러한 진술의 효과는 그렇지 않을 것입니다 예측 가능.

현재, 데이터 수정의 대상으로 사용되는 모든 테이블 진술witha 조건부 규칙, 또는또한규칙, 또한대신확장되는 규칙 다중 진술.