질문에 대한 답변입니다.

질문의 역사.

SQL:2016 표준 이전에는 정렬된 데이터 세트 내에서 다중 행 순차적 패턴을 식별하기 위해 복잡한 자기 조인, 커서 기반 절차적 로직 또는 유한 상태 기계를 시뮬레이션하는 재귀 CTE가 필요했습니다. 이러한 접근 방식은 조합 폭발, 성능 저하 및 유지 보수 문제에 시달렸습니다. MATCH_RECOGNIZE 절의 도입은 행 패턴 인식을 위한 정규 표현식 기반의 선언적이고 수학적으로 엄격한 구문을 제공하여 관계형 엔진 내에서 복잡한 이벤트 처리를 가능하게 했습니다.

문제.

특정 가변 길이 시퀀스(예: W형 가격 형성)를 감지하려면 각 행을 여러 선행 및 후행 행과 비교하면서 전체 시퀀스에서 맥락 상태를 유지해야 합니다. 표준 창 함수는 고정 오프셋만 참조할 수 있으므로(예: LAG 1, LEAD 1), 다리 길이가 변동하는 패턴을 처리할 수 없습니다. 이론적으로 재귀 CTE는 상태 전환을 추적할 수 있지만 다단계 패턴을 처리할 때 계산 비용이 많이 들고 구문이 장황해집니다.

해결책.

MATCH_RECOGNIZE를 사용하면 불리언 조건을 사용하여 패턴 변수를 정의하고, 정규 표현식 구문을 통해 대상 패턴을 지정하며(예: A B+ C+ D+ E+), 일치한 행에 대한 집계 측정을 계산할 수 있습니다. 이는 파티셔닝, 정렬 및 탐색 함수(PREV, NEXT, FIRST, LAST)를 네이티브로 처리합니다.

SELECT *
FROM stock_ticks
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY tick_time
  MEASURES
    STRT.price AS start_price,
    FINAL LAST(DOWN1.price) AS first_trough,
    FINAL LAST(UP1.price) AS middle_peak,
    FINAL LAST(DOWN2.price) AS second_trough,
    FINAL LAST(UP2.price) AS end_price,
    MATCH_NUMBER() AS pattern_id
  ONE ROW PER MATCH
  AFTER MATCH SKIP TO LAST UP2
  PATTERN (STRT DOWN1+ UP1+ DOWN2+ UP2+)
  DEFINE
    DOWN1 AS DOWN1.price < PREV(DOWN1.price),
    UP1   AS UP1.price > PREV(UP1.price),
    DOWN2 AS DOWN2.price < PREV(DOWN2.price) 
        AND DOWN2.price < FIRST(UP1.price),  -- 중간 최고점 아래로 내려가야 함
    UP2   AS UP2.price > PREV(UP2.price)
) AS pattern_matches;

실제 사례

맥락.

정량적 거래 회사는 고빈도 외환 데이터(틱별)에서 W형 더블 바닥 패턴을 감지하여 롱 포지션의 진입점을 자동으로 설정해야 했습니다. 이 패턴은 두 개의 뚜렷한 골짜기 사이에 하나의 최고점이 필요하며, 각 다리는 최소 0.5%의 가격 변동을 나타냅니다.

문제.

데이터 세트는 50개 통화 쌍에서 매일 1000만 개의 행을 포함하고 있었습니다. Python 기반 탐지는 기가바이트의 데이터를 매시간 전송할 때 네트워크 지연 및 메모리 제약을 초래했습니다. 여러 LAG()/LEAD() 자기 조인을 사용하는 표준 SQL 접근 방식은 W 패턴의 네 다리를 연관시키려 할 때 데카르트 곱을 생성하여 쿼리가 10분 후에 시간 초과되는 문제를 초래했습니다.

해결책 1: 클라이언트 측 Python 처리.

팀은 처음에 맞춤형 루프 로직을 사용하여 피크와 골짜기를 감지하기 위해 pandas를 사용했습니다. 장점: 풍부한 분석 라이브러리, 손쉬운 단위 테스트. 단점: 막대한 데이터 전송 병목 현상(시간의 지연), 전체 시장 이력을 처리할 때 애플리케이션 서버에서 메모리 소진 및 실시간 반응 불가.

해결책 2: 재귀 CTE 상태 기계.

그들은 다섯 개의 상태(0=시작 찾기, 1=첫 번째 하락, 2=첫 번째 상승, 3=두 번째 하락, 4=두 번째 상승)를 추적하는 재귀 CTE를 시도했습니다. 장점: 순수 SQL, 논리적으로 엄격함. 단점: 데이터베이스 엔진에서 단일 스레드 실행, 깊은 재귀에 따른 기하급수적 둔화 및 300줄 이상의 이해할 수 없는 SQL이 유동적인 시퀀스에서 스택 오버플로 오류가 발생하기 쉬움.

해결책 3: MATCH_RECOGNIZE 구현.

팀은 위에서 설명한 SQL:2016 패턴 일치 쿼리를 구현했습니다. 장점: 네이티브 엔진 최적화(벡터화된 실행), 수학적 패턴 정의를 정확하게 반영한 간결한 25줄 쿼리, 수량자(+)를 통해 가변 길이 다리를 자동으로 처리하며 중복 겹침을 방지하기 위한 효율적인 건너뛰기. 단점: SQL:2016 기능을 지원하는 Oracle 19c로 데이터베이스 마이그레이션이 필요하고 SQL에서 정규 표현식 구문에 익숙하지 않은 개발자 교육이 필요했습니다.

선택된 해결책 및 결과.

해결책 3은 역사적 백테스트에서 초당 성능 덕분에 선택되었습니다. AFTER MATCH SKIP TO LAST UP2 절은 W 패턴이 완료된 후 스캔이 패턴의 끝에서 재개되도록 보장하여 중복 감지를 피했습니다. 시스템은 수동으로 검증된 W 패턴의 99.8%를 성공적으로 식별하여 탐지 대기 시간을 Python의 45분에서 800밀리초로 단축시켜 실시간 알고리즘 거래를 가능하게 했습니다.

후보자들이 자주 놓치는 점

MATCH 후 SKIP 절은 일치 후 재개 지점을 어떻게 결정하고, 다음 행을 SKIP 대 LAST ROW로 건너뛰는 것이 겹치는 패턴에서 중요한 이유는 무엇인가요?

AFTER MATCH SKIP은 패턴 매처가 검색을 계속하는 위치를 결정합니다. SKIP PAST LAST ROW(기본값)는 현재 일치의 마지막 행 다음에서 재개하여 아무 행도 여러 일치에 참여하지 못하도록 하여 독립적인 이벤트 감지에 적합합니다. 반대로, SKIP TO NEXT ROW는 일치의 시작 행 다음 행에서 재개되어 겹치는 일치를 허용합니다. 이는 금융 시계열에서 단일 골짜기가 두 개의 연속 W 패턴의 바닥을 형성할 수 있는 경우에 중요합니다(겹치는 창). 후보자들은 종종 표준 건너뛰기에 기본 설정되어 유효한 겹치는 신호를 잘못 필터링하고 탐지 민감도를 낮추게 됩니다.

MEASURES 절에서 RUNNING과 FINAL 의미의 차별점은 무엇이며, 이것이 가변 길이 패턴 내에서 집계 계산에 어떤 영향을 미칩니까?

RUNNING은 일치가 구성되는 각 성공적인 행에서 표현식을 평가합니다(예: 하락 다리 동안 이동 평균을 계산). FINAL은 전체 일치의 마지막 행에서만 표현식을 평가하여 모든 패턴 변수를 위한 최종 경계 값을 사용합니다(예: 패턴 시작부터 끝까지의 총 백분율 변화를 계산). 후보자들은 종종 패턴 전체 메트릭을 계산할 때 FINAL 키워드를 생략하므로, 불완전한 매치에서 중간 값이 반환되어 잘못된 거래 신호 계산을 초래합니다.

비어 있는 매치를 어떻게 처리하며, 디버깅 목적으로 일치하지 않는 행이 출력에 나타나도록 보장합니까?

기본적으로 MATCH_RECOGNIZE는 어떤 매치에도 참여하지 않는 행을 필터링합니다. 일치하지 않는 행을 포함하려면(특정 시퀀스가 패턴 기준을 충족하지 못한 이유를 감사하는 데 필수적입니다), ALL ROWS PER MATCH를 지정하고 SHOW EMPTY MATCHES와 결합해야 합니다. 이 모드에서는 모든 입력 행이 출력으로 생성되며, 패턴 메트릭은 일치 외의 행에 대해 NULL을 반환합니다. 또한, MATCH_NUMBER()는 일치하지 않는 행에 대해 NULL을 반환합니다. 후보자들은 종종 "누락된 데이터" 디버깅에 어려움을 겪으며, 엄격한 DEFINE 조건이 유효한 행을 필터링했다는 사실을 인식하지 못하고, SHOW EMPTY MATCHES를 활용하여 특정 불리언 조건(예: 두 번째 골짜기가 첫 번째보다 낮지 않음)이 패턴 거부를 초래했음을 진단하지 못합니다.