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

Оконная функция может быть определена в выражении WINDOW или в выражении OVER. В обоих случаях спецификация окна может включать выражения для распознавания паттернов. Синтаксис и семантика распознавания паттернов в окне аналогичны выражению MATCH_RECOGNIZE.

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

Окно с распознаванием паттернов#

Спецификация окна:

(
[ existing_window_name ]
[ PARTITION BY column [, ...] ]
[ ORDER BY column [, ...] ]
[ window_frame ]
)

Фрейм окна:

[ MEASURES measure_definition [, ...] ]
frame_extent
[ AFTER MATCH skip_to ]
[ INITIAL | SEEK ]
[ PATTERN ( row_pattern ) ]
[ SUBSET subset_definition [, ...] ]
[ DEFINE variable_definition [, ...] ]

В общем случае фрейм окна задает frame_extent, определяющий «скользящее окно» строк, обрабатываемых оконной функцией. Он может быть задан в терминах ROWS, RANGE или GROUPS.

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

Фрейм окна с распознаванием паттернов:

[ MEASURES measure_definition [, ...] ]
ROWS BETWEEN CURRENT ROW AND frame_end
[ AFTER MATCH skip_to ]
[ INITIAL | SEEK ]
PATTERN ( row_pattern )
[ SUBSET subset_definition [, ...] ]
DEFINE variable_definition [, ...]

Описание выражений распознавания паттернов#

frame_extent при распознавании паттернов должен быть задан в терминах ROWS. Начало фрейма должно приходиться на CURRENT ROW, что ограничивает допустимые значения frame_extent следующими вариантами:

ROWS BETWEEN CURRENT ROW AND CURRENT ROW

ROWS BETWEEN CURRENT ROW AND <expression> FOLLOWING

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Для каждой обрабатываемой окном входной строки область строк, ограниченная frame_extent, определяет область поиска для распознавания паттернов. В отличие от MATCH_RECOGNIZE, где поиск паттерна может исследовать все строки до конца партиции, и все строки партиции доступны для вычислений, в оконных функциях поиск паттерна не может ни сопоставлять строки, ни извлекать входные значения за пределами фрейма.

Помимо frame_extent, для поиска паттерна необходимы выражения PATTERN и DEFINE.

Выражение PATTERN задает строковый паттерн, являющийся разновидностью регулярного выражения с некоторыми синтаксическими расширениями. Синтаксис строкового паттерна аналогичен синтаксису строкового паттерна в MATCH_RECOGNIZE. Однако якорные паттерны ^ и $ не допускаются в спецификации окна.

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

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

SELECT cust_key, value OVER w, label OVER w
    FROM orders
    WINDOW w AS (
                 PARTITION BY cust_key
                 ORDER BY order_date
                 MEASURES
                        RUNNING LAST(total_price) AS value,
                        CLASSIFIER() AS label
                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                 PATTERN (A B+ C+)
                 DEFINE
                        B AS B.value < PREV (B.value),
                        C AS C.value > PREV (C.value)
                )

На меры, определенные в окне, можно ссылаться в выражении SELECT и в выражении ORDER BY внешнего запроса.

Ключевые слова RUNNING и FINAL допускаются в выражении MEASURES. Они могут предшествовать функции логической навигации FIRST или LAST, или агрегатной функции. Однако они не оказывают эффекта. Все вычисления выполняются с позиции последней строки совпадения, поэтому фактически используется семантика FINAL.

Выражение AFTER MATCH SKIP имеет тот же синтаксис, что и выражение AFTER MATCH SKIP в MATCH_RECOGNIZE.

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

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

SUBSET U = (A, B)

Следующее выражение возвращает значение total_price из последней строки, сопоставленной с A или B:

LAST(U.total_price)

Если необходимо ссылаться на все строки совпадения, не нужно определять SUBSET, содержащий все переменные паттерна. Существует неявная универсальная переменная паттерна, применяемая к любому имени столбца без префикса и к любому вызову CLASSIFIER без аргумента. Следующее выражение возвращает значение total_price из последней совпавшей строки:

LAST(total_price)

Следующий вызов возвращает первичную переменную паттерна первой совпавшей строки:

FIRST(CLASSIFIER())

В окне, в отличие от MATCH_RECOGNIZE, нельзя указать ONE ROW PER MATCH или ALL ROWS PER MATCH. Это связано с тем, что все вызовы над окном, будь то обычные оконные функции или меры, должны соответствовать семантике окна. Вызов над окном должен формировать ровно одну выходную строку для каждой входной строки. Таким образом, режим вывода распознавания паттернов в окне представляет собой комбинацию ONE ROW PER MATCH и WITH UNMATCHED ROWS.

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

Распознавание паттернов в окне обрабатывает входные строки в двух случаях:

при вызове меры строкового паттерна над окном:
```
some_measure OVER w
```
при вызове оконной функции над окном:
```
sum(total_price) OVER w
```

Выходная строка, формируемая для каждой входной строки, состоит из:

всех значений из входной строки
значения вызванной меры или оконной функции, вычисленного с учетом совпадения паттерна, связанного со строкой

Обработка входных данных может быть описана следующей последовательностью шагов:

Разделить входные данные на партиции в соответствии с PARTITION BY
Отсортировать каждую партицию по выражениям ORDER BY
Для каждой строки отсортированной партиции:
Если строка «пропущена» совпадением некоторой предыдущей строки:
Для меры — сформировать выходную строку как для несовпавшей строки

Для оконной функции — вычислить функцию над пустым фреймом и сформировать выходную строку
В противном случае:
Определить границы фрейма

Попытаться найти совпадение строкового паттерна, начиная с текущей строки в пределах фрейма

Если совпадение не найдено и указан SEEK, попытаться найти совпадение, начиная с последующих строк в пределах фрейма

Если совпадение не найдено:

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

Для оконной функции — вычислить функцию над пустым фреймом и сформировать выходную строку

В противном случае:

Для меры — сформировать выходную строку для совпадения

Для оконной функции — вычислить функцию над фреймом, ограниченным последовательностью совпавших строк, и сформировать выходную строку

Вычислить выражение AFTER MATCH SKIP и пометить «пропущенные» строки

Пустые совпадения и несовпавшие строки#

Если с конкретной входной строкой не может быть связано совпадение, строка является несовпавшей. Это происходит, когда для строки не удается найти совпадение. Это также происходит, когда поиск совпадения для строки не выполняется, потому что она пропущена выражением AFTER MATCH SKIP некоторой предшествующей строки. Для несовпавшей строки каждая мера равна null. Каждая оконная функция вычисляется над пустым фреймом.

Пустое совпадение — это успешное совпадение, не включающее ни одной переменной паттерна. Другими словами, пустое совпадение не содержит строк. Если пустое совпадение связано с входной строкой, каждая мера для этой строки вычисляется над пустой последовательностью строк. Все операции навигации и функция CLASSIFIER возвращают null. Каждая оконная функция вычисляется над пустым фреймом.

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

Следующий вызов возвращает 'matched' для каждой совпавшей строки, включая пустые совпадения, и null для каждой несовпавшей строки:

matched OVER (
              ...
              MEASURES 'matched' AS matched
              ...
             )