Ответ на вопрос.

История вопроса.

До стандарта SQL:2016 идентификация многорядных последовательных шаблонов в упорядоченных наборах данных требовала запутанных самосоединений, логики на основе курсоров или рекурсивных CTE, имитирующих конечные автоматы. Эти подходы страдали от комбинаторного взрыва, плохой производительности и сложностей с обслуживанием. Введение оператора MATCH_RECOGNIZE предоставило декларативный, математически строгий синтаксис, основанный на регулярных выражениях для распознавания рядовых шаблонов, что позволило обрабатывать сложные события непосредственно в реляционном движке.

Проблема.

Обнаружение специфических последовательностей переменной длины, таких как W-образные ценовые образования, требует сравнивать каждую строку с несколькими предшественниками и последователями, при этом поддерживая контекстное состояние на протяжении всей последовательности. Стандартные оконные функции могут ссылаться только на фиксированные смещения (например, LAG 1, LEAD 1), что делает их неспособными справляться с шаблонами, где продолжительность ног варьируется. Рекурсивные CTE теоретически могут отслеживать переходы состояния, но становятся затратными по вычислениям и синтаксически многословными при обработке многошаговых шаблонов с строгими ограничениями по порядку.

Решение.

MATCH_RECOGNIZE позволяет определить переменные шаблона с использованием булевых условий, специфицировать целевой шаблон с помощью синтаксиса регулярных выражений (например, A B+ C+ D+ E+), и вычислять агрегатные показатели по совпавшим строкам. Он обрабатывает разбиение на группы, упорядочение и навигационные функции (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),  -- Должен опуститься ниже среднего пика
    UP2   AS UP2.price > PREV(UP2.price)
) AS pattern_matches;

Ситуация из жизни

Контекст.

Квантитативная торговая фирма нуждалась в обнаружении W-образных двойных днообразующих паттернов в данных по форексу высокой частоты (тик за тиком), чтобы автоматизировать точки входа для длинных позиций. Шаблон требовал наличия двух отдельных впадин, разделенных пиком, причем каждая нога должна была представлять как минимум 0,5% движения цены.

Проблема.

Набор данных содержал 10 миллионов строк ежедневно по 50 валютным парам. Обнаружение на основе Python привело к задержкам в сети и ограничениям памяти при передаче гигабайтов данных ежечасно. Стандартные SQL-подходы, использующие несколько самосоединений LAG()/LEAD(), создавали декартовы произведения при попытке сопоставить четыре ноги W-шаблона, что вызывало превышение времени ожидания запросов после 10 минут.

Решение 1: Обработка на стороне клиента с использованием Python.

Команда изначально использовала pandas с пользовательской логикой циклов для обнаружения пиков и впадин. Плюсы: Богатые аналитические библиотеки, легкость юнит-тестирования. Минусы: Огромный узкое место в передаче данных (часы задержки), исчерпание памяти на сервере приложений при обработке полной истории рынка и невозможность реагировать в реальном времени.

Решение 2: Рекурсивная CTE автомат состояния.

Они попытались создать рекурсивную CTE для отслеживания пяти состояний (0=поиск начала, 1=первый спад, 2=первый подъем, 3=второй спад, 4=второй подъем). Плюсы: Чистый SQL, логически строгий. Минусы: Однопоточное выполнение в движке базы данных, экспоненциальное замедление при глубокой рекурсии и 300+ строк непонятного SQL, подверженные ошибкам переполнения стека на волатильных последовательностях.

Решение 3: Реализация MATCH_RECOGNIZE.

Команда реализовала SQL:2016 запрос на сопоставление шаблонов, показанный выше. Плюсы: Оптимизация нативного движка (векторизованное выполнение), лаконичный 25-строчный запрос, который точно отражал математическое определение шаблона, автоматическая обработка переменной длины ног с помощью квантификаторов (+), и эффективное пропускание для предотвращения избыточных пересекающихся совпадений. Минусы: Требовалась миграция базы данных на Oracle 19c (которая поддерживает функции SQL:2016) и первоначальное обучение для разработчиков, не знакомых с синтаксисом регулярных выражений в SQL.

Выбранное решение и результат.

Решение 3 было выбрано благодаря его производительности менее одной секунды на историческом тестировании. Клауза AFTER MATCH SKIP TO LAST UP2 обеспечила то, что после завершения W-шаблона сканирование продолжалось с конца шаблона, чтобы избежать перекрывающихся обнаружений. Система успешно определила 99.8% вручную проверенных W-шаблонов, сократив задержку обнаружения с 45 минут (Python) до 800 миллисекунд, что позволило осуществлять алгоритмическую торговлю в реальном времени.

Что часто упускают кандидаты

Как клауза AFTER MATCH SKIP определяет точку возобновления после совпадения, и почему важно различать SKIP TO NEXT ROW и SKIP PAST LAST ROW для перекрывающихся шаблонов?

AFTER MATCH SKIP определяет, где сопоставитель шаблонов продолжает сканирование. SKIP PAST LAST ROW (по умолчанию) возобновляет после последней строки текущего совпадения, предотвращая участие любой строки в нескольких совпадениях — подходяще для обнаружения отдельных событий. Напротив, SKIP TO NEXT ROW возобновляет сразу после строки, с которой началось совпадение, что позволяет перекрывающимся совпадениям. Это критично в финансовых временных рядах, где одна впадина может действительно образовать дно двух последовательных W-шаблонов (перекрывающиеся окна). Кандидаты часто по умолчанию возвращаются к стандартному пропусканию, невольно фильтруя действительные перекрывающие сигналы и уменьшая чувствительность обнаружения.

В чем разница между семантикой RUNNING и FINAL в клаузе MEASURES и как это влияет на агрегатные вычисления в переменной длине шаблонов?

RUNNING оценивает выражение для каждой последующей строки по мере построения совпадения (например, вычисление скользящего среднего во время снижения). FINAL оценивает выражение только один раз на последней строке полного совпадения, используя последние границы для всех переменных шаблона (например, вычисление общего процентного изменения от начала до конца шаблона). Кандидаты часто упускают ключевое слово FINAL, когда вычисляют метрики по всему шаблону, такие как MAX(leg_price) - MIN(leg_price), в результате чего возвращаются промежуточные значения из неполных совпадений, что ведет к неправильным расчетам торговых сигналов.

Как вы обрабатываете пустые совпадения и обеспечиваете появление несовпадающих строк в выводе для отладки?

По умолчанию MATCH_RECOGNIZE отфильтровывает строки, которые не участвуют ни в каком совпадении. Чтобы включить несовпадающие строки (что важно для аудита, почему определенные последовательности не соответствовали критериям шаблона), необходимо указать ALL ROWS PER MATCH в сочетании с SHOW EMPTY MATCHES. В этом режиме каждая входная строка генерирует вывод, а показатели шаблона возвращают NULL для строк вне совпадений. Кроме того, MATCH_NUMBER() возвращает NULL для несовпадающих строк. Кандидаты часто сталкиваются с проблемами "отсутствующих данных" в отладке, не осознавая, что строгие условия DEFINE отфильтровали действительные строки, и не используют SHOW EMPTY MATCHES, чтобы диагностировать, какое конкретное булевое условие (например, вторая впадина не ниже первой) привело к отклонению шаблона.