윈도우 구조의 행 패턴 인식

윈도우 구조의 행 패턴 인식 (Row pattern recognition in window structures)

윈도우 구조 안에서 행 패턴 인식(row pattern recognition)을 정의하는 방법을 설명하는 문서예요. MATCH_RECOGNIZE와 유사하지만 윈도우 프레임 안에서 동작해요.

출처: 문서

본문

윈도우 구조는 윈도우 연산의 WINDOW 절이나 OVER 절에서 정의할 수 있어요. 두 경우 모두 윈도우 사양에 행 패턴 인식 절을 포함할 수 있어요. 그것들은 윈도우 프레임의 일부예요. 윈도우에서의 행 패턴 인식 문법과 의미는 MATCH_RECOGNIZE 절의 것과 유사해요.

이 섹션은 윈도우 구조에서의 행 패턴 인식 세부 사항을 설명하고, 두 패턴 인식 메커니즘 사이의 유사점과 차이점을 강조해요.

행 패턴 인식이 있는 윈도우

윈도우 사양:

(
[ existing_window_name ]
[ PARTITION BY column [, ...] ]
[ ORDER BY column [, ...] ]
[ window_frame ]
)

윈도우 프레임:

[ MEASURES measure_definition [, ...] ]
frame_extent
[ AFTER MATCH skip_to ]
[ INITIAL | SEEK ]
[ PATTERN ( row_pattern ) ]
[ SUBSET subset_definition [, ...] ]
[ DEFINE variable_definition [, ...] ]

일반적으로 윈도우 프레임은 윈도우 함수가 처리할 행의 "슬라이딩 윈도우"를 정의하는 frame_extent를 지정해요. ROWS, RANGE, GROUPS로 정의할 수 있어요.

행 패턴 인식이 있는 윈도우 프레임은 필수 또는 선택적인 다양한 문법 구성 요소를 포함하며, frame_extent에 특정 제한을 가해요.

행 패턴 인식이 있는 윈도우 프레임:

[ MEASURES measure_definition [, ...] ]
ROWS BETWEEN CURRENT ROW AND frame_end
[ AFTER MATCH skip_to ]
[ INITIAL | SEEK ]
PATTERN ( row_pattern )
[ SUBSET subset_definition [, ...] ]
DEFINE variable_definition [, ...]

패턴 인식 절 설명

행 패턴 인식이 있는 frame_extent는 ROWS로 정의되어야 해요. 프레임 시작은 CURRENT ROW여야 하며, 허용되는 프레임 범위 값은 다음과 같이 제한돼요:

ROWS BETWEEN CURRENT ROW AND CURRENT ROW

ROWS BETWEEN CURRENT ROW AND <expression> FOLLOWING

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

윈도우가 처리하는 각 입력 행에 대해, frame_extent가 감싸는 행 부분은 행 패턴 인식의 검색 영역을 제한해요. MATCH_RECOGNIZE에서는 패턴 검색이 파티션 끝까지 모든 행을 탐색할 수 있고 파티션의 모든 행이 계산에 사용 가능하지만, 윈도우 구조에서는 패턴 일치가 프레임 밖의 행을 일치시키거나 입력 값을 검색할 수 없어요.

frame_extent 외에도 패턴 일치는 PATTERN과 DEFINE 절을 요구해요.

PATTERN 절은 일부 문법 확장이 있는 정규식 형태의 행 패턴을 지정해요. 행 패턴 문법은 MATCH_RECOGNIZE의 행 패턴 문법과 유사해요. 다만 앵커 패턴 ^과 $는 윈도우 사양에서 허용되지 않아요.

DEFINE 절은 충족되어야 하는 boolean 조건으로 행 패턴 기본 변수를 정의해요. MATCH_RECOGNIZE의 DEFINE 절과 유사해요. 유일한 차이는 윈도우 문법이 MATCH_NUMBER 함수를 지원하지 않는다는 점이에요.

MEASURES 절은 문법적으로 MATCH_RECOGNIZE의 MEASURES 절과 유사해요. 유일한 제한은 MATCH_NUMBER 함수가 허용되지 않는다는 점이에요. 다만 이 절의 의미는 MATCH_RECOGNIZE와 윈도우 간에 달라요. MATCH_RECOGNIZE에서는 모든 measure가 출력 컬럼을 만들지만, 윈도우의 measure는 윈도우 구조와 연관된 정의로 간주돼요. 일반 윈도우 함수처럼 윈도우에 대해 호출할 수 있어요:

SELECT cust_key, value OVER w, label OVER w
    FROM orders
    WINDOW w AS (
                 PARTITION BY cust_key
                 ORDER BY order_date
                 MEASURES
                        RUNNING LAST(total_price) AS value,
                        CLASSIFIER() AS label
                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                 PATTERN (A B+ C+)
                 DEFINE
                        B AS B.value < PREV (B.value),
                        C AS C.value > PREV (C.value)
                )

윈도우에 정의된 measure는 둘러싸는 쿼리의 SELECT 절과 ORDER BY 절에서 참조할 수 있어요.

RUNNING과 FINAL 키워드는 MEASURES 절에서 허용돼요. 논리적 내비게이션 함수 FIRST나 LAST 또는 집계 함수 앞에 올 수 있어요. 다만 효과는 없어요. 모든 계산은 일치의 마지막 행 위치에서 수행되므로, 의미는 사실상 FINAL이에요.

AFTER MATCH SKIP 절은 MATCH_RECOGNIZE의 AFTER MATCH SKIP 절과 같은 문법을 가져요.

INITIAL 또는 SEEK 수정자는 윈도우의 행 패턴 인식에 특화돼요. 기본값인 INITIAL에서는 입력 행의 패턴 일치가 그 행에서 시작해서만 찾을 수 있어요. SEEK에서는 현재 행에서 시작하는 일치가 없으면, 엔진이 프레임 안의 이후 행에서 시작하는 일치를 찾으려 시도해요. 그 결과 입력 행을 그 행과 분리된 일치와 연관시키는 것이 가능해요.

SUBSET 절은 기본 패턴 변수들의 집합으로 union 변수를 정의하는 데 사용돼요. union 변수를 사용해 부분 집합의 어떤 기본 패턴 변수와도 일치한 행 집합을 참조할 수 있어요:

SUBSET U = (A, B)

다음 표현식은 A나 B 중 하나와 일치한 마지막 행에서 total_price 값을 반환해요:

LAST(U.total_price)

일치의 모든 행을 참조하려면 모든 패턴 변수를 포함하는 SUBSET를 정의할 필요가 없어요. 접두사 없는 컬럼 이름과 인자 없는 CLASSIFIER 호출에 적용되는 암시적 보편 패턴 변수(universal pattern variable) 가 있어요. 다음 표현식은 마지막으로 일치한 행에서 total_price 값을 반환해요:

LAST(total_price)

다음 호출은 첫 번째로 일치한 행의 기본 패턴 변수를 반환해요:

FIRST(CLASSIFIER())

윈도우에서는 MATCH_RECOGNIZE와 달리 ONE ROW PER MATCH나 ALL ROWS PER MATCH를 지정할 수 없어요. 일반 윈도우 함수든 measure든 윈도우에 대한 모든 호출이 윈도우 의미를 준수해야 하기 때문이에요. 윈도우에 대한 호출은 입력 행마다 정확히 하나의 출력 행을 만들어야 한다고 가정돼요. 그래서 윈도우의 패턴 인식 출력 모드는 ONE ROW PER MATCH와 WITH UNMATCHED ROWS의 조합이에요.

행 패턴 인식으로 입력 처리

윈도우의 패턴 인식은 두 가지 다른 경우로 입력 행을 처리해요:

  • 윈도우에 대한 행 패턴 measure 호출 시: some_measure OVER w
  • 윈도우에 대한 윈도우 함수 호출 시: sum(total_price) OVER w

각 입력 행에 대해 생성되는 출력 행은 다음으로 구성돼요:

  • 입력 행의 모든 값
  • 행과 연관된 패턴 일치에 대해 계산된 호출된 measure 또는 윈도우 함수의 값

입력 처리는 다음 단계 순서로 설명할 수 있어요:

  • PARTITION BY에 따라 입력 데이터를 분할
  • ORDER BY 표현식으로 각 파티션 정렬
  • 정렬된 파티션의 각 행에 대해:
    • 행이 이전 행의 일치로 '건너뛰어졌다면'(skipped):
      • measure의 경우: 일치하지 않은 행으로 단일 행 출력 생성
      • 윈도우 함수의 경우: 빈 프레임에 대해 함수를 평가하고 단일 행 출력 생성
    • 그렇지 않으면:
      • 프레임 범위를 결정
      • 프레임 범위 안의 현재 행에서 시작해 행 패턴을 일치시키려 시도
      • 일치를 찾지 못하고 SEEK가 지정됐다면, 프레임 범위 안의 이후 행에서 시작하는 일치를 찾으려 시도
      • 일치를 찾지 못하면:
        • measure의 경우: 일치하지 않은 행으로 단일 행 출력 생성
        • 윈도우 함수의 경우: 빈 프레임에 대해 함수를 평가하고 단일 행 출력 생성
      • 그렇지 않으면:
        • measure의 경우: 일치에 대해 단일 행 출력 생성
        • 윈도우 함수의 경우: 일치한 행 시퀀스로 제한된 프레임에 대해 함수를 평가하고 단일 행 출력 생성
      • AFTER MATCH SKIP 절을 평가하고 '건너뛰어진' 행을 표시

빈 일치와 일치하지 않는 행

특정 입력 행과 연관될 수 있는 일치가 없으면 그 행은 unmatched(일치하지 않음)예요. 행에 대해 일치를 찾을 수 없을 때 이런 일이 생겨요. 행에 대해 일치가 시도되지 않을 때도 생기는데, 이전 행의 AFTER MATCH SKIP 절에 의해 건너뛰어졌기 때문이에요. 일치하지 않는 행에 대해 모든 행 패턴 measure는 null이에요. 모든 윈도우 함수는 빈 프레임에 대해 평가돼요.

빈 일치(empty match) 는 어떤 패턴 변수도 포함하지 않는 성공적인 일치예요. 다시 말해 빈 일치는 어떤 행도 포함하지 않아요. 빈 일치가 입력 행과 연관되면, 그 행의 모든 행 패턴 measure는 빈 행 시퀀스에 대해 평가돼요. 모든 내비게이션 연산과 CLASSIFIER 함수는 null을 반환해요. 모든 윈도우 함수는 빈 프레임에 대해 평가돼요.

대부분의 경우 빈 일치와 일치하지 않는 행의 결과는 같아요. 상수 measure가 두 경우를 구분하는 데 도움이 될 수 있어요:

다음 호출은 빈 일치를 포함한 모든 일치 행에 대해 'matched'를, 모든 일치하지 않는 행에 대해 null을 반환해요:

matched OVER (
              ...
              MEASURES 'matched' AS matched
              ...
             )

더 알아보기 (Learn more)

일반적 행 패턴 인식은 MATCH_RECOGNIZE 문서에서, 윈도우 함수의 기본 구조는 SELECT 문서를 참고해요.