Risposta alla domanda.

Storia della domanda.

Prima dello standard SQL:2016, l'identificazione di schemi sequenziali multi-riga all'interno di set di dati ordinati richiedeva autoinnesti complicati, logica procedurale basata su cursori o CTE ricorsivi che simulavano macchine a stati finiti. Questi approcci soffrivano di esplosione combinatoria, prestazioni scadenti e incubi di manutenzione. L'introduzione della clausola MATCH_RECOGNIZE ha fornito una sintassi dichiarativa, matematicamente rigorosa basata su espressioni regolari per il riconoscimento degli schemi di riga, consentendo il processamento di eventi complessi direttamente all'interno del motore relazionale.

Il problema.

Rilevare sequenze specifiche di lunghezza variabile—come le formazioni di prezzo a forma di W—richiede di confrontare ogni riga con più predecessori e successori mantenendo uno stato contestuale lungo l'intera sequenza. Le funzioni di finestra standard possono solo fare riferimento a offset fissi (ad es., LAG 1, LEAD 1), rendendole incapaci di gestire schemi in cui le durate delle gambe variano. I CTE ricorsivi possono teoricamente tenere traccia delle transizioni di stato, ma diventano costosi dal punto di vista computazionale e sintatticamente verbose quando gestiscono schemi multi-step con vincoli di ordinamento rigorosi.

La soluzione.

MATCH_RECOGNIZE consente la definizione di variabili di pattern utilizzando condizioni booleane, la specifica del pattern target tramite sintassi di espressione regolare (ad es., A B+ C+ D+ E+), e il calcolo di misure aggregate tra le righe corrispondenti. Gestisce nativamente partizionamento, ordinamento e funzioni di navigazione (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),  -- Deve scendere sotto il picco medio
    UP2   AS UP2.price > PREV(UP2.price)
) AS pattern_matches;

Situazione dalla vita

Contesto.

Una società di trading quantitativo aveva bisogno di rilevare schemi a U di forma W in dati forex ad alta frequenza (tick dopo tick) per automatizzare i punti di entrata per posizioni lunghe. Il pattern richiedeva due distinti fondi separati da un picco, con ogni gamba che rappresentava almeno un movimento di prezzo dello 0,5%.

Il problema.

Il dataset conteneva 10 milioni di righe al giorno attraverso 50 coppie valutarie. Il rilevamento basato su Python introduceva latenza di rete e vincoli di memoria quando si trasferivano gigabyte di dati ogni ora. Gli approcci SQL standard utilizzando molteplici autoinnesti LAG()/LEAD() creavano prodotti cartesiani nel tentativo di correlare le quattro gambe del pattern a W, causando timeout delle query dopo 10 minuti.

Soluzione 1: Elaborazione Python lato client.

Il team inizialmente utilizzava pandas con logica di ciclo personalizzata per rilevare picchi e fondi. Pro: Ricche librerie analitiche, facile testing unitario. Contro: Collo di bottiglia nel trasferimento di dati massici (ore di latenza), esaurimento della memoria sul server applicativo quando si elaborava l'intera storia di mercato, e incapacità di reagire in tempo reale.

Soluzione 2: Macchina a stati CTE ricorsiva.

Hanno tentato un CTE ricorsivo che tracciava cinque stati (0=cercando inizio, 1=prima discesa, 2=prima salita, 3=seconda discesa, 4=seconda salita). Pro: SQL puro, logicamente rigoroso. Contro: Esecuzione a singolo thread nel motore del database, rallentamento esponenziale con ricorsione profonda, e oltre 300 righe di SQL incomprensibile soggette a errori di overflow dello stack su sequenze volatile.

Soluzione 3: Implementazione di MATCH_RECOGNIZE.

Il team ha implementato la query di corrispondenza degli schemi SQL:2016 mostrata sopra. Pro: Ottimizzazione del motore nativo (esecuzione vettorizzata), query concisa di 25 righe che rispecchiava esattamente la definizione matematica del pattern, gestione automatica delle gambe di lunghezza variabile tramite quantificatori (+), e salto efficiente per prevenire corrispondenze sovrapposte ridondanti. Contro: Richiedeva la migrazione del database a Oracle 19c (che supporta le funzionalità di SQL:2016) e una formazione iniziale per sviluppatori non familiari con la sintassi delle espressioni regolari in SQL.

Soluzione scelta e risultato.

La soluzione 3 è stata selezionata per le sue prestazioni sub-secondo nei backtest storici. La clausola AFTER MATCH SKIP TO LAST UP2 garantiva che, una volta che un pattern a W era completato, la scansione riprendesse alla fine del pattern per evitare rilevamenti sovrapposti. Il sistema ha identificato con successo il 99,8% dei pattern a W convalidati manualmente, riducendo la latenza di rilevamento da 45 minuti (Python) a 800 millisecondi, consentendo il trading algoritmico in tempo reale.

Cosa che i candidati spesso dimenticano

Come determina la clausola AFTER MATCH SKIP il punto di ripresa dopo un abbinamento e perché importa SKIP TO NEXT ROW rispetto a SKIP PAST LAST ROW per schemi sovrapposti?

AFTER MATCH SKIP stabilisce dove il rilevatore di pattern continua a scansionare. SKIP PAST LAST ROW (il predefinito) riprende dopo l'ultima riga dell'abbinamento corrente, impedendo a qualsiasi riga di partecipare a più abbinamenti—adatto per il rilevamento di eventi distinti. Al contrario, SKIP TO NEXT ROW riprende alla riga immediatamente successiva alla riga di inizio dell'abbinamento, consentendo abbinamenti sovrapposti. Questo è critico nelle serie temporali finanziarie in cui un singolo fondo potrebbe legittimamente formare il fondo di due pattern a W consecutivi (finestre sovrapposte). I candidati spesso si attengono al salto standard, filtrando inavvertitamente segnali sovrapposti validi e riducendo la sensibilità al rilevamento.

Qual è la distinzione tra le semantiche RUNNING e FINAL nella clausola MEASURES e come impatta questo sui calcoli aggregati all'interno di schemi di lunghezza variabile?

RUNNING valuta un'espressione a ogni riga successiva mentre il match viene costruito (ad es., calcolando una media mobile durante la gamba di discesa). FINAL valuta l'espressione solo una volta all'ultima riga dell'abbinamento completo, utilizzando i valori finali per tutte le variabili di pattern (ad es., calcolando il cambiamento percentuale totale dall'inizio alla fine del pattern). I candidati frequentemente omettono la parola chiave FINAL quando calcolano metriche su scala del pattern come MAX(leg_price) - MIN(leg_price), risultando in valori intermedi da abbinamenti incompleti che portano a calcoli di segnali di trading errati.

Come gestisci gli abbinamenti vuoti e garantisci che le righe non abbinate appaiano nell'output per scopi di debug?

Per impostazione predefinita, MATCH_RECOGNIZE filtra le righe che non partecipano a nessun abbinamento. Per includere righe non abbinate (essenziali per verificare perché certe sequenze non hanno soddisfatto i criteri del pattern), è necessario specificare ALL ROWS PER MATCH combinato con SHOW EMPTY MATCHES. In questa modalità, ogni riga di input genera output, con misure del pattern che restituiscono NULL per le righe al di fuori degli abbinamenti. Inoltre, MATCH_NUMBER() restituisce NULL per le righe non abbinate. I candidati spesso hanno difficoltà con il debug dei "dati mancanti", ignari che condizioni DEFINE rigorose hanno filtrato righe valide, e non utilizzano SHOW EMPTY MATCHES per diagnosticare quale specifica condizione booleana (ad es., il secondo fondo che non è al di sotto del primo) ha causato il rifiuto del pattern.