패턴과 일치하는 행 시퀀스 식별하기

패턴과 일치하는 행 시퀀스 식별하기 (Identifying Sequences of Rows That Match a Pattern)

이 문서는 Snowflake의 MATCH_RECOGNIZE 기능을 사용해 행 시퀀스에서 특정 패턴과 일치하는 부분을 식별하는 방법을 설명해요.

출처: Identifying Sequences of Rows That Match a Pattern

본문

개요

MATCH_RECOGNIZE는 SQL:2016 표준에 정의된 기능으로, 행의 시퀀스에서 지정된 패턴을 검색하는 데 사용돼요. 패턴 매칭(Pattern matching)은 각 행에 부여된 클래스(class) 라벨의 시퀀스를 기반으로, 어떤 행들이 함께 특정 패턴을 구성하는지 식별해요.

이 기능은 이상 징후 탐지, 사기 감지, 로그 분석, 보안 침입 탐지 같이 시간 순서가 있는 데이터에서 의미 있는 행 그룹을 찾는 데 유용해요.

기본 개념

공식적으로, 우리는 "다음 순서로 된 행들이 특정 패턴을 형성합니다"라는 규칙을 정의합니다. 행들은 "매치"를 형성하도록 클래스가 지정됩니다.

간단한 예로, W(거짓말)와 Z(진실) 클래스가 있는 시퀀스에서 Z+ W+처럼 연속된 행 패턴을 찾을 수 있어요.

MATCH_RECOGNIZE는 다음 요소로 구성돼요:

PARTITION BY: 행을 그룹으로 나누는 컬럼.
ORDER BY: 각 파티션 내에서 행을 정렬하는 컬럼.
MEASURES: 매치에서 추출할 값 정의.
PATTERN: 매칭할 패턴 (클래스 시퀀스).
DEFINE: 각 클래스의 조건 정의.

구문

기본 MATCH_RECOGNIZE 구문:

SELECT ...
FROM mytable
MATCH_RECOGNIZE (
  PARTITION BY <partition_cols>
  ORDER BY <order_cols>
  MEASURES <measure_definitions>
  PATTERN (<pattern>)
  DEFINE <class_definitions>
);

간단한 예시

다음 예시는 코드 변경과 관련된 완료 이벤트 패턴을 식별하는 전형적인 사례예요.

먼저 테이블을 만들고 데이터를 로드해요:

CREATE OR REPLACE TABLE mytbl (
  ID NUMBER,
  WORKFLOW VARCHAR,
  ITERATION_NUMBER NUMBER,
  COMPLETED VARCHAR
);

INSERT INTO mytbl VALUES
  (1, 'P1', 1, 'N'),
  (2, 'P1', 2, 'Y'),
  (3, 'P1', 3, 'Y'),
  (4, 'P1', 4, 'Y'),
  ...
  ;

MATCH_RECOGNIZE를 사용해 완료된(COMPLETED='Y') 연속 이벤트를 식별해요:

SELECT *
FROM mytbl
MATCH_RECOGNIZE(
  PARTITION BY workflow
  ORDER BY id
  MEASURES
    MATCH_NUMBER() AS match_number,
    MATCH_ROWTIME() AS match_rowtime,
    FIRST(id) AS first_id,
    LAST(id) AS last_id
  ALL ROWS PER MATCH
  PATTERN (completed+)
  DEFINE
    completed AS completed = 'Y'
);

PARTITION BY workflow: 워크플로별로 행을 그룹화.
ORDER BY id: id 순서로 정렬.
PATTERN (completed+): 하나 이상의 연속된 완료 이벤트를 매치.
DEFINE completed AS completed = 'Y': completed 클래스는 COMPLETED='Y'인 행을 뜻함.

PATTERN 요소

패턴은 다음 요소로 구성될 수 있어요:

클래스 변수: DEFINE에서 정의된 행 클래스. 예: A, B, completed.
수량자:
- * : 0개 이상
- + : 1개 이상
- ? : 0개 또는 1개
- {m} : 정확히 m개
- {m,} : m개 이상
- {m,n} : m~n개
연결: 공백이나 쉼표로 클래스를 나열하며 순차 매칭. 예: A B C.
선택: |로 대안을 나열. 예: A | B.
괄호: ()로 하위 패턴을 그룹화.
앵커: ^(파티션 시작), $(파티션 끝).
EXCLUDE: 매치에서 행을 제외.

MEASURES

MEASURES 절은 매치에서 추출할 값을 정의해요. 유용한 함수:

MATCH_NUMBER(): 매치 번호.
MATCH_ROWTIME(): 행의 타임스탬프 (IF 클래스를 대표하는 행의).
FIRST(<col>) / LAST(<col>): 매치의 첫/마지막 행 값.
PREV(<col>): 이전 행 값.
NEXT(<col>): 다음 행 값.
CLASSIFIER(): 행이 속한 클래스.

ROW PER MATCH 옵션

ONE ROW PER MATCH: 각 매치에 대해 한 행을 출력 (기본 동작 방식 중 하나).
ALL ROWS PER MATCH: 매치에 포함된 모든 행을 출력.

DEFINE 클래스 조건

DEFINE 절에서 각 클래스의 조건을 정의해요. 조건은 일반 SQL 술어이며, 다른 클래스나 행 값, PREV/NEXT 함수를 참조할 수 있어요.

DEFINE
  A AS temperature > PREV(temperature),
  B AS temperature < PREV(temperature)

실전 예시: 연속 상승 패턴

주식 가격에서 연속 상승을 감지하는 예시:

SELECT *
FROM price_history
MATCH_RECOGNIZE(
  PARTITION BY symbol
  ORDER BY ts
  MEASURES
    FIRST(ts) AS start_ts,
    LAST(ts) AS end_ts,
    COUNT(*) AS num_rows
  ONE ROW PER MATCH
  PATTERN (up+)
  DEFINE
    up AS price > PREV(price)
);

이 쿼리는 가격이 이전 행보다 높은 연속 행 시퀀스를 식별해요.

고려 사항 및 제한

MATCH_RECOGNIZE는 FROM 절에서 다른 테이블·뷰·테이블 함수와 함께 사용할 수 있어요.
ORDER BY는 패턴 매칭의 순서를 결정하므로 중요해요.
패턴이 복잡해지면 성능에 영향을 줄 수 있어요.
모든 데이터 타입이 클래스 정의와 매칭에 사용될 수 있어요.
MATCH_RECOGNIZE는 Windows 함수처럼 파티션과 정렬을 사용해요.
SQL:2016 표준의 일부이며, 일부 구현 세부사항은 데이터베이스마다 달라요.

패턴과 일치하는 행 시퀀스 식별하기