MATCH_RECOGNIZE

Overview

MATCH_RECOGNIZE detects sequences of rows that match a regular-expression-like pattern. Krishiv implements it via the built-in PatternMatcher and intercepts the statement before DataFusion parsing (DataFusion does not parse MATCH_RECOGNIZE natively).

Syntax

SELECT *
FROM <source_table>
MATCH_RECOGNIZE (
  PARTITION BY <column> [, ...]
  ORDER BY <column> [ASC|DESC]
  MEASURES
    <pattern_var>.<column> AS <alias> [, ...]
  ONE ROW PER MATCH
  PATTERN (<pattern>)
  DEFINE
    <pattern_var> AS <condition> [, ...]
)

Example — Detect Price Rise followed by Drop

SELECT * FROM ticks
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY ts
  MEASURES
    FIRST(UP.price) AS start_price,
    LAST(DOWN.price) AS end_price
  ONE ROW PER MATCH
  PATTERN (UP+ DOWN+)
  DEFINE
    UP   AS price > PREV(price),
    DOWN AS price < PREV(price)
)

Streaming Sources

When the source table is an unbounded streaming source, Krishiv materialises a bounded window of recent events (default 100 000 rows) and runs the pattern matcher over that window. Set KRISHIV_MATCH_RECOGNIZE_STREAMING_LIMIT to override. Results cover only the collected window.

Supported Clauses

Clause	Support
PARTITION BY	Available — matches run per partition key.
ORDER BY	Available — rows within a partition are sorted before matching.
MEASURES	Available — extract columns from matched pattern variables.
ONE ROW PER MATCH	Available (default).
ALL ROWS PER MATCH	Not yet supported.
PATTERN	Available — quantifiers +, *, ?, concatenation, alternation.
DEFINE	Available — Boolean conditions referencing previous row values via PREV() and FIRST()/LAST().