MATCH_RECOGNIZE

MATCH_RECOGNIZE — это оператор языка SQL, входящий в стандарт ISO/IEC 9075:2016 (SQL:2016), предназначенный для поиска и извлечения последовательностей строк в таблице, соответствующих заданному шаблону. Он позволяет выполнять распознавание образов (pattern matching) на уровне реляционных данных, что ранее требовало применения сложных оконных функций, рекурсивных запросов или внешней обработки на языках общего назначения.

История и стандартизация

Потребность в распознавании последовательностей в реляционных базах данных возникла в связи с развитием систем обработки событий (CEP) и анализа временных рядов. До появления MATCH_RECOGNIZE разработчики были вынуждены использовать комбинации оконных функций (LAG, LEAD, ROW_NUMBER), самосоединений и хранимых процедур, что делало код громоздким и трудно поддерживаемым.

Работа над стандартизацией началась в конце 2000-х годов в рамках комитета ISO/IEC JTC 1/SC 32. В 2016 году оператор был включён в третью редакцию стандарта SQL (SQL:2016). Первой крупной системой управления базами данных (СУБД), реализовавшей MATCH_RECOGNIZE, стала Oracle Database (версия 12c, выпущенная в 2013 году, ещё до официальной стандартизации). Позднее поддержка появилась в IBM Db2, Teradata, Snowflake, а также в PostgreSQL (начиная с версии 15, 2022 год) и в некоторых других СУБД.

Синтаксис и основные компоненты

Оператор MATCH_RECOGNIZE используется в предложении FROM запроса SELECT. Его синтаксис включает несколько ключевых блоков:

PARTITION BY

Определяет разделение данных на независимые группы (аналогично PARTITION BY в оконных функциях). Распознавание шаблона выполняется отдельно в каждой группе, строки из разных групп не смешиваются.

ORDER BY

Задаёт порядок строк внутри каждой группы. Для корректного распознавания последовательности строки должны быть упорядочены по времени или другому логическому ключу.

MEASURES

Определяет столбцы, которые будут возвращены в результирующем наборе. В этом блоке используются ссылки на переменные шаблона и агрегатные функции, вычисляемые на основе найденного совпадения.

PATTERN

Задаёт шаблон последовательности с помощью регулярного выражения, оперирующего переменными строк. Поддерживаются следующие конструкции:

• Классы символов: переменные, определённые в блоке DEFINE.
• Квантификаторы: * (ноль или более), + (один или более), ? (ноль или один), {n} (ровно n), {n,m} (от n до m).
• Группировка: круглые скобки (...).
• Альтернатива: вертикальная черта |.
• Исключение: {- ... -} (исключает часть последовательности из вывода).
• Ленивые квантификаторы: *?, +?, ??.

DEFINE

Определяет условия, которым должна удовлетворять каждая строка, чтобы быть отнесённой к той или иной переменной шаблона. Условия записываются как логические выражения, которые могут ссылаться на значения текущей строки и на предыдущие строки в рамках одного совпадения.

AFTER MATCH SKIP

Управляет поведением после нахождения совпадения. Возможные значения:

• SKIP PAST LAST ROW — пропустить все строки, участвовавшие в совпадении (по умолчанию).
• SKIP TO NEXT ROW — начать поиск со следующей строки после первой строки совпадения.
• SKIP TO FIRST variable — начать поиск с первой строки, соответствующей указанной переменной.
• SKIP TO LAST variable — начать поиск с последней строки, соответствующей указанной переменной.

ONE ROW PER MATCH / ALL ROWS PER MATCH

Определяет, сколько строк будет возвращено для каждого найденного совпадения. ONE ROW PER MATCH (по умолчанию) возвращает одну строку с агрегированными данными. ALL ROWS PER MATCH возвращает все строки, участвовавшие в совпадении, с добавлением классифицирующих столбцов.

Пример использования

Рассмотрим таблицу stock_prices с колонками symbol (тикер), trading_date (дата) и price (цена закрытия). Требуется найти все случаи, когда цена росла три дня подряд.

``sql SELECT symbol, start_date, end_date, start_price, end_price FROM stock_prices MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY trading_date MEASURES FIRST(up.trading_date) AS start_date, LAST(up.trading_date) AS end_date, FIRST(up.price) AS start_price, LAST(up.price) AS end_price PATTERN (up{3,}) DEFINE up AS up.price > PREV(up.price) ) AS mr; ``

В этом запросе:

• Данные разбиваются по символу акции.
• Строки упорядочиваются по дате.
• Шаблон up{3,} означает одну или более последовательностей из трёх и более строк, где каждая следующая строка дороже предыдущей.
• Функция PREV обращается к значению предыдущей строки в рамках совпадения.
• Результат содержит даты начала и конца роста, а также цены на эти даты.

Применение

MATCH_RECOGNIZE используется в различных областях, где требуется анализ последовательностей:

Финансовый анализ

• Выявление трендов (рост, падение, флэт).
• Поиск графических паттернов (голова и плечи, двойное дно, треугольники).
• Обнаружение аномалий в торговых данных.

Телекоммуникации

• Анализ последовательностей звонков для выявления мошеннических схем.
• Мониторинг качества обслуживания по последовательности событий (сбросы, переключения между базовыми станциями).

Логистика и производство

• Отслеживание движения товаров по цепочке поставок.
• Контроль последовательности технологических операций на производственной линии.

Системы безопасности

• Анализ логов доступа для выявления подозрительных последовательностей действий.
• Обнаружение атак по временным рядам сетевых событий.

Особенности реализации в различных СУБД

Хотя MATCH_RECOGNIZE стандартизирован, реализации в разных СУБД имеют отличия:

• Oracle Database: наиболее полная реализация, включающая все опции стандарта. Поддерживает SUBSET (объединение переменных), WITHIN (ограничение по времени), RUNNING и FINAL ключевые слова.
• PostgreSQL: реализация в версии 15 поддерживает базовый синтаксис, но не включает SUBSET, WITHIN и некоторые типы AFTER MATCH SKIP. Ограничена поддержка вложенных шаблонов.
• Snowflake: реализация близка к стандарту, но имеет собственные расширения, такие как ROW WITH MATCH и PATTERN с поддержкой EXCLUDE.
• IBM Db2: поддерживает MATCH_RECOGNIZE с некоторыми ограничениями, в частности, не поддерживает SUBSET.
• Teradata: реализация, близкая к стандарту, но с собственными особенностями в обработке AFTER MATCH SKIP.

Сравнение с альтернативными подходами

До появления MATCH_RECOGNIZE для распознавания последовательностей использовались:

• Оконные функции: требовали написания сложных самосоединений и рекурсивных CTE, код был трудночитаемым и медленным на больших объёмах данных.
• Рекурсивные CTE (WITH RECURSIVE): позволяли реализовать произвольную логику, но были сложны в отладке и оптимизации.
• Хранимые процедуры: требовали выгрузки данных из базы или использования курсоров, что снижало производительность.
• Внешние инструменты: системы CEP (Apache Flink, Esper) или скрипты на Python/R, что добавляло задержки и усложняло архитектуру.

MATCH_RECOGNIZE объединяет выразительность регулярных выражений с производительностью реляционной обработки, позволяя выполнять распознавание образов непосредственно в ядре СУБД.

Ограничения и критика

• Сложность синтаксиса: оператор имеет много опций, что делает его трудным для изучения и отладки.
• Производительность: на больших объёмах данных и сложных шаблонах запросы могут выполняться медленно, особенно при использовании ALL ROWS PER MATCH.
• Ограниченная поддержка: не все СУБД реализуют полный стандарт, что затрудняет переносимость кода.
• Отсутствие поддержки временных ограничений в некоторых реализациях: стандарт предусматривает опцию WITHIN для ограничения длины последовательности по времени, но она реализована не везде.

Источники

• ISO/IEC 9075:2016, Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation).
• Oracle Database SQL Language Reference, 12c Release 2.
• PostgreSQL 15 Documentation, Chapter 4. SQL Syntax, MATCH_RECOGNIZE.
• Snowflake SQL Reference Guide, MATCH_RECOGNIZE.
• IBM Db2 for z/OS, SQL Reference, MATCH_RECOGNIZE.
• Teradata Database SQL Data Manipulation Language, MATCH_RECOGNIZE.