Advanced

Pattern Matching With MATCH_RECOGNIZE

`MATCH_RECOGNIZE` detects ordered event patterns over a timestamped stream.

Guide

`MATCH_RECOGNIZE` detects ordered event patterns over a timestamped stream.

Basic tested query:

sql
SELECT mr.start_price, mr.end_price INTO output
FROM input TIMESTAMP BY ts
MATCH_RECOGNIZE (
    LIMIT Duration(minute, 1)
    MEASURES
        A.price AS start_price,
        B.price AS end_price
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (A B)
    DEFINE
        A AS A.kind = 'A',
        B AS B.kind = 'B'
) AS mr

Tested input:

jsonl
{"ts":1,"kind":"A","price":10}
{"ts":2,"kind":"B","price":12}
{"ts":3,"kind":"A","price":20}
{"ts":4,"kind":"B","price":25}

Tested output:

jsonl
{"start_price":10,"end_price":12}
{"start_price":20,"end_price":25}

The output event timestamp is the accepting event timestamp. In the test above, intermediate output arrival times are `2` and `4`.

MATCH_RECOGNIZE Grammar

text
FROM <input> TIMESTAMP BY <field>
MATCH_RECOGNIZE (
    [PARTITION BY <field-path>[, ...]]
    LIMIT Duration(<unit>, <value>)
    MEASURES
        <measure-expression> AS <alias>[, ...]
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (<pattern-expression>)
    DEFINE
        <variable> AS <condition>[, ...]
) AS <alias>

Supported and tested constraints:

  • `TIMESTAMP BY` is required.
  • `LIMIT Duration(...)` is required.
  • An alias after `) AS` is required.
  • `ONE ROW PER MATCH` is the supported row mode.
  • `AFTER MATCH SKIP TO NEXT ROW` is the supported skip mode.
  • Every primary pattern variable must have a `DEFINE`.
  • Every `DEFINE` variable must appear in the `PATTERN`.
  • Outer `SELECT` can reference measure names directly or through the match alias.
  • Aggregate expressions in the outer `SELECT` are not supported for `MATCH_RECOGNIZE`.
  • `MATCH_RECOGNIZE` cannot be combined with `WINDOW BY`, `GROUP BY`, or `HAVING`.

Partitioned Matching

Partitions keep independent match state per key.

sql
SELECT device, first_value, last_value INTO output
FROM input TIMESTAMP BY ts
MATCH_RECOGNIZE (
    PARTITION BY device
    LIMIT Duration(minute, 1)
    MEASURES
        FIRST(A.value) AS first_value,
        LAST(B.value) AS last_value
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (A B)
    DEFINE
        A AS A.kind = 'start',
        B AS B.kind = 'end'
) AS mr

With interleaved `X` and `Y` devices, the tests emit one match per device.

Measures

Tested measure expressions include:

sql
A.price AS start_price
B.price AS end_price
FIRST(A.value) AS first_value
LAST(B.value) AS last_value

Measures require aliases.

PREV Navigation

`PREV` compares the current variable binding to an earlier binding in the same pattern variable:

sql
SELECT run_id, last_b INTO output
FROM input TIMESTAMP BY ts
MATCH_RECOGNIZE (
    LIMIT Duration(minute, 1)
    MEASURES
        A.run_id AS run_id,
        LAST(B.value) AS last_b
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (A B{2})
    DEFINE
        A AS A.kind = 'A',
        B AS B.kind = 'B' AND B.value > PREV(B.value)
) AS mr

In the tests, only the run whose `B` values increase from `10` to `20` matches.

Quantifiers

The tests cover these pattern quantifiers:

text
B*      zero or more
B+      one or more
B?      zero or one
B{2,}   at least two
B{1,3}  one to three
B{,2}   zero to two
B{3}    exactly three

Example with zero-or-more:

sql
SELECT run_id, c_value INTO output
FROM input TIMESTAMP BY ts
MATCH_RECOGNIZE (
    PARTITION BY run_id
    LIMIT Duration(minute, 1)
    MEASURES
        C.value AS c_value
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (A B* C)
    DEFINE
        A AS A.kind = 'A',
        B AS B.kind = 'B',
        C AS C.kind = 'C'
) AS mr

Example with one-or-more:

sql
SELECT run_id, c_value INTO output
FROM input TIMESTAMP BY ts
MATCH_RECOGNIZE (
    PARTITION BY run_id
    LIMIT Duration(minute, 1)
    MEASURES
        C.value AS c_value
    ONE ROW PER MATCH
    AFTER MATCH SKIP TO NEXT ROW
    PATTERN (A B+ C)
    DEFINE
        A AS A.kind = 'A',
        B AS B.kind = 'B',
        C AS C.kind = 'C'
) AS mr

Alternation and Complex Conditions

The parser supports pattern alternation and boolean conditions in `DEFINE`, with the same predicate family used by `WHERE` where covered by tests.

The tested syntax suite includes:

sql
PATTERN (A B* C+ D? E{2,} F{1,3} G{,2} H{3})
PreviousWITH Scripts and Multiple OutputsNextWindow Statistics With WINDOW BY ... COMPUTE