MATCH_RECOGNIZE#

Синтаксис#

MATCH_RECOGNIZE (
  [ PARTITION BY column [, ...] ]
  [ ORDER BY column [, ...] ]
  [ MEASURES measure_definition [, ...] ]
  [ rows_per_match ]
  [ AFTER MATCH skip_to ]
  PATTERN ( row_pattern )
  [ SUBSET subset_definition [, ...] ]
  DEFINE variable_definition [, ...]
  )

Описание#

Выражение MATCH_RECOGNIZE является необязательным подвыражением выражения FROM. Оно используется для обнаружения паттернов в наборе строк. Паттерны задаются с использованием синтаксиса, основанного на регулярных выражениях. Входными данными для поиска паттернов является таблица, представление или подзапрос. Для каждого обнаруженного совпадения возвращается одна или несколько строк, содержащих запрошенную информацию о совпадении.

Поиск паттернов в строках — мощный инструмент для анализа сложных последовательностей событий. Ниже приведены примеры типичных сценариев использования:

  • в торговых приложениях — отслеживание трендов или выявление клиентов с определенными поведенческими паттернами

  • в логистических приложениях — отслеживание посылок по всем возможным допустимым маршрутам

  • в финансовых приложениях — обнаружение нетипичных инцидентов, которые могут сигнализировать о мошенничестве

Пример#

В следующем примере паттерн описывает V-образную форму изменения значения столбца totalprice. Совпадение обнаруживается каждый раз, когда заказы клиента сначала уменьшаются в цене, а затем увеличиваются, превышая начальное значение:

SELECT * FROM orders MATCH_RECOGNIZE(
     PARTITION BY custkey
     ORDER BY orderdate
     MEASURES
              A.totalprice AS starting_price,
              LAST(B.totalprice) AS bottom_price,
              LAST(U.totalprice) AS top_price
     ONE ROW PER MATCH
     AFTER MATCH SKIP PAST LAST ROW
     PATTERN (A B+ C+ D+)
     SUBSET U = (C, D)
     DEFINE
              B AS totalprice < PREV(totalprice),
              C AS totalprice > PREV(totalprice) AND totalprice <= A.totalprice,
              D AS totalprice > PREV(totalprice)
     )

В следующих разделах все подвыражения MATCH_RECOGNIZE объясняются на примере данного запроса.

Партиционирование и сортировка#

PARTITION BY custkey

Выражение PARTITION BY позволяет разделить входную таблицу на отдельные секции, которые независимо обрабатываются для поиска паттернов. Без объявления партиций используется вся входная таблица. Данное поведение аналогично семантике выражения PARTITION BY в спецификации оконных функций. В примере таблица orders партиционируется по значению custkey, поэтому поиск паттернов выполняется для заказов конкретного клиента независимо от заказов других клиентов.

ORDER BY orderdate

Необязательное выражение ORDER BY полезно для поиска паттернов в упорядоченном наборе данных. Например, сортировка входных данных по orderdate позволяет находить тренды изменений во времени.

Меры строкового паттерна#

Выражение MEASURES позволяет указать, какая информация извлекается из найденной последовательности строк.

MEASURES measure_expression AS measure_name [, ...]

Выражение меры — это скалярное выражение, значение которого вычисляется на основе совпадения. В примере заданы три меры:

A.totalprice AS starting_price возвращает цену в первой строке совпадения, которая является единственной строкой, связанной с A в соответствии с паттерном.

LAST(B.totalprice) AS bottom_price возвращает наименьшую цену (соответствующую нижней точке «V» в паттерне). Это цена в последней строке, связанной с B, то есть в последней строке нисходящего участка.

LAST(U.totalprice) AS top_price возвращает наибольшую цену в совпадении. Это цена в последней строке, связанной с C или D, которая также является последней строкой совпадения.

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

Каждая мера определяет выходной столбец результата распознавания паттерна. На столбец можно ссылаться по имени measure_name.

Выражение MEASURES является необязательным. Если меры не указаны, определенные входные столбцы (в зависимости от выражения ROWS PER MATCH) являются результатом распознавания паттерна.

Количество строк на совпадение#

Данное выражение используется для задания количества выходных строк. Существуют два основных варианта:

ONE ROW PER MATCH

и

ALL ROWS PER MATCH

ONE ROW PER MATCH — вариант по умолчанию. Для каждого совпадения формируется одна выходная строка. Результат состоит из столбцов PARTITION BY и мер. Результат также формируется для пустых совпадений на основе их начальных строк. Строки, не вошедшие ни в какое непустое совпадение и не являющиеся начальными строками пустого совпадения, не включаются в результат.

Для ALL ROWS PER MATCH каждая строка совпадения формирует выходную строку, если она не исключена из результата с помощью синтаксиса исключения. Результат состоит из столбцов PARTITION BY, столбцов ORDER BY, мер и остальных столбцов входной таблицы. По умолчанию пустые совпадения отображаются, а несовпавшие строки пропускаются, аналогично варианту ONE ROW PER MATCH. Однако это поведение может быть изменено с помощью модификаторов:

ALL ROWS PER MATCH SHOW EMPTY MATCHES

отображает пустые совпадения и пропускает несовпавшие строки, как и по умолчанию.

ALL ROWS PER MATCH OMIT EMPTY MATCHES

исключает пустые совпадения из результата.

ALL ROWS PER MATCH WITH UNMATCHED ROWS

отображает пустые совпадения и формирует дополнительную выходную строку для каждой несовпавшей строки.

Существуют специальные правила вычисления мер для пустых совпадений и несовпавших строк. Они описаны в разделе Вычисление выражений для пустых совпадений и несовпавших строк.

Несовпавшие строки могут возникнуть только тогда, когда паттерн не допускает пустого совпадения. В противном случае они считаются начальными строками пустых совпадений. Вариант ALL ROWS PER MATCH WITH UNMATCHED ROWS рекомендуется, когда ожидается, что распознавание паттерна пропустит все входные строки, и нет уверенности, допускает ли паттерн пустое совпадение.

Пропуск после совпадения#

Выражение AFTER MATCH SKIP определяет, с какой позиции возобновляется поиск паттерна после нахождения непустого совпадения.

Вариант по умолчанию:

AFTER MATCH SKIP PAST LAST ROW

В этом случае поиск паттерна начинается со строки, следующей за последней строкой совпадения. Перекрывающиеся совпадения не обнаруживаются.

В следующем варианте поиск паттерна начинается со второй строки совпадения:

AFTER MATCH SKIP TO NEXT ROW

В примере, если обнаружена V-образная форма, далее находятся перекрывающиеся совпадения, начинающиеся с последовательных строк на нисходящем склоне «V». Переход к следующей строке является поведением по умолчанию после обнаружения пустого совпадения или несовпавшей строки.

Следующие варианты AFTER MATCH SKIP позволяют возобновить поиск паттерна на основе компонентов паттерна. Поиск начинается с последней (по умолчанию) или первой строки, сопоставленной с определенной переменной строкового паттерна. Это может быть либо первичная переменная паттерна (описана в разделе Синтаксис строкового паттерна), либо объединенная переменная:

AFTER MATCH SKIP TO [ FIRST | LAST ] pattern_variable

Запрещено переходить к первой строке текущего совпадения, так как это приводит к бесконечному циклу. Например, указание AFTER MATCH SKIP TO A завершится ошибкой, поскольку A — первый элемент паттерна, и возврат к нему создает бесконечный цикл. Аналогично, переход к переменной паттерна, отсутствующей в совпадении, также вызывает ошибку.

Все варианты, кроме AFTER MATCH SKIP PAST LAST ROW (вариант по умолчанию), допускают обнаружение перекрывающихся совпадений. Комбинация ALL ROWS PER MATCH WITH UNMATCHED ROWS с AFTER MATCH SKIP PAST LAST ROW — единственная конфигурация, гарантирующая ровно одну выходную строку для каждой входной строки.

Синтаксис строкового паттерна#

Строковый паттерн — это разновидность регулярного выражения с некоторыми синтаксическими расширениями, специфичными для распознавания паттернов в строках. Он задается в выражении PATTERN:

PATTERN ( row_pattern )

Базовым элементом строкового паттерна является первичная переменная паттерна. Подобно тому как поиск паттернов в символьных строках ищет символы, поиск паттернов в последовательностях строк ищет строки, которые могут быть «помечены» определенными первичными переменными паттерна. Первичная переменная паттерна имеет форму идентификатора и определяется логическим условием. Это условие определяет, может ли конкретная входная строка быть сопоставлена с данной переменной и участвовать в совпадении.

В примере PATTERN (A B+ C+ D+) используются четыре первичные переменные паттерна: A, B, C и D.

Синтаксис строкового паттерна включает следующие конструкции:

конкатенация#

A B+ C+ D+

Последовательность компонентов без операторов между ними. Все компоненты сопоставляются в том же порядке, в котором они указаны.

альтернация#

A | B | C

Последовательность компонентов, разделенных символом |. Сопоставляется ровно один из компонентов. Если несколько компонентов могут быть сопоставлены, выбирается самый левый подходящий компонент.

перестановка#

PERMUTE(A, B, C)

Эквивалентна альтернации всех перестановок своих компонентов. Все компоненты сопоставляются в некотором порядке. Если возможны несколько совпадений при разных порядках компонентов, совпадение выбирается на основе лексикографического порядка, определяемого порядком компонентов в списке PERMUTE. В приведенном примере наиболее предпочтительный вариант — A B C, наименее предпочтительный — C B A.

группировка#

(A B C)

якорь начала партиции#

^

Якорь конца партиции#

$

Пустой паттерн#

()

Синтаксис исключения#

{- row_pattern -}

Синтаксис исключения используется для указания частей совпадения, которые следует исключить из результата. Он полезен в сочетании с вариантом ALL ROWS PER MATCH, когда интересны только определенные части совпадения.

Если изменить пример на использование ALL ROWS PER MATCH и модифицировать паттерн на PATTERN (A {- B+ C+ -} D+), результат будет состоять из начальной совпавшей строки и завершающей секции строк.

Указание исключений не влияет на вычисление выражений в выражениях MEASURES и DEFINE. Исключения также не влияют на поиск паттернов. Они имеют ту же семантику, что и обычная группировка скобками.

Использование исключений запрещено совместно с вариантом ALL ROWS PER MATCH WITH UNMATCHED ROWS.

квантификаторы#

Квантификаторы паттерна позволяют задать желаемое количество повторений подпаттерна в совпадении. Они добавляются после соответствующего компонента паттерна:

(A | B)*

Существуют следующие квантификаторы строковых паттернов:

  • ноль или более повторений:

*

  • одно или более повторений:

+

  • ноль или одно повторение:

?

  • точное количество повторений, заданное неотрицательным целым числом:

{n}

  • количество повторений в диапазоне, заданном неотрицательными целыми числами:

{m, n}

Указание границ необязательно. Если левая граница опущена, она по умолчанию равна 0. Так, {, 5} можно описать как «от нуля до пяти повторений». Если правая граница опущена, количество допустимых повторений не ограничено. Так, {5, } можно описать как «не менее пяти повторений». Также {,} эквивалентно *.

По умолчанию квантификаторы являются жадными. Это означает, что большее количество повторений предпочитается меньшему. Это поведение можно изменить на ленивое, добавив ? сразу после квантификатора. Для {3, 5} 3 повторения — наименее предпочтительный вариант, а 5 повторений — наиболее предпочтительный. Для {3, 5}? 3 повторения являются наиболее предпочтительными. Аналогично, ? предпочитает 1 повторение, а ?? предпочитает 0 повторений.

Объединенные переменные строкового паттерна#

Как описано в разделе Синтаксис строкового паттерна, первичные переменные паттерна являются базовыми элементами строкового паттерна. Помимо первичных переменных паттерна, можно определить объединенные переменные. Они задаются в выражении SUBSET:

SUBSET U = (C, D), ...

В приведенном примере объединенная переменная U определена как объединение первичных переменных C и D. Объединенные переменные полезны в выражениях MEASURES, DEFINE и AFTER MATCH SKIP. Они позволяют ссылаться на набор строк, сопоставленных с любой первичной переменной из подмножества.

Для паттерна PATTERN((A | B){5} C+) невозможно заранее определить, содержит ли совпадение какие-либо A или B. Объединенная переменная позволяет обратиться к последней строке, сопоставленной с A или B. Определите SUBSET U = (A, B), и выражение LAST(U.totalprice) вернет значение столбца totalprice из последней строки, сопоставленной с A или B. Также AFTER MATCH SKIP TO LAST A или AFTER MATCH SKIP TO LAST B могут завершиться ошибкой, если A или B отсутствуют в совпадении. AFTER MATCH SKIP TO LAST U ошибки не вызовет.

Определения переменных строкового паттерна#

В выражении DEFINE задаются определения первичных переменных строкового паттерна. Каждая переменная связана с логическим условием:

DEFINE B AS totalprice < PREV(totalprice), ...

Во время поиска паттерна, когда определенная переменная рассматривается для следующего шага совпадения, логическое условие вычисляется в контексте текущего совпадения. Если результат равен true, текущая строка, «помеченная» данной переменной, становится частью совпадения.

В приведенном примере предположим, что паттерн допускает сопоставление B на определенном этапе. Некоторые строки уже сопоставлены с некоторыми переменными паттерна. Теперь переменная B рассматривается для текущей строки. Перед сопоставлением вычисляется определяющее условие для B. В данном примере оно истинно только в том случае, если значение столбца totalprice в текущей строке меньше, чем totalprice в предыдущей строке.

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

Не обязательно, чтобы каждая первичная переменная имела определение в выражении DEFINE. Переменные, не упомянутые в выражении DEFINE, неявно связаны с условием true, что означает возможность их сопоставления с любой строкой.

Логические выражения в выражении DEFINE поддерживают тот же специальный синтаксис, что и выражения в выражении MEASURES. Подробности описаны в разделе Выражения распознавания строковых паттернов.

Выражения распознавания строковых паттернов#

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

Ссылки на переменные паттерна#

A.totalprice

U.orderdate

orderstatus

Имя столбца с префиксом в виде переменной паттерна ссылается на значения данного столбца во всех строках, сопоставленных с этой переменной, или с любой переменной из подмножества в случае объединенной переменной. Если имя столбца не содержит префикса, оно считается имеющим префикс универсальной переменной паттерна, определенной как объединение всех первичных переменных паттерна. Другими словами, имя столбца без префикса ссылается на все строки текущего совпадения.

В контексте распознавания паттернов запрещено использовать имя таблицы в качестве префикса имени столбца.

Функция classifier#

CLASSIFIER()

CLASSIFIER(A)

CLASSIFIER(U)

Функция classifier возвращает первичную переменную паттерна, связанную со строкой. Тип возвращаемого значения — varchar. Необязательный аргумент — переменная паттерна. Он ограничивает интересующие строки аналогично ссылкам на столбцы с префиксом. Функция classifier особенно полезна с объединенной переменной в качестве аргумента. Она позволяет определить, какая именно переменная из подмножества была сопоставлена.

функция match_number#

MATCH_NUMBER()

Функция match_number возвращает порядковый номер совпадения внутри партиции, начиная с 1. Порядковые номера присваиваются как пустым, так и непустым совпадениям. Тип возвращаемого значения — bigint.

Функции логической навигации#

FIRST(A.totalprice, 2)

В приведенном примере функция first переходит к первой строке, сопоставленной с переменной паттерна A, а затем выполняет поиск вперед, пока не найдет еще два вхождения переменной A в совпадении. Результат — значение столбца totalprice в этой строке.

LAST(A.totalprice, 2)

В приведенном примере функция last переходит к последней строке, сопоставленной с переменной паттерна A, а затем выполняет поиск назад, пока не найдет еще два вхождения переменной A в совпадении. Результат — значение столбца totalprice в этой строке.

Для функций first и last результат равен null, если искомая строка не найдена в совпадении.

Второй аргумент необязателен. Значение по умолчанию — 0, что означает, что по умолчанию эти функции переходят к первой или последней интересующей строке. Если указан, второй аргумент должен быть неотрицательным целым числом.

Функции физической навигации#

PREV(A.totalprice, 2)

В приведенном примере функция prev переходит к последней строке, сопоставленной с переменной паттерна A, а затем перемещается на две строки назад. Результат — значение столбца totalprice в этой строке.

NEXT(A.totalprice, 2)

В приведенном примере функция next переходит к последней строке, сопоставленной с переменной паттерна A, а затем перемещается на две строки вперед. Результат — значение столбца totalprice в этой строке.

С помощью функций prev и next можно перемещаться и получать значения за пределами совпадения. Если навигация выходит за границы партиции, результат равен null.

Второй аргумент необязателен. Значение по умолчанию — 1, что означает, что по умолчанию эти функции переходят к предыдущей или следующей строке. Если указан, второй аргумент должен быть неотрицательным целым числом.

Вложенность функций навигации#

Допускается вложение функций логической навигации в функции физической навигации:

PREV(FIRST(A.totalprice, 3), 2)

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

Функции навигации по паттерну требуют наличия хотя бы одной ссылки на столбец или функции classifier внутри своего первого аргумента. Следующие примеры корректны:

LAST("pattern_variable_" || CLASSIFIER())

NEXT(U.totalprice + 10)

Следующий пример некорректен:

LAST(1)

Также требуется, чтобы все ссылки на столбцы и все вызовы classifier внутри функции навигации по паттерну согласованно ссылались на одни и те же переменные паттерна. Все они должны ссылаться либо на одну и ту же первичную переменную, либо на одну и ту же объединенную переменную, либо на неявную универсальную переменную паттерна. Следующие примеры корректны:

LAST(CLASSIFIER() = 'A' OR totalprice > 10) /* универсальная переменная паттерна */

LAST(CLASSIFIER(U) = 'A' OR U.totalprice > 10) /* переменная паттерна U */

Следующий пример некорректен:

LAST(A.totalprice + B.totalprice)

Агрегатные функции#

В контексте распознавания строковых паттернов допускается использование агрегатных функций. Агрегатные функции вычисляются над всеми строками текущего совпадения или над подмножеством строк на основе сопоставленных переменных паттерна. Поддерживается семантика RUNNING и FINAL, по умолчанию используется running.

Следующее выражение возвращает среднее значение столбца totalprice для всех строк, сопоставленных с переменной паттерна A:

avg(A.totalprice)

Следующее выражение возвращает среднее значение столбца totalprice для всех строк, сопоставленных с переменными паттерна из подмножества U:

avg(U.totalprice)

Следующее выражение возвращает среднее значение столбца totalprice для всех строк совпадения:

avg(totalprice)

Аргументы агрегации#

Если агрегатная функция имеет несколько аргументов, требуется, чтобы все аргументы согласованно ссылались на один и тот же набор строк:

max_by(totalprice, tax) /* агрегация по всем строкам совпадения */

max_by(CLASSIFIER(A), A.tax) /* агрегация по всем строкам, сопоставленным с A */

Следующие примеры некорректны:

max_by(A.totalprice, tax)

max_by(A.totalprice, A.tax + B.tax)

Если аргумент агрегатной функции не содержит ссылок на столбцы или вызовов функции classifier, он не ссылается ни на какую переменную паттерна. В этом случае другие аргументы определяют набор строк для агрегации. Если ни один из аргументов не содержит ссылки на переменную паттерна, неявно используется универсальная переменная паттерна. Это означает, что агрегатная функция применяется ко всем строкам совпадения:

count(1) /* агрегация по всем строкам совпадения */

min_by(1, 2) /* агрегация по всем строкам совпадения */

min_by(1, totalprice) /* агрегация по всем строкам совпадения */

min_by(totalprice, 1) /* агрегация по всем строкам совпадения */

min_by(A.totalprice, 1) /* агрегация по всем строкам, сопоставленным с A */

max_by(1, A.totalprice) /* агрегация по всем строкам, сопоставленным с A */

Вложенность агрегатных функций#

Аргументы агрегатных функций не должны содержать функции навигации по паттерну. Аналогично, агрегатные функции не могут быть вложены в функции навигации по паттерну.

Использование функций classifier и match_number#

В аргументах агрегатных функций допускается использование функций classifier и match_number. Следующее выражение возвращает массив, содержащий все сопоставленные переменные паттерна:

array_agg(CLASSIFIER())

Это особенно полезно в сочетании с вариантом ONE ROW PER MATCH. Оно позволяет получить все компоненты совпадения, сохраняя при этом компактный размер результата.

Агрегация count в контексте строковых паттернов#

Как и другие агрегатные функции в контексте распознавания строковых паттернов, функция count может применяться ко всем строкам совпадения или к строкам, связанным с определенными переменными строкового паттерна:

count(*), count() /* подсчет всех строк совпадения */

count(totalprice) /* подсчет ненулевых значений столбца totalprice
                     во всех строках совпадения */

count(A.totalprice) /* подсчет ненулевых значений столбца totalprice
                       во всех строках, сопоставленных с A */

Функция count в контексте распознавания строковых паттернов поддерживает специальный синтаксис для реализации поведения count(*) над ограниченным набором строк:

count(A.*) /* подсчет строк, сопоставленных с A */

count(U.*) /* подсчет строк, сопоставленных с переменными паттерна из подмножества U */

Семантика RUNNING и FINAL#

При поиске паттерна в последовательности строк строки проверяются одна за другой на соответствие паттерну. На любом шаге известно частичное совпадение, но еще не известно, какие строки будут добавлены в будущем или с какими переменными паттерна они будут сопоставлены. Поэтому при вычислении логического условия в выражении DEFINE для текущей строки «видна» только предшествующая часть совпадения (плюс текущая строка). Это семантика running.

При вычислении выражений в выражении MEASURES совпадение уже завершено. В этом случае можно применить семантику final. При семантике final все совпадение «видно» как с позиции последней строки.

В выражении MEASURES также может применяться семантика running. При выводе информации построчно (как в ALL ROWS PER MATCH) семантика running вычисляет выражения с позиций последовательных строк.

Семантика running и final обозначается ключевыми словами RUNNING и FINAL, предшествующими функции логической навигации first или last, или агрегатной функции:

RUNNING LAST(A.totalprice)

FINAL LAST(A.totalprice)

RUNNING avg(A.totalprice)

FINAL count(A.*)

Семантика running используется по умолчанию в выражениях MEASURES и DEFINE. FINAL может быть указан только в выражении MEASURES.

С вариантом ONE ROW PER MATCH меры вычисляются с позиции последней строки совпадения. Поэтому семантика running и final совпадают.

Вычисление выражений для пустых совпадений и несовпавших строк#

Пустое совпадение возникает, когда строковый паттерн успешно сопоставлен, но ни одна переменная паттерна не была назначена. Следующий паттерн создает пустое совпадение для каждой строки:

PATTERN(())

При вычислении мер для пустого совпадения:

  • все ссылки на столбцы возвращают null

  • все операции навигации возвращают null

  • функция classifier возвращает null

  • функция match_number возвращает порядковый номер совпадения

  • все агрегатные функции вычисляются над пустым набором строк

Как и любое совпадение, пустое совпадение имеет свою начальную строку. Все входные значения, которые должны быть выведены вместе с мерами (как описано в разделе Количество строк на совпадение), берутся из начальной строки.

Несовпавшая строка — это строка, которая не является частью какого-либо непустого совпадения и не является начальной строкой пустого совпадения. С вариантом ALL ROWS PER MATCH WITH UNMATCHED ROWS для нее формируется одна выходная строка. В этой строке все меры равны null. Все входные значения, которые должны быть выведены вместе с мерами (как описано в разделе Количество строк на совпадение), берутся из несовпавшей строки. Использование функции match_number в качестве меры позволяет отличить пустое совпадение от несовпавшей строки.