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. Оно используется для обнаружения patterns в наборе строк. Интересующие patterns задаются с помощью синтаксиса row pattern на основе регулярных выражений. Входом для pattern matching служит таблица, представление или подзапрос. Для каждого обнаруженного match возвращается одна или несколько строк с запрошенной информацией о match.

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

  • в торговых приложениях: отслеживание трендов или выявление customers с определенными behavioral patterns

  • в shipping-приложениях: отслеживание посылок по всем допустимым маршрутам

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

Пример#

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

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 объясняются на основе этого примерного запроса.

Partitioning и ordering#

PARTITION BY custkey

Предложение PARTITION BY позволяет разбить входную таблицу на отдельные sections, которые независимо обрабатываются для pattern matching. Без объявления partition используется вся входная таблица. Такое поведение аналогично семантике предложения PARTITION BY в window specification. В примере таблица orders разбивается на partitions по значению custkey, так что pattern matching выполняется для всех заказов конкретного customer независимо от заказов других customers.

ORDER BY orderdate

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

Row pattern measures#

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

MEASURES measure_expression AS measure_name [, ...]

Measure expression — это scalar expression, значение которого вычисляется на основе match. В примере указаны три row pattern measures:

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

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

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

Measure expressions могут ссылаться на столбцы входной таблицы. Они также допускают специальный синтаксис, позволяющий объединять входную информацию с деталями match (см. Выражения row pattern recognition).

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

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

Rows per match#

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

ONE ROW PER MATCH

and

ALL ROWS PER MATCH

ONE ROW PER MATCH — вариант по умолчанию. Для каждого match создается одна выходная строка. Вывод состоит из столбцов PARTITION BY и measures. Вывод также создается для empty matches на основе их начальных строк. Строки, которые являются unmatched, то есть не входят ни в один non-empty match и не являются начальной строкой empty match, в вывод не включаются.

Для ALL ROWS PER MATCH каждая строка match создает выходную строку, если она не исключена из вывода с помощью Синтаксис exclusion. Вывод состоит из столбцов PARTITION BY, столбцов ORDER BY, measures и остальных столбцов входной таблицы. По умолчанию empty matches показываются, а unmatched rows пропускаются, как и при варианте ONE ROW PER MATCH. Однако это поведение можно изменить модификаторами:

ALL ROWS PER MATCH SHOW EMPTY MATCHES

показывает empty matches и пропускает unmatched rows, как вариант по умолчанию.

ALL ROWS PER MATCH OMIT EMPTY MATCHES

исключает empty matches из вывода.

ALL ROWS PER MATCH WITH UNMATCHED ROWS

показывает empty matches и создает дополнительную выходную строку для каждой unmatched row.

Для вычисления row pattern measures для empty matches и unmatched rows действуют специальные правила. Они описаны в Вычисление выражений в empty matches и unmatched rows.

Unmatched rows могут возникать только тогда, когда pattern не допускает empty match. В противном случае они считаются начальными строками empty matches. Вариант ALL ROWS PER MATCH WITH UNMATCHED ROWS рекомендуется, когда pattern recognition должен пропускать через себя все входные строки и нет уверенности, допускает ли pattern empty match.

After match skip#

Предложение AFTER MATCH SKIP указывает, где возобновляется pattern matching после обнаружения non-empty match.

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

AFTER MATCH SKIP PAST LAST ROW

При этом варианте pattern matching начинается со строки после последней строки match. Перекрывающиеся matches не обнаруживаются.

При следующем варианте pattern matching начинается со второй строки match:

AFTER MATCH SKIP TO NEXT ROW

В примере, если обнаружена V-образная форма, дальнейшие перекрывающиеся matches находятся начиная с последовательных строк на нисходящем склоне “V”. Переход к следующей строке является поведением по умолчанию после обнаружения empty match или unmatched row.

Следующие варианты AFTER MATCH SKIP позволяют возобновлять pattern matching на основе компонентов pattern. Pattern matching начинается с последней (по умолчанию) или первой строки, сопоставленной с определенной row pattern variable. Это может быть primary pattern variable (они описаны в Синтаксис row pattern) или union variable:

AFTER MATCH SKIP TO [ FIRST | LAST ] pattern_variable

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

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

Синтаксис row pattern#

Row pattern — это форма регулярного выражения с некоторыми синтаксическими расширениями, специфичными для row pattern recognition. Он задается в предложении PATTERN:

PATTERN ( row_pattern )

Базовый элемент row pattern — primary pattern variable. Подобно тому как pattern matching в строках символов ищет символы, pattern matching в последовательностях строк ищет строки, которые можно “пометить” определенными primary pattern variables. Primary pattern variable имеет форму идентификатора и определяется логическим условием. Это условие определяет, может ли конкретная входная строка быть сопоставлена с этой variable и участвовать в match.

В примере PATTERN (A B+ C+ D+) есть четыре primary pattern variables: A, B, C и D.

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

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

A B+ C+ D+

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

Альтернатива#

A | B | C

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

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

PERMUTE(A, B, C)

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

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

(A B C)

Anchor начала partition#

^

Anchor конца partition#

$

Empty pattern#

()

Синтаксис exclusion#

{- row_pattern -}

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

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

Указание pattern exclusions не влияет на вычисление выражений в предложениях MEASURES и DEFINE. Exclusions также не влияют на pattern matching. Они имеют ту же семантику, что и обычная группировка с помощью скобок.

Запрещено указывать pattern exclusions с вариантом ALL ROWS PER MATCH WITH UNMATCHED ROWS.

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

Pattern quantifiers позволяют указать требуемое число повторений sub-pattern в match. Они добавляются после соответствующего компонента pattern:

(A | B)*

Доступны следующие row pattern quantifiers:

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

*

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

+

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

?

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

{n}

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

{m, n}

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

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

Row pattern union variables#

Как объяснялось в Синтаксис row pattern, primary pattern variables являются базовыми элементами row pattern. Помимо primary pattern variables, можно определять union variables. Они вводятся в предложении SUBSET:

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

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

Для pattern PATTERN((A | B){5} C+) заранее нельзя определить, содержит ли match какие-либо A или B. Union variable можно использовать для доступа к последней строке, сопоставленной с 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 отсутствует в match. AFTER MATCH SKIP TO LAST U не завершается ошибкой.

Определения row pattern variables#

Предложение DEFINE — место, где определяются primary variables row pattern. Каждая variable связывается с логическим условием:

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

Во время pattern matching, когда некоторая variable рассматривается для следующего шага match, логическое условие вычисляется в контексте текущего match. Если результат равен true, текущая строка, “помеченная” этой variable, становится частью match.

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

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

Не требуется, чтобы каждая primary variable имела определение в предложении DEFINE. Variables, не упомянутые в предложении DEFINE, неявно связываются с условием true; это означает, что они могут быть сопоставлены с любой строкой.

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

Выражения row pattern recognition#

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

Ссылки на pattern variables#

A.totalprice

U.orderdate

orderstatus

Имя столбца с префиксом pattern variable ссылается на значения этого столбца во всех строках, сопоставленных с этой variable, или с любой variable из subset в случае union variable. Если имя столбца не имеет префикса, считается, что оно имеет префикс universal pattern variable, определенной как объединение всех primary pattern variables. Другими словами, имя столбца без префикса ссылается на все строки текущего match.

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

Функция classifier#

CLASSIFIER()

CLASSIFIER(A)

CLASSIFIER(U)

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

Функция match_number#

MATCH_NUMBER()

Функция match_number возвращает порядковый номер match внутри partition, начиная с 1. Empty matches получают порядковые номера так же, как non-empty matches. Возвращаемый тип — bigint.

Logical navigation functions#

FIRST(A.totalprice, 2)

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

LAST(A.totalprice, 2)

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

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

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

Physical navigation functions#

PREV(A.totalprice, 2)

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

NEXT(A.totalprice, 2)

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

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

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

Aggregate functions#

В контексте row pattern recognition разрешено использовать aggregate functions. Aggregate functions вычисляются по всем строкам текущего match или по subset строк на основе сопоставленных pattern variables. Поддерживается семантика running и final; по умолчанию используется running.

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

avg(A.totalprice)

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

avg(U.totalprice)

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

avg(totalprice)

Аргументы aggregation#

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

max_by(totalprice, tax) /* aggregate over all rows of the match */

max_by(CLASSIFIER(A), A.tax) /* aggregate over all rows matched to A */

Это некорректно:

max_by(A.totalprice, tax)

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

Если аргумент aggregate не содержит ссылки на столбец или функции classifier, он не ссылается ни на одну pattern variable. В таком случае другие аргументы aggregate определяют набор строк для aggregation. Если ни один из аргументов не содержит ссылки на pattern variable, подразумевается universal row pattern variable. Это означает, что aggregate function применяется ко всем строкам match:

count(1) /* aggregate over all rows of the match */

min_by(1, 2) /* aggregate over all rows of the match */

min_by(1, totalprice) /* aggregate over all rows of the match */

min_by(totalprice, 1) /* aggregate over all rows of the match */

min_by(A.totalprice, 1) /* aggregate over all rows matched to A */

max_by(1, A.totalprice) /* aggregate over all rows matched to A */

Вложение aggregate functions#

Аргументы aggregate function не должны содержать pattern navigation functions. Аналогично, aggregate functions нельзя вкладывать в pattern navigation functions.

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

Функции classifier и match_number разрешено использовать в аргументах aggregate function. Следующее выражение возвращает массив, содержащий все сопоставленные pattern variables:

array_agg(CLASSIFIER())

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

Aggregation count для row pattern#

Как и другие aggregate functions в контексте row pattern recognition, функцию count можно применять ко всем строкам match или к строкам, связанным с определенными row pattern variables:

count(*), count() /* count all rows of the match */

count(totalprice) /* count non-null values of the totalprice column
                     in all rows of the match */

count(A.totalprice) /* count non-null values of the totalprice column
                       in all rows matched to A */

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

count(A.*) /* count rows matched to A */

count(U.*) /* count rows matched to pattern variables from subset U */

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

Во время pattern matching в последовательности строк каждая строка последовательно проверяется, чтобы определить, подходит ли она под pattern. На любом шаге известен partial match, но еще неизвестно, какие строки будут добавлены в будущем и с какими pattern variables они будут сопоставлены. Поэтому при вычислении логического условия в предложении DEFINE для текущей строки “видна” только предыдущая часть match плюс текущая строка. Это семантика running.

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

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

Семантика running и final обозначается ключевыми словами RUNNING и FINAL, которые предшествуют logical navigation function first или last либо aggregate function:

RUNNING LAST(A.totalprice)

FINAL LAST(A.totalprice)

RUNNING avg(A.totalprice)

FINAL count(A.*)

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

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

Вычисление выражений в empty matches и unmatched rows#

Empty match возникает, когда row pattern успешно сопоставлен, но pattern variables не назначены. Следующий pattern создает empty match для каждой строки:

PATTERN(())

При вычислении row pattern measures для empty match:

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

  • все navigation operations возвращают null

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

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

  • все aggregate functions вычисляются по пустому набору строк

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

Unmatched row — это строка, которая не является частью какого-либо non-empty match и не является начальной строкой empty match. При варианте ALL ROWS PER MATCH WITH UNMATCHED ROWS создается одна выходная строка. В этой строке все row pattern measures равны null. Все входные значения, которые должны выводиться вместе с measures, как описано в Rows per match, являются значениями из unmatched row. Использование функции match_number в качестве measure помогает различать empty match и unmatched row.