# Row pattern recognition in window structures#

A window structure can be defined in the `WINDOW`

clause or in the `OVER`

clause of a window operation. In both cases, the window specification can
include row pattern recognition clauses. They are part of the window frame. The
syntax and semantics of row pattern recognition in window are similar to those
of the MATCH_RECOGNIZE clause.

This section explains the details of row pattern recognition in window structures, and highlights the similarities and the differences between both pattern recognition mechanisms.

## Window with 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 [, ...] ]
```

Generally, a window frame specifies the `frame_extent`

, which defines the
“sliding window” of rows to be processed by a window function. It can be
defined in terms of `ROWS`

, `RANGE`

or `GROUPS`

.

A window frame with row pattern recognition involves many other syntactical
components, mandatory or optional, and enforces certain limitations on the
`frame_extent`

.

**Window frame with 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 [, ...]
```

## Description of the pattern recognition clauses#

The `frame_extent`

with row pattern recognition must be defined in terms of
`ROWS`

. The frame start must be at the `CURRENT ROW`

, which limits the
allowed frame extent values to the following:

```
ROWS BETWEEN CURRENT ROW AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND <expression> FOLLOWING
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
```

For every input row processed by the window, the portion of rows enclosed by
the `frame_extent`

limits the search area for row pattern recognition. Unlike
in `MATCH_RECOGNIZE`

, where the pattern search can explore all rows until the
partition end, and all rows of the partition are available for computations, in
window structures the pattern matching can neither match rows nor retrieve
input values outside the frame.

Besides the `frame_extent`

, pattern matching requires the `PATTERN`

and
`DEFINE`

clauses.

The `PATTERN`

clause specifies a row pattern, which is a form of a regular
expression with some syntactical extensions. The row pattern syntax is similar
to the row pattern syntax in MATCH_RECOGNIZE.
However, the anchor patterns `^`

and `$`

are not allowed in a window
specification.

The `DEFINE`

clause defines the row pattern primary variables in terms of
boolean conditions that must be satisfied. It is similar to the
DEFINE clause of MATCH_RECOGNIZE.
The only difference is that the window syntax does not support the
`MATCH_NUMBER`

function.

The `MEASURES`

clause is syntactically similar to the
MEASURES clause of MATCH_RECOGNIZE. The only
limitation is that the `MATCH_NUMBER`

function is not allowed. However, the
semantics of this clause differs between `MATCH_RECOGNIZE`

and window.
While in `MATCH_RECOGNIZE`

every measure produces an output column, the
measures in window should be considered as **definitions** associated with the
window structure. They can be called over the window, in the same manner as
regular 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 defined in a window can be referenced in the `SELECT`

clause and in
the `ORDER BY`

clause of the enclosing query.

The `RUNNING`

and `FINAL`

keywords are allowed in the `MEASURES`

clause.
They can precede a logical navigation function `FIRST`

or `LAST`

. However,
they have no effect. Every computation is performed from the position of the
final row of the match, so the semantics is effectively `FINAL`

.

The `AFTER MATCH SKIP`

clause has the same syntax as the
AFTER MATCH SKIP clause of MATCH_RECOGNIZE.

The `INITIAL`

or `SEEK`

modifier is specific to row pattern recognition in
window. With `INITIAL`

, which is the default, the pattern match for an input
row can only be found starting from that row. With `SEEK`

, if there is no
match starting from the current row, the engine tries to find a match starting
from subsequent rows within the frame. As a result, it is possible to associate
an input row with a match which is detached from that row.

The `SUBSET`

clause is used to define union variables as sets of primary pattern variables. You can
use union variables to refer to a set of rows matched to any primary pattern
variable from the subset:

```
SUBSET U = (A, B)
```

The following expression returns the `total_price`

value from the last row
matched to either `A`

or `B`

:

```
LAST(U.total_price)
```

If you want to refer to all rows of the match, there is no need to define a
`SUBSET`

containing all pattern variables. There is an implicit *universal
pattern variable* applied to any non prefixed column name and any
`CLASSIFIER`

call without an argument. The following expression returns the
`total_price`

value from the last matched row:

```
LAST(total_price)
```

The following call returns the primary pattern variable of the first matched row:

```
FIRST(CLASSIFIER())
```

In window, unlike in `MATCH_RECOGNIZE`

, you cannot specify ```
ONE ROW PER
MATCH
```

or `ALL ROWS PER MATCH`

. This is because all calls over window,
whether they are regular window functions or measures, must comply with the
window semantics. A call over window is supposed to produce exactly one output
row for every input row. And so, the output mode of pattern recognition in
window is a combination of `ONE ROW PER MATCH`

and `WITH UNMATCHED ROWS`

.

## Processing input with row pattern recognition#

Pattern recognition in window processes input rows in two different cases:

upon a row pattern measure call over the window:

some_measure OVER w

upon a window function call over the window:

sum(total_price) OVER w

The output row produced for each input row, consists of:

all values from the input row

the value of the called measure or window function, computed with respect to the pattern match associated with the row

Processing the input can be described as the following sequence of steps:

Partition the input data accordingly to

`PARTITION BY`

Order each partition by the

`ORDER BY`

expressions- For every row of the ordered partition:
- If the row is ‘skipped’ by a match of some previous row:
For a measure, produce a one-row output as for an unmatched row

For a window function, evaluate the function over an empty frame and produce a one-row output

- Otherwise:
Determine the frame extent

Try match the row pattern starting from the current row within the frame extent

If no match is found, and

`SEEK`

is specified, try to find a match starting from subsequent rows within the frame extent

- If no match is found:
For a measure, produce a one-row output for an unmatched row

For a window function, evaluate the function over an empty frame and produce a one-row output

- Otherwise:
For a measure, produce a one-row output for the match

For a window function, evaluate the function over a frame limited to the matched rows sequence and produce a one-row output

Evaluate the

`AFTER MATCH SKIP`

clause, and mark the ‘skipped’ rows

## Empty matches and unmatched rows#

If no match can be associated with a particular input row, the row is
*unmatched*. This happens when no match can be found for the row. This also
happens when no match is attempted for the row, because it is skipped by the
`AFTER MATCH SKIP`

clause of some preceding row. For an unmatched row,
every row pattern measure is `null`

. Every window function is evaluated over
an empty frame.

An *empty match* is a successful match which does not involve any pattern
variables. In other words, an empty match does not contain any rows. If an
empty match is associated with an input row, every row pattern measure for that
row is evaluated over an empty sequence of rows. All navigation operations and
the `CLASSIFIER`

function return `null`

. Every window function is evaluated
over an empty frame.

In most cases, the results for empty matches and unmatched rows are the same. A constant measure can be helpful to distinguish between them:

The following call returns `'matched'`

for every matched row, including empty
matches, and `null`

for every unmatched row:

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

## Limitations#

Standard SQL syntax allows you to use aggregate functions inside pattern recognition expressions. Trino does not support it.