Réponse à la question.
Histoire de la question.
Avant la norme SQL:2016, l'identification de motifs séquentiels multiligne au sein d'ensembles de données ordonnées nécessitait des auto-joints compliqués, une logique procédurale basée sur des curseurs, ou des CTE récursifs simulant des machines à états finis. Ces approches souffraient d'explosion combinatoire, de mauvaise performance et de cauchemars de maintenance. L'introduction de la clause MATCH_RECOGNIZE a fourni une syntaxe déclarative, mathématiquement rigoureuse, basée sur des expressions régulières pour la reconnaissance de motifs de ligne, permettant le traitement d'événements complexes directement au sein du moteur relationnel.
Le problème.
Détecter des séquences de longueur variable spécifiques - telles que des formations de prix en forme de W - nécessite de comparer chaque ligne avec plusieurs prédécesseurs et successeurs tout en maintenant un état contextuel à travers l'ensemble de la séquence. Les fonctions de fenêtre standard ne peuvent référencer que des offsets fixes (par exemple, LAG 1, LEAD 1), rendant impossible la gestion de motifs où la durée des jambes varie. Les CTE récursifs peuvent théoriquement suivre les transitions d'état mais deviennent coûteux en calcul et syntactiquement verbeux lorsqu'ils traitent des motifs à plusieurs étapes avec des contraintes d'ordre strictes.
La solution.
MATCH_RECOGNIZE permet la définition de variables de motif en utilisant des conditions boolean, la spécification du motif cible via une syntaxe d'expressions régulières (par exemple, A B+ C+ D+ E+), et le calcul de mesures agrégées à travers les lignes correspondantes. Il gère nativement le partitionnement, le tri et les fonctions de navigation (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), -- Doit descendre en dessous du pic moyen UP2 AS UP2.price > PREV(UP2.price) ) AS pattern_matches;
Situation de la vie
Contexte.
Une société de trading quantitatif devait détecter des motifs en double fond en forme de W dans des données Forex à haute fréquence (tick par tick) pour automatiser les points d'entrée pour les positions longues. Le motif nécessitait deux creux distincts séparés par un pic, chaque jambe représentant au moins un mouvement de prix de 0.5%.
Le problème.
L'ensemble de données contenait 10 millions de lignes quotidiennement à travers 50 paires de devises. La détection basée sur Python a introduit une latence réseau et des contraintes de mémoire lors du transfert de gigaoctets de données horaires. Les approches SQL standard utilisant plusieurs auto-joints LAG()/LEAD() ont créé des produits cartésiens lors de la tentative de corréler les quatre jambes du motif W, provoquant l'expiration des requêtes après 10 minutes.
Solution 1 : Traitement côté client avec Python.
L'équipe a initialement utilisé pandas avec une logique de boucle personnalisée pour détecter les pics et les creux. Avantages : Bibliothèques analytiques riches, tests unitaires faciles. Inconvénients : Goulot d'étranglement de transfert de données massif (heures de latence), épuisement de la mémoire sur le serveur d'application lors du traitement de l'historique complet du marché, et incapacité à réagir en temps réel.
Solution 2 : machine à états par CTE récursifs.
Ils ont tenté une CTE récursive suivant cinq états (0 = recherche de début, 1 = première baisse, 2 = première hausse, 3 = deuxième baisse, 4 = deuxième hausse). Avantages : SQL pur, logiquement rigoureux. Inconvénients : Exécution mono-fil dans le moteur de base de données, ralentissement exponentiel avec une récursion profonde, et plus de 300 lignes de SQL incompréhensible sujettes aux erreurs de débordement de pile sur des séquences volatiles.
Solution 3 : Mise en œuvre de MATCH_RECOGNIZE.
L'équipe a mis en œuvre la requête de correspondance de motifs SQL:2016 montrée ci-dessus. Avantages : Optimisation par le moteur natif (exécution vectorisée), requête concise de 25 lignes qui reflétait exactement la définition mathématique du motif, gestion automatique des jambes de longueur variable via des quantificateurs (+), et saut efficace pour éviter les correspondances redondantes qui se chevauchent. Inconvénients : Nécessitait la migration de la base de données vers Oracle 19c (qui prend en charge les fonctionnalités SQL:2016) et une formation initiale pour les développeurs peu familiers avec la syntaxe des expressions régulières en SQL.
Solution choisie et résultat.
La solution 3 a été sélectionnée en raison de sa performance inférieure à une seconde lors des tests rétrospectifs historiques. La clause AFTER MATCH SKIP TO LAST UP2 garantissait qu'une fois qu'un motif W était complété, le scan reprenait à la fin du motif pour éviter les détections superposées. Le système a réussi à identifier 99.8% des motifs W validés manuellement, réduisant la latence de détection de 45 minutes (Python) à 800 millisecondes, permettant le trading algorithmique en temps réel.
Ce que les candidats manquent souvent
Comment la clause AFTER MATCH SKIP détermine le point de reprise après une correspondance, et pourquoi SKIP TO NEXT ROW versus SKIP PAST LAST ROW importe-t-il pour les motifs se chevauchant ?
AFTER MATCH SKIP dicte où le moteur de correspondance de motifs continue de scanner. SKIP PAST LAST ROW (la valeur par défaut) reprend après la dernière ligne de la correspondance actuelle, empêchant toute ligne de participer à plusieurs correspondances - convenant à la détection d'événements distincts. En revanche, SKIP TO NEXT ROW reprend à la ligne immédiatement après la ligne de début de la correspondance, permettant des correspondances se chevauchant. Ceci est critique dans les séries temporelles financières où un seul creux pourrait légitimement former le bas de deux motifs W consécutifs (fenêtres se chevauchant). Les candidats se basent souvent sur le saut standard, filtrant involontairement des signaux de chevauchement valides et réduisant la sensibilité de détection.
Quelle est la distinction entre les sémantiques RUNNING et FINAL dans la clause MEASURES, et comment cela impacte-t-il les calculs agrégés au sein des motifs de longueur variable ?
RUNNING évalue une expression à chaque ligne successive au fur et à mesure que la correspondance est construite (par exemple, calculer une moyenne mobile pendant la jambe de déclin). FINAL évalue l'expression une seule fois à la dernière ligne de la correspondance complète, utilisant les valeurs finales liées pour toutes les variables du motif (par exemple, calculer le changement de pourcentage total du début à la fin du motif). Les candidats omettent souvent le mot clé FINAL lors de la computation de statistiques à l'échelle du motif comme MAX(leg_price) - MIN(leg_price), entraînant le retour de valeurs intermédiaires de correspondances incomplètes, ce qui conduit à des calculs de signaux de négociation incorrects.
Comment gérez-vous les correspondances vides et assurez-vous que les lignes non correspondantes apparaissent dans la sortie à des fins de débogage ?
Par défaut, MATCH_RECOGNIZE filtre les lignes qui ne participent à aucune correspondance. Pour inclure les lignes non correspondantes (essentielles pour vérifier pourquoi certaines séquences n'ont pas satisfait aux critères de motif), il faut spécifier ALL ROWS PER MATCH combiné avec SHOW EMPTY MATCHES. Dans ce mode, chaque ligne d'entrée génère une sortie, les mesures de motif retournant NULL pour les lignes hors correspondances. De plus, MATCH_NUMBER() retourne NULL pour les lignes non correspondantes. Les candidats ont souvent des difficultés avec le débogage de "données manquantes", ignorant que des conditions strictes DEFINE ont filtré des lignes valides, et ils ne parviennent pas à utiliser SHOW EMPTY MATCHES pour diagnostiquer quelle condition boolean spécifique (par exemple, le deuxième creux ne se situant pas sous le premier) a causé le rejet du motif.