Odpowiedź na pytanie.

Historia pytania.

Przed standardem SQL:2016 identyfikacja wzorców sekwencyjnych w uporządkowanych zbiorach danych wymagała złożonych self-joinów, logiki proceduralnej opartej na kursorach lub rekurencyjnych CTE symulujących maszyny stanów skończonych. Te podejścia cierpiały na eksplozję kombinacyjną, słabą wydajność oraz koszmary związane z utrzymaniem. Wprowadzenie klauzuli MATCH_RECOGNIZE zapewniło deklaratywną, matematycznie rygorystyczną składnię opartą na wyrażeniach regularnych do rozpoznawania wzorców wierszy, umożliwiając kompleksowe przetwarzanie zdarzeń bezpośrednio w silniku relacyjnym.

Problem.

Wykrycie specyficznych sekwencji o zmiennej długości — takich jak formacje cenowe w kształcie litery W — wymaga porównania każdego wiersza z wieloma poprzednikami i następcami przy zachowaniu kontekstowego stanu w całej sekwencji. Standardowe funkcje okna mogą odnosić się tylko do ustalonych przesunięć (np. LAG 1, LEAD 1), co czyni je niezdolnymi do obsługi wzorców, w których długości nóg się różnią. Rekurencyjne CTE teoretycznie mogą śledzić przejścia stanów, ale stają się kosztujące obliczeniowo i syntaktycznie rozwlekłe w przypadku obsługi wzorców wielostopniowych z restrykcyjnymi ograniczeniami porządkowymi.

Rozwiązanie.

MATCH_RECOGNIZE umożliwia definiowanie zmiennych wzorców za pomocą warunków boolowskich, specyfikację docelowego wzorca za pomocą składni wyrażeń regularnych (np. A B+ C+ D+ E+) oraz obliczanie miar agregacyjnych na dopasowanych wierszach. Obsługuje partycjonowanie, porządkowanie oraz funkcje nawigacyjne (PREV, NEXT, FIRST, LAST) natywnie.

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),  -- Musi spaść poniżej szczytu środkowego
    UP2   AS UP2.price > PREV(UP2.price)
) AS pattern_matches;

Sytuacja z życia

Kontekst.

Firma zajmująca się handlem ilościowym potrzebowała wykryć formacje W w danych forex w wysokiej częstotliwości (tick po ticku) w celu zautomatyzowania punktów wejściowych dla długich pozycji. Wzorzec wymagał dwóch odrębnych dolin oddzielonych szczytem, przy czym każda noga reprezentowała przynajmniej 0,5% ruchu cenowego.

Problem.

Zbiór danych zawierał 10 milionów wierszy dziennie w 50 parach walutowych. Wykrywanie oparte na Pythonie wprowadzało latencję sieciową oraz ograniczenia pamięci przy transferze gigabajtów danych co godzinę. Standardowe podejścia SQL wykorzystujące wiele self-joinów LAG()/LEAD() tworzyły iloczyny kartezjańskie przy próbie skorelowania czterech nóg wzoru W, powodując, że zapytania wygasały po 10 minutach.

Rozwiązanie 1: Procesowanie po stronie klienta w Pythonie.

Zespół początkowo użył pandas z niestandardową logiką pętli do wykrywania szczytów i dolin. Zalety: Bogate biblioteki analityczne, łatwe testy jednostkowe. Wady: Ogromny wąskie gardło transferu danych (godziny opóźnienia), wyczerpanie pamięci na serwerze aplikacyjnym podczas przetwarzania pełnej historii rynku i niemożność reakcji w czasie rzeczywistym.

Rozwiązanie 2: Rekurencyjna maszyna stanów CTE.

Próbowali śledzić pięć stanów (0=szukanie początku, 1=pierwszy spadek, 2= pierwszy wzrost, 3=drugi spadek, 4=drugi wzrost) za pomocą rekurencyjnego CTE. Zalety: Czysty SQL, logicznie rygorystyczny. Wady: Wykonanie jednowątkowe w silniku bazy danych, eksplozja spowolnienia przy głębokiej rekurencji oraz 300+ linii nieczytelnego SQL podatnego na błędy przepełnienia stosu w przypadku zmiennych sekwencji.

Rozwiązanie 3: Implementacja MATCH_RECOGNIZE.

Zespół zaimplementował zapytanie do dopasowywania wzorców SQL:2016 pokazane powyżej. Zalety: Optymalizacja natywnego silnika (wektorowe wykonanie), zwięzłe zapytanie o 25 liniach, które doskonale odzwierciedla definicję wzorca matematycznego, automatyczne obsługiwanie nóg o zmiennej długości za pomocą kwantyfikatorów (+) oraz efektywne pomijanie w celu zapobieżenia nadmiarowym pokrywającym się dopasowaniom. Wady: Wymaga migracji bazy danych do Oracle 19c (które obsługuje funkcje SQL:2016) oraz początkowego szkolenia dla programistów niezaznajomionych z składnią wyrażeń regularnych w SQL.

Wybrane rozwiązanie i wynik.

Rozwiązanie 3 zostało wybrane ze względu na swoją wydajność subsekundową w testach historycznych. Klauzula AFTER MATCH SKIP TO LAST UP2 upewniła się, że po zakończeniu wzoru W skanowanie wznowiło się na końcu wzoru, aby uniknąć nakładających się wykryć. System pomyślnie zidentyfikował 99,8% ręcznie walidowanych wzorów W, skracając latencję wykrywania z 45 minut (Python) do 800 milisekund, co umożliwiło handel algorytmiczny w czasie rzeczywistym.

Co często umyka kandydatom

Jak klauzula AFTER MATCH SKIP określa punkt wznowienia po dopasowaniu i dlaczego SKIP TO NEXT ROW versus SKIP PAST LAST ROW ma znaczenie dla pokrywających się wzorów?

AFTER MATCH SKIP określa, gdzie dopasowywacz wzorów kontynuuje skanowanie. SKIP PAST LAST ROW (domyślnie) wznowi się po ostatnim wierszu aktualnego dopasowania, zapobiegając udziałowi jakiegokolwiek wiersza w wielu dopasowaniach — odpowiednie dla wykrywania odrębnych zdarzeń. Przeciwnie, SKIP TO NEXT ROW wznawia się w wierszu natychmiastowym po startowym wierszu dopasowania, pozwalając na pokrywające się dopasowania. To jest kluczowe w finansowych szeregach czasowych, gdzie jedna dolina może legalnie tworzyć dno dwóch kolejnych wzorów W (pokrywające się okna). Kandydaci często wracają do standardowego pomijania, nieświadomi tego, że filtrują ważne sygnały nakładające się, co prowadzi do zmniejszenia wrażliwości wykrywania.

Jaka jest różnica między semantyką RUNNING a FINAL w klauzuli MEASURES i jak to wpływa na obliczenia agregatów w zmiennych długościach wzorów?

RUNNING ocenia wyrażenie w każdym kolejno następującym wierszu podczas budowania dopasowania (np. obliczanie średniej kroczącej podczas spadku). FINAL ocenia wyrażenie tylko raz w ostatnim wierszu pełnego dopasowania, używając wartości granicznych dla wszystkich zmiennych wzorców (np. obliczając całkowity procentowy wzrost od początku do końca wzoru). Kandydaci często pomijają słowo kluczowe FINAL podczas obliczania metryk obejmujących wzór, takich jak MAX(leg_price) - MIN(leg_price), co prowadzi do zwrócenia wartości pośrednich z niekompletnych dopasowań, co skutkuje niepoprawnymi obliczeniami sygnałów handlowych.

Jak radzisz sobie z pustymi dopasowaniami i zapewniasz, że niepasujące wiersze pojawiają się w wyniku do celów debugowania?

Domyślnie MATCH_RECOGNIZE filtruje wiersze, które nie uczestniczą w żadnym dopasowaniu. Aby uwzględnić niepasujące wiersze (co jest istotne dla audytowania, dlaczego niektóre sekwencje nie spełniły kryteriów wzorców), należy określić ALL ROWS PER MATCH w połączeniu z SHOW EMPTY MATCHES. W tym trybie każdy wiersz wejściowy generuje wynik, a miary wzorców zwracają NULL dla wierszy poza dopasowaniami. Dodatkowo MATCH_NUMBER() zwraca NULL dla niepasujących wierszy. Kandydaci często zmagają się z „brakującymi danymi” podczas debugowania, nie zdając sobie sprawy, że surowe warunki DEFINE filtrują ważne wiersze i nie wykorzystują SHOW EMPTY MATCHES, aby zdiagnozować, które konkretne warunki boolowskie (np. druga dolina nie będąca niższa od pierwszej) spowodowały odrzucenie wzoru.