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사설 토토는 자체 출력을 참조 할 수 있습니다. 에이 매우 간단한 예는이 사설 토토입니다. 100을 통해 :

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

재귀의 일반적인 형태with사설 토토는 항상입니다.비 수거 용어,Union(또는연합 모두), a재귀 용어, 어디에 재귀 용어 만 쿼리 자체에 대한 참조를 포함 할 수 있습니다. 산출. 이러한 쿼리는 다음과 같이 실행됩니다.

위의 예에서 작업 테이블에는 단일 행이 있습니다. 각 단계, 1 ~ 100의 값을 취합니다. 연속적인 단계. 100 번째 단계에서는 그만큼여기서조항 및 사설 토토 종료.

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

14671_15020

재귀 쿼리로 작업 할 때는 확실합니다. 쿼리의 재귀 부분이 결국 반환 될 것입니다. 튜플, 그렇지 않으면 쿼리가 무기한으로 루프됩니다. 때로는 사용Union대신연합 모두복제 행을 폐기하여이를 달성 할 수 있습니다 이전 출력 행. 그러나 종종주기는 포함되지 않습니다 완전히 중복 된 출력 행 : 필요할 수 있습니다. 동일한 지점이 있는지 확인하려면 하나 또는 몇 개의 필드 만 확인하십시오. 전에 도달했습니다. 그러한 상황을 처리하는 표준 방법은 다음과 같습니다 이미 방문한 값의 배열을 계산합니다. 예를 들어, 테이블을 검색하는 다음 쿼리를 고려하십시오그래프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"특정 행에 도달하기 위해 촬영.

일반적인 경우 둘 이상의 필드가 필요한 경우 사이클을 인식하기 위해 점검하고 행을 사용하십시오. 예를 들어, if 우리는 필드를 비교해야했습니다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를 배치하는 것입니다.Limitin 부모 쿼리. 예를 들어,이 쿼리는없이 영원히 루프됩니다 그만큼Limit:

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

이것은 작동하기 때문에PostgreSQL's 구현은 A의 많은 행만 평가합니다.with실제로 부모가 가져온 쿼리 질문. 이 트릭을 생산에 사용하는 것은 권장되지 않습니다 다른 시스템은 다르게 작동 할 수 있습니다. 또한 일반적으로 작동하지 않습니다 외부 쿼리를 재귀 쿼리의 결과를 정렬하거나 그러한 경우 외부에서는 다른 테이블에 가입하십시오. 쿼리는 일반적으로 모든 것을 가져 오려고합니다.with어쨌든 사설 토토의 출력.

유용한 속성with쿼리입니다 부모의 실행 당 한 번만 평가됩니다. 부모가 두 번 이상 참조하더라도 쿼리 쿼리 또는 형제with사설 토토. 따라서, 여러 곳에서 필요한 값 비싼 계산은 A 내에 배치with피하기위한 사설 토토 중복 작업. 또 다른 가능한 응용 프로그램은 원치 않는 것을 방지하는 것입니다 부작용과 함께 함수의 다중 평가. 그러나, 이 동전의 다른 쪽 부모 사설 토토에서 a 제한with일반 하위 사설 토토보다 사설 토토. 그만큼with사설 토토는 일반적으로 다음과 같이 평가됩니다 상위 사설 토토가 할 수있는 행을 억제하지 않고 나중에 버립니다. (그러나 위에서 언급했듯이 평가가 중단 될 수 있습니다 사설 토토에 대한 참조 (들)가 제한된 숫자 만 요구하는 경우 일찍 줄의

위의 예제 만 표시with존재 와 함께select그러나 첨부 할 수 있습니다 같은 방식으로삽입, 업데이트또는삭제. 각각 경우 효과적으로 임시 테이블을 제공 할 수 있습니다 메인 명령에서 언급 됨.

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보통반환조항 (참조스포츠 토토 사이트 : 문서 : 9.3 : 수정 된 행에서 데이터를 반환) 위의 예. 그것은의 출력입니다.반환절,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정확히 한 번에 실행되고 항상 독립적으로 완성됩니다 기본 쿼리가 모든 것을 읽는지 (또는 실제로는) 산출. 이것은 규칙과 다릅니다selectinwith: 언급 된대로 이전 섹션에서 a 실행select는 기본 사설 토토까지만 운반됩니다 출력을 요구합니다.

하위 진술의with실행됩니다 동시에 서로와 메인 사설 토토와 함께. 그러므로, 데이터 수정 문을 사용할 때with, 지정된 업데이트 순서 실제로 발생하는 것은 예측할 수 없습니다. 모든 진술이 실행됩니다 똑같이스냅 샷(참조13 장), 그렇지 않아"대상 테이블에 대한 서로의 영향. 이것은 실제의 예측 불가능 성의 영향을 완화합니다. 행 업데이트 순서,반환데이터는 의사 소통하는 유일한 방법입니다 다른 변화with하위 진술 및 기본 사설 토토. 이것의 예는 그 것입니다 안에

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

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

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

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

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

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