Antwort auf die Frage.
Historie der Frage.
Vor dem SQL:2016 Standard erforderte die Identifizierung von mehrzeiligen sequenziellen Mustern innerhalb geordneter Datensätze umständliche Selbstverknüpfungen, cursorbasierte prozedurale Logik oder rekursive CTEs, die endliche Zustandsautomaten simulierten. Diese Ansätze litten unter kombinatorischer Explosion, schlechter Leistung und Wartungsproblemen. Die Einführung der MATCH_RECOGNIZE Klausel bot eine deklarative, mathematisch rigorose Syntax basierend auf regulären Ausdrücken zur Mustererkennung von Zeilen und ermöglichte die Verarbeitung komplexer Ereignisse direkt innerhalb der relationalen Engine.
Das Problem.
Die Erkennung spezifischer variablenlanger Sequenzen – wie W-förmige Preisformationen – erfordert den Vergleich jeder Zeile mit mehreren Vorgängern und Nachfolgern, während der kontextuelle Zustand über die gesamte Sequenz hinweg aufrechterhalten wird. Standardfensterfunktionen können nur feste Offsets referenzieren (z. B. LAG 1, LEAD 1), wodurch sie nicht in der Lage sind, Muster zu verarbeiten, bei denen die Dauer der Beine variiert. Rekursive CTEs können theoretisch Zustandsübergänge nachverfolgen, werden jedoch rechnerisch teuer und syntaktisch langwierig, wenn sie mehrstufige Muster mit strengen Ordnungsanforderungen behandeln.
Die Lösung.
MATCH_RECOGNIZE ermöglicht die Definition von Mustervariablen mithilfe von booleschen Bedingungen, die Spezifizierung des Zielmusters über reguläre Ausdruckssyntax (z. B. A B+ C+ D+ E+) und die Berechnung aggregierter Maße über die gefundenen Zeilen hinweg. Es behandelt Partitionierung, Ordnung und Navigationsfunktionen (PREV, NEXT, FIRST, LAST) nativ.
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), -- Muss unter dem mittleren Gipfel liegen UP2 AS UP2.price > PREV(UP2.price) ) AS pattern_matches;
Lebenssituation
Kontext.
Eine quantitative Handelsfirma musste W-förmige Doppelbodenmuster in hochfrequenten Forex-Daten (Tick-by-Tick) erkennen, um Einstiegspunkte für Long-Positionen zu automatisieren. Das Muster erforderte zwei separate Täler, die durch einen Gipfel getrennt waren, wobei jedes Bein mindestens eine Preisbewegung von 0,5 % darstellen musste.
Das Problem.
Der Datensatz enthielt täglich 10 Millionen Zeilen über 50 Währungspaare. Die auf Python basierende Erkennung führte zu Netzwerkverzögerungen und Speichereinschränkungen beim stündlichen Übertragen von Gigabyte an Daten. Standard-SQL-Ansätze, die mehrere LAG()/LEAD() Selbstverknüpfungen verwendeten, erzeugten kartesische Produkte, wenn versucht wurde, die vier Beine des W-Musters zu korrelieren, was dazu führte, dass Abfragen nach 10 Minuten zeitüberschritten wurden.
Lösung 1: Client-seitige Verarbeitung mit Python.
Das Team verwendete zunächst Pandas mit benutzerdefinierter Schleifenlogik, um Gipfel und Täler zu erkennen. Vorteile: Umfangreiche analytische Bibliotheken, einfache Unit-Tests. Nachteile: Massiver Übertragungsengpass von Daten (Stunden der Verzögerung), Speicherauslastung auf dem Anwendungsserver bei der Verarbeitung der vollständigen Markthistorie und Unfähigkeit, in Echtzeit zu reagieren.
Lösung 2: Rekursive CTE-Zustandsmaschine.
Sie versuchten eine rekursive CTE, die fünf Zustände verfolgte (0=Suche Anfang, 1=erste Abnahme, 2=erster Anstieg, 3=zweite Abnahme, 4=zweiter Anstieg). Vorteile: Reines SQL, logisch rigoros. Nachteile: Einzelprozessausführung in der Datenbank-Engine, exponentielle Verlangsamung bei tiefer Rekursion und 300+ Zeilen unverständliches SQL, das anfällig für Stacküberlauf-Fehler bei volatilen Sequenzen ist.
Lösung 3: MATCH_RECOGNIZE-Implementierung.
Das Team implementierte die oben gezeigte SQL:2016-Mustererkennungsabfrage. Vorteile: Native Engine-Optimierung (vektorisierte Ausführung), knappe 25-Zeilen-Abfrage, die die mathematische Musterdefinition genau widerspiegelte, automatische Verarbeitung variablenlanger Beine über Quantifizierer (+) und effizientes Überspringen, um redundante überlappende Übereinstimmungen zu vermeiden. Nachteile: erforderte eine Datenbankmigration zu Oracle 19c (das SQL:2016-Funktionen unterstützt) und eine anfängliche Schulung für Entwickler, die mit regulärer Ausdruckssyntax in SQL nicht vertraut waren.
Ausgewählte Lösung und Ergebnis.
Lösung 3 wurde aufgrund ihrer Sub-Sekunden-Leistung bei historischen Rücktests ausgewählt. Die Klausel AFTER MATCH SKIP TO LAST UP2 stellte sicher, dass nach Abschluss eines W-Musters der Scan am Ende des Musters fortgesetzt wurde, um überlappende Erkennungen zu vermeiden. Das System identifizierte erfolgreich 99,8% der manuell validierten W-Muster und reduzierte die Erkennungsverzögerung von 45 Minuten (Python) auf 800 Millisekunden, was den algorithmischen Handel in Echtzeit ermöglichte.
Was Bewerber oft übersehen
Wie bestimmt die AFTER MATCH SKIP-Klausel den Fortsetzungszeitpunkt nach einer Übereinstimmung und warum ist SKIP TO NEXT ROW im Vergleich zu SKIP PAST LAST ROW wichtig für überlappende Muster?
AFTER MATCH SKIP diktiert, wo der Mustererkenner mit dem Scannen fortfährt. SKIP PAST LAST ROW (der Standard) setzt nach der letzten Zeile der aktuellen Übereinstimmung fort und verhindert, dass eine Zeile an mehreren Übereinstimmungen teilnimmt – geeignet für die Erkennung unterschiedlicher Ereignisse. Im Gegensatz dazu setzt SKIP TO NEXT ROW an der Zeile direkt nach der Startzeile der Übereinstimmung fort, was überlappende Übereinstimmungen ermöglicht. Dies ist entscheidend in finanziellen Zeitreihen, in denen ein einzelnes Tal möglicherweise legitimerweise der Boden von zwei aufeinanderfolgenden W-Mustern sein könnte (überlappende Fenster). Bewerber verwenden oft standardmäßiges Überspringen und filtern unbeabsichtigt gültige überlappende Signale heraus, was die Sensitivität der Erkennung verringert.
Was ist der Unterschied zwischen RUNNING und FINAL Semantik in der MEASURES-Klausel und wie wirkt sich dies auf aggregierte Berechnungen innerhalb variablenlanger Muster aus?
RUNNING bewertet einen Ausdruck bei jeder aufeinander folgenden Zeile, während das Muster konstruiert wird (z. B. Berechnung eines gleitenden Durchschnitts während des Rückgangsbeins). FINAL bewertet den Ausdruck nur einmal in der letzten Zeile der vollständigen Übereinstimmung unter Verwendung der endgültigen Werte für alle Mustervariablen (z. B. Berechnung der gesamten prozentualen Änderung vom Musterstart bis zum Ende). Bewerber lassen häufig das FINAL-Schlüsselwort aus, wenn sie musterweite Kennzahlen wie MAX(leg_price) - MIN(leg_price) berechnen, was zur Rückgabe von Zwischenwerten aus unvollständigen Übereinstimmungen führt, was zu falschen Handelssignalberechnungen führt.
Wie gehen Sie mit leeren Übereinstimmungen um und stellen sicher, dass nicht übereinstimmende Zeilen zu Debuggingzwecken in der Ausgabe erscheinen?
Standardmäßig filtert MATCH_RECOGNIZE Zeilen heraus, die nicht an einer Übereinstimmung teilnehmen. Um nicht übereinstimmende Zeilen einzuschließen (essentiell für die Prüfung, warum bestimmte Sequenzen die Musterkriterien nicht erfüllten), muss man ALL ROWS PER MATCH zusammen mit SHOW EMPTY MATCHES angeben. In diesem Modus erzeugt jede Eingabezeile eine Ausgabe, wobei die Mustermaße NULL für Zeilen außerhalb der Übereinstimmungen zurückgeben. Zusätzlich gibt MATCH_NUMBER() NULL für nicht übereinstimmende Zeilen zurück. Bewerber kämpfen oft mit "fehlenden Daten" beim Debuggen und sind sich nicht bewusst, dass strenge DEFINE-Bedingungen gültige Zeilen herausgefiltert haben, und sie versäumen es, SHOW EMPTY MATCHES zur Diagnose zu verwenden, welche spezifische boolesche Bedingung (z. B. das zweite Tal ist nicht niedriger als das erste) die Musterablehnung verursacht hat.