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;

이 젠 토토는이면 루프됩니다.링크관계에는주기가 포함됩니다. 우리가 필요하기 때문에"깊이"출력, 그냥 변경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의 구현은 많은 행의 행만 평가합니다with쿼리는 실제로 상위 쿼리에 의해 가져옵니다. 다른 시스템이 다르게 작동 할 수 있으므로이 트릭을 생산에 사용하는 것은 권장되지 않습니다. 또한 외부 쿼리가 재귀 쿼리의 결과를 정렬하거나 다른 테이블에 결합하면 일반적으로 작동하지 않습니다.이 경우 외부 쿼리는 일반적으로 모든를 가져 오려고합니다.with어쨌든 젠 토토의 출력.

유용한 속성with쿼리는 부모 쿼리 또는 형제 자매가 한 번 이상 참조하더라도 부모 쿼리의 실행 당 한 번만 평가된다는 것입니다with젠 토토. 따라서 여러 장소에서 필요한 비싼 계산은 A 내에 배치 할 수 있습니다.with중복 작업을 피하기위한 젠 토토. 또 다른 가능한 응용 프로그램은 부작용과 함께 함수의 원치 않는 다중 평가를 방지하는 것입니다. 그러나이 코인의 다른 쪽은 Optimizer가 상위 젠 토토의 제한을 A로 푸시 할 수 없다는 것입니다.with일반 하위 젠 토토보다 젠 토토. 그만큼with젠 토토는 일반적으로 상위 젠 토토가 나중에 버릴 수있는 행을 억제하지 않고 서면으로 평가됩니다. (그러나 위에서 언급했듯이 젠 토토에 대한 참조가 제한된 수의 행만 요구하면 평가가 일찍 중지 될 수 있습니다.)

위의 예제 만 표시withselect, 그러나 같은 방식으로 첨부 할 수 있습니다삽입, 업데이트또는삭제. 각각의 경우에는 메인 명령에서 참조 할 수있는 임시 테이블을 효과적으로 제공합니다.

7.8.2. 의 데이터 수정 진술with

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

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

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

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

데이터 수정 문의with보통반환조항 (참조PostgreSQL : 문서 : 9.4 : 수정 된 행에서 윈 토토를 반환22746_22806반환절,not데이터 수정 문의 대상 테이블은 나머지 쿼리에서 참조 할 수있는 임시 테이블을 형성합니다. 데이터 수정 명세서에서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는 1 차 쿼리가 출력의 모든 (또는 실제로)를 읽는지 여부와 독립적으로 한 번 정확하게 한 번 실행되며 항상 완료됩니다. 이것은 규칙과 다릅니다selectinwith24588_24642select기본 젠 토토가 출력을 요구하는 한만 운반됩니다.

하위 진술의with는 서로 동시에 그리고 기본 젠 토토와 함께 실행됩니다. 따라서 데이터 수정 문을 사용할 때with, 지정된 업데이트가 실제로 발생하는 순서는 예측할 수 없습니다. 모든 진술은 동일하게 실행됩니다스냅 샷(참조사설 토토 사이트 : 문서 : 9.4 : 사설 토토 사이트 제어), 따라서"참조"대상 테이블에 대한 서로의 영향. 이것은 실제 행 업데이트의 실제 순서의 예측 불가능 성의 영향을 완화하며반환데이터는 다른간에 변경 사항을 전달하는 유일한 방법입니다with하위 진술 및 기본 젠 토토. 이것의 예는에서입니다.

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

외부select의 행동 전에 원래 가격을 반환합니다업데이트

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

외부select업데이트 된 데이터를 반환합니다.

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

현재, 데이터 수정 명령문의 대상으로 사용 된 모든 테이블with조건부 규칙도 없어야합니다.또한규칙, 또는 AN대신여러 진술로 확장하는 규칙.