Распознавание row pattern в window structures#

Window structure может быть определена в предложении WINDOW или в предложении OVER window operation. В обоих случаях window specification может включать предложения row pattern recognition. Они являются частью window frame. Синтаксис и семантика row pattern recognition в window похожи на синтаксис и семантику предложения MATCH_RECOGNIZE.

В этом разделе описаны детали row pattern recognition в window structures, а также сходства и различия между двумя механизмами pattern recognition.

Window с row pattern recognition#

Window specification:

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

Window frame:

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

В общем случае window frame задает frame_extent, который определяет “скользящее окно” строк, обрабатываемых window function. Он может быть задан через ROWS, RANGE или GROUPS.

Window frame с row pattern recognition включает много других синтаксических компонентов, обязательных или необязательных, и накладывает определенные ограничения на frame_extent.

Window frame с row pattern recognition:

[ 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 [, ...]

Описание предложений pattern recognition#

frame_extent с row pattern recognition должен быть задан через ROWS. Начало frame должно находиться в 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

Для каждой входной строки, обрабатываемой window, часть строк, ограниченная frame_extent, задает область поиска для row pattern recognition. В отличие от MATCH_RECOGNIZE, где поиск pattern может просматривать все строки до конца partition и все строки partition доступны для вычислений, в window structures pattern matching не может ни сопоставлять строки, ни получать входные значения за пределами frame.

Помимо frame_extent, для pattern matching требуются предложения PATTERN и DEFINE.

Предложение PATTERN задает row pattern, который является формой регулярного выражения с некоторыми синтаксическими расширениями. Синтаксис row pattern похож на синтаксис row pattern в MATCH_RECOGNIZE. Однако anchor patterns ^ и $ в window specification не допускаются.

Предложение DEFINE определяет primary variables row pattern через логические условия, которые должны выполняться. Оно похоже на предложение DEFINE в MATCH_RECOGNIZE. Единственное различие в том, что window syntax не поддерживает функцию MATCH_NUMBER.

Предложение MEASURES синтаксически похоже на предложение MEASURES в MATCH_RECOGNIZE. Единственное ограничение — функция MATCH_NUMBER не допускается. Однако семантика этого предложения отличается для MATCH_RECOGNIZE и window. В MATCH_RECOGNIZE каждая measure создает выходной столбец, а measures в window следует рассматривать как definitions, связанные с window structure. Их можно вызывать поверх window так же, как обычные window functions:

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)
                )

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

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

Предложение AFTER MATCH SKIP имеет тот же синтаксис, что и предложение AFTER MATCH SKIP в MATCH_RECOGNIZE.

Модификатор INITIAL или SEEK специфичен для row pattern recognition в window. При INITIAL, который используется по умолчанию, pattern match для входной строки может быть найден только начиная с этой строки. При SEEK, если match, начинающийся с текущей строки, отсутствует, engine пытается найти match, начинающийся с последующих строк внутри frame. В результате входную строку можно связать с match, который отделен от этой строки.

Предложение SUBSET используется для определения union variables как наборов primary pattern variables. Union variables можно использовать, чтобы ссылаться на набор строк, сопоставленных с любой primary pattern variable из subset:

SUBSET U = (A, B)

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

LAST(U.total_price)

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

LAST(total_price)

Следующий вызов возвращает primary pattern variable первой сопоставленной строки:

FIRST(CLASSIFIER())

В window, в отличие от MATCH_RECOGNIZE, нельзя указать ONE ROW PER MATCH или ALL ROWS PER MATCH. Это связано с тем, что все вызовы поверх window, как обычные window functions, так и measures, должны соответствовать window semantics. Предполагается, что вызов поверх window создает ровно одну выходную строку для каждой входной строки. Поэтому режим вывода pattern recognition в window представляет собой сочетание ONE ROW PER MATCH и WITH UNMATCHED ROWS.

Обработка входных данных с row pattern recognition#

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

  • при вызове row pattern measure поверх window:

    some_measure OVER w

  • при вызове window function поверх window:

    sum(total_price) OVER w

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

  • всех значений из входной строки

  • значения вызванной measure или window function, вычисленного относительно pattern match, связанного со строкой

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

  • Разделить входные данные на partitions в соответствии с PARTITION BY

  • Упорядочить каждую partition по выражениям ORDER BY

  • Для каждой строки упорядоченной partition:
    Если строка “пропущена” match некоторой предыдущей строки:

    • Для measure создать однострочный вывод как для unmatched row

    • Для window function вычислить функцию по пустому frame и создать однострочный вывод

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

    • Определить frame extent

    • Попытаться сопоставить row pattern, начиная с текущей строки внутри frame extent

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

    Если match не найден:

    • Для measure создать однострочный вывод для unmatched row

    • Для window function вычислить функцию по пустому frame и создать однострочный вывод

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

    • Для measure создать однострочный вывод для match

    • Для window function вычислить функцию по frame, ограниченному последовательностью сопоставленных строк, и создать однострочный вывод

    • Вычислить предложение AFTER MATCH SKIP и отметить “пропущенные” строки

Empty matches и unmatched rows#

Если с конкретной входной строкой нельзя связать match, строка считается unmatched. Это происходит, когда для строки не удается найти match. Также это происходит, когда для строки не выполняется попытка поиска match, поскольку она пропущена предложением AFTER MATCH SKIP некоторой предыдущей строки. Для unmatched row каждая row pattern measure имеет значение null. Каждая window function вычисляется по пустому frame.

Empty match — это успешный match, который не включает pattern variables. Другими словами, empty match не содержит строк. Если empty match связан с входной строкой, каждая row pattern measure для этой строки вычисляется по пустой последовательности строк. Все navigation operations и функция CLASSIFIER возвращают null. Каждая window function вычисляется по пустому frame.

В большинстве случаев результаты для empty matches и unmatched rows одинаковы. Для их различения может быть полезна константная measure:

Следующий вызов возвращает 'matched' для каждой matched row, включая empty matches, и null для каждой unmatched row:

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