Antwoord op de vraag.

Geschiedenis van de vraag.

Voordat de SQL:2016 standaard werd geïntroduceerd, vereiste het identificeren van multi-rij sequentiële patronen binnen geordende datasets ingewikkelde zelf-joins, cursor-gebaseerde procedurele logica, of recursieve CTE's die eindige toestandsmachines simuleerden. Deze benaderingen leden onder combinatoriële explosie, slechte prestaties en onderhoudsproblemen. De introductie van de MATCH_RECOGNIZE clausule bood een declaratieve, mathematisch rigoureuze syntaxis gebaseerd op reguliere expressies voor rijpatroonherkenning, waardoor complexe gebeurtenisverwerking direct binnen de relationele engine mogelijk werd.

Het probleem.

Het detecteren van specifieke sequenties met variabele lengte—zoals W-vormige prijsformaties—vereist het vergelijken van elke rij met meerdere voorgangers en opvolgers, terwijl de contextuele status over de hele sequentie behouden blijft. Standaard vensterfuncties kunnen alleen vaste offsets refereren (bijv. LAG 1, LEAD 1), waardoor ze niet in staat zijn om patronen te verwerken waarbij de duur van de benen varieert. Recursieve CTE's kunnen theoretisch toestandsovergangen volgen, maar worden computationeel duur en syntactisch zeer uitgebreid bij het omgaan met meer-staps patronen met strikte volgordebeperkingen.

De oplossing.

MATCH_RECOGNIZE maakt het mogelijk om patroonvariabelen te definiëren met behulp van booleaanse voorwaarden, het specificeren van het doelpatroon via reguliere expressiesyntaxis (bijv. A B+ C+ D+ E+), en het berekenen van aggregaatmetingen over de overeenkomende rijen. Het behandelt partitionering, ordening en navigatiefuncties (PREV, NEXT, FIRST, LAST) van nature.

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),  -- Moet onder de middelste piek dalen
    UP2   AS UP2.price > PREV(UP2.price)
) AS pattern_matches;

Situatie uit het leven

Context.

Een kwantitatief handelsbedrijf moest W-vormige double-bottom patronen in high-frequency forex data (tick-by-tick) detecteren om instappunten voor lange posities te automatiseren. Het patroon vereiste twee verschillende troughs gescheiden door een piek, waarbij elk been ten minste een prijsbeweging van 0.5% vertegenwoordigde.

Het probleem.

De dataset bevatte 10 miljoen rijen dagelijks over 50 valuta-paren. Detectie op basis van Python introduceerde netwerklatentie en geheugenbeperkingen bij het overdragen van gigabytes aan gegevens per uur. Standaard SQL-benaderingen met meerdere LAG()/LEAD() zelf-joins creëerden cartesiaanse producten bij het proberen correlatie te maken met de vier benen van het W-patroon, waardoor query's na 10 minuten time-out gingen.

Oplossing 1: Client-side Python verwerking.

Het team gebruikte aanvankelijk pandas met aangepaste lussen om pieken en troughs te detecteren. Voordelen: Rijke analytische bibliotheken, gemakkelijke unit testing. Nadelen: Massale gegevensoverdrachts bottleneck (uren van latentie), geheugenuitputting op de applicatieserver bij het verwerken van de volledige markthistorie, en onvermogen om in real-time te reageren.

Oplossing 2: Recursieve CTE toestandmachine.

Ze probeerden een recursieve CTE die vijf staten volgde (0=zoekt begin, 1=eerste daling, 2=eerste stijging, 3=tweede daling, 4=tweede stijging). Voordelen: Pure SQL, logisch rigoureus. Nadelen: Eentjevoudige uitvoering in de database-engine, exponentiële vertraging bij diepe recursie, en 300+ lijnen onbegrijpelijke SQL die vatbaar was voor stack overflow fouten op onvoorspelbare reeksen.

Oplossing 3: MATCH_RECOGNIZE implementatie.

Het team implementeerde de SQL:2016 patroonherkenning query hierboven getoond. Voordelen: Natuurlijke engine-optimalisatie (gevectoriseerde uitvoering), beknopte 25-regelige query die exact de wiskundige patroondefinitie weergeeft, automatische afhandeling van variabele lengte benen via kwantifiers (+), en efficiënte overslaan om redundante overlapping matches te voorkomen. Nadelen: Vereiste database-migratie naar Oracle 19c (dat SQL:2016-functies ondersteunt) en initiële training voor ontwikkelaars die niet bekend waren met reguliere expressiesyntaxis in SQL.

Gekozen oplossing en resultaat.

Oplossing 3 werd geselecteerd vanwege de sub-seconde prestatie op historische backtests. De AFTER MATCH SKIP TO LAST UP2 clausule zorgde ervoor dat zodra een W-patroon was voltooid, de scan opnieuw begon aan het einde van het patroon om overlappingdetectie te voorkomen. Het systeem identificeerde met succes 99.8% van de handmatig gevalideerde W-patronen, waardoor de detectietijd van 45 minuten (Python) naar 800 milliseconden verkort werd, waardoor real-time algoritmische handel mogelijk werd.

Wat kandidaten vaak missen

Hoe bepaalt de AFTER MATCH SKIP clausule het hervattingspunt na een match en waarom is SKIP TO NEXT ROW versus SKIP PAST LAST ROW belangrijk voor overlappende patronen?

AFTER MATCH SKIP bepaalt waar de patroonmatcher blijft scannen. SKIP PAST LAST ROW (de standaard) hervat na de laatste rij van de huidige match, waardoor geen rij kan deelnemen aan meerdere matches—geschikt voor het detecteren van distincte gebeurtenissen. Daarentegen hervat SKIP TO NEXT ROW bij de rij onmiddellijk volgend op de startrij van de match, wat overlappende matches mogelijk maakt. Dit is kritiek in financiële tijdreeksen waar een enkele trough legitiem de bodem kan vormen van twee opeenvolgende W-patronen (overlappende vensters). Kandidaten vallen vaak terug op standaard overslaan, en filteren onbedoeld geldige overlappende signalen eruit en verlagen de detectie-sensitiviteit.

Wat is het onderscheid tussen RUNNING en FINAL semantiek in de MEASURES clausule, en hoe beïnvloedt dit aggregaatberekeningen binnen variabele lengte patronen?

RUNNING evalueert een expressie bij elke opvolgende rij terwijl de match wordt geconstrueerd (bijv. het berekenen van een voortschrijdend gemiddelde tijdens de daling). FINAL evalueert de expressie slechts één keer bij de laatste rij van de complete match, met gebruik van de laatste gebonden waarden voor alle patroonvariabelen (bijv. het berekenen van de totale procentuele verandering van patroonbegin tot eind). Kandidaten vergeten vaak het FINAL-trefwoord bij het berekenen van patroonbrede metrics zoals MAX(leg_price) - MIN(leg_price), wat resulteert in tussenliggende waarden van onvolledige matches die worden teruggegeven, wat leidt tot onjuiste handelsignaalberekeningen.

Hoe ga je om met lege matches en zorg je ervoor dat niet-matched rijen in de output verschijnen voor debuggingdoeleinden?

Standaard filtert MATCH_RECOGNIZE rijen die niet deelnemen aan een match eruit. Om niet-gematchte rijen op te nemen (essentieel voor het controleren waarom bepaalde sequenties niet voldeden aan de patrooncriteria), moet men ALL ROWS PER MATCH samen met SHOW EMPTY MATCHES specificeren. In deze modus genereert elke invoerrij output, waarbij patroonmetingen NULL retourneren voor rijen buiten matches. Bovendien retourneert MATCH_NUMBER() NULL voor niet-gematchte rijen. Kandidaten hebben vaak moeite met het debuggen van "missende gegevens", onwetend dat strikte DEFINE voorwaarden geldige rijen filterden, en ze vergeten SHOW EMPTY MATCHES te gebruiken om te diagnosticeren welke specifieke booleaanse voorwaarde (bijv. de tweede trough die niet lager is dan de eerste) de patroonafwijzing veroorzaakte.