MATCH_RECOGNIZE clause

Applies to: Databricks SQL Databricks Runtime 19.0 and above

Finds and filters patterns in the rows of the preceding table_reference. MATCH_RECOGNIZE partitions the input, orders rows within each partition, matches a row pattern against that ordered sequence, and returns summary or per-row results depending on the rows-per-match mode.

Typical uses include detecting runs of consecutive values, V-shaped or W-shaped price movements, and sessionizing event streams.

Syntax

MATCH_RECOGNIZE (
  [ PARTITION BY partition [, ...] ]
  [ ORDER BY order_by ]
  [ MEASURES measures ]
  [ row_pattern_rows_per_match ]
  [ AFTER MATCH row_pattern_skip_to ]
  PATTERN ( row_pattern )
  DEFINE row_pattern_definition_list )

measures
  MEASURES { measureExpr AS measureName } [, ...]

row_pattern_rows_per_match
  { ONE ROW PER MATCH
  | ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ] }

row_pattern_skip_to
  SKIP PAST LAST ROW

Parameters

• PARTITION BY partition [, ...]

  One or more expressions that define the groups of rows on which pattern matching runs. If you omit PARTITION BY, the partition contains all rows.

  PARTITION BY accepts column references only. If you specify another expression, Azure Databricks raises MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN.

• ORDER BY order_by

  Specifies the order of rows within each partition. Pattern matching and navigation functions use this order.

• MEASURES

  Optionally defines the measure columns returned for each pattern match.

• row_pattern_rows_per_match

  Controls how many rows are returned per match. The default is ONE ROW PER MATCH.

  • ONE ROW PER MATCH

    Returns one row per match. The result contains partition columns and measure columns only.

  • ALL ROWS PER MATCH [ SHOW EMPTY MATCHES ]

    Returns one row for each row that participates in a match. Each output row includes the corresponding input columns from the table_reference, PARTITION BY columns, and MEASURES columns computed for that match.

    SHOW EMPTY MATCHES is accepted under ALL ROWS PER MATCH, and is the default when you omit the empty-match-handling sub-clause. This release does not produce empty matches, so the keyword has no observable effect on the result.

• AFTER MATCH row_pattern_skip_to

  Specifies which row to continue from after a match is found. This release supports SKIP PAST LAST ROW only. Continue with the row immediately following the last row of the current match. This is the default when you omit the AFTER MATCH clause.

• PATTERN ( row_pattern )

  Specifies the pattern to match.

• DEFINE row_pattern_definition_list

  Defines the boolean variables referenced in the PATTERN and MEASURES clauses.

Result

The result depends on the rows-per-match mode:

• ONE ROW PER MATCH

  Returns PARTITION BY columns followed by MEASURES columns.

• ALL ROWS PER MATCH

  Returns one row for each row that participates in a match. Each output row includes the corresponding input columns from the table_reference, PARTITION BY columns, and MEASURES columns computed for that match.

Common error conditions

• MATCH_RECOGNIZE_EMPTY_MEASURES
• MATCH_RECOGNIZE_FUNCTION_OUTSIDE_MATCH_RECOGNIZE
• MATCH_RECOGNIZE_MEASURES_MUST_BE_ALIASED
• MATCH_RECOGNIZE_PARTITION_BY_MUST_BE_COLUMN

Examples

Each query uses a stock_ticker(symbol, tstamp, price) table, except the last example which uses page_views(user_id, event_time).

Example 1: Consecutive rising run

Find every maximal run of consecutive price increases per symbol. The variable strt has no DEFINE entry, so it matches any row and anchors the run. up+ extends the match across one or more consecutive increases. PREV(price) reads the price of the immediately preceding row in ORDER BY order. ONE ROW PER MATCH emits a single summary row per run.

> CREATE OR REPLACE TEMP VIEW stock_ticker AS
  SELECT * FROM VALUES
    ('AAPL', TIMESTAMP '2024-01-01 09:30:00', 100.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:31:00', 102.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:32:00', 105.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:33:00', 104.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:34:00', 106.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:35:00', 108.0)
  AS t(symbol, tstamp, price);

> SELECT symbol, start_tstamp, end_tstamp, run_length
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp) AS start_tstamp,
             LAST(tstamp)  AS end_tstamp,
             COUNT(*)      AS run_length
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt up+ )
    DEFINE up AS price > PREV(price) ) AS T;
 symbol  start_tstamp           end_tstamp             run_length
 AAPL    2024-01-01 09:30:00    2024-01-01 09:32:00    3
 AAPL    2024-01-01 09:33:00    2024-01-01 09:35:00    3

Example 2: V-shape (dip and recover)

Detect a price that first falls, then rises. down+ matches the falling leg and up+ the recovery. LAST(down.tstamp) picks the last row classified as down, which is the trough of the V. A variable-qualified reference such as down.tstamp lets a MEASURES expression read rows matched by a specific pattern variable.

> SELECT symbol, start_tstamp, bottom_tstamp, end_tstamp
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp)     AS start_tstamp,
             LAST(down.tstamp) AS bottom_tstamp,
             LAST(tstamp)      AS end_tstamp
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt down+ up+ )
    DEFINE down AS price < PREV(price),
           up   AS price > PREV(price) ) AS T;
 symbol  start_tstamp           bottom_tstamp          end_tstamp
 AAPL    2024-01-01 09:32:00    2024-01-01 09:33:00    2024-01-01 09:35:00

Example 3: Double-bottom (W-shape)

Detect two dips separated by a partial recovery. The pattern spells out four legs (down1+ up1+ down2+ up2+), and distinct variable names let you measure or filter each trough independently. MATCH_NUMBER() numbers each W found within a partition.

> CREATE OR REPLACE TEMP VIEW stock_ticker AS
  SELECT * FROM VALUES
    ('AAPL', TIMESTAMP '2024-01-01 09:30:00', 100.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:31:00', 96.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:32:00', 92.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:33:00', 98.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:34:00', 101.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:35:00', 95.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:36:00', 90.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:37:00', 99.0),
    ('AAPL', TIMESTAMP '2024-01-01 09:38:00', 104.0)
  AS t(symbol, tstamp, price);

> SELECT symbol, start_tstamp, end_tstamp, w_no
  FROM stock_ticker
  MATCH_RECOGNIZE (
    PARTITION BY symbol
    ORDER BY tstamp
    MEASURES FIRST(tstamp)  AS start_tstamp,
             LAST(tstamp)   AS end_tstamp,
             MATCH_NUMBER() AS w_no
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt down1+ up1+ down2+ up2+ )
    DEFINE down1 AS price < PREV(price),
           up1   AS price > PREV(price),
           down2 AS price < PREV(price),
           up2   AS price > PREV(price) ) AS T;
 symbol  start_tstamp           end_tstamp             w_no
 AAPL    2024-01-01 09:30:00    2024-01-01 09:38:00    1

Example 4: Sessionization

Collapse a user's event stream into sessions, where a gap of more than 30 minutes between consecutive events starts a new session. strt opens a session on any row. same_session* absorbs every following event that occurs within 30 minutes of its predecessor. When a gap exceeds the threshold, the match ends, AFTER MATCH SKIP PAST LAST ROW resumes at the next event, and a fresh session (a new MATCH_NUMBER()) begins. The * quantifier makes a lone event a valid one-row session.

> CREATE OR REPLACE TEMP VIEW page_views AS
  SELECT * FROM VALUES
    (1, TIMESTAMP '2024-01-01 09:00:00'),
    (1, TIMESTAMP '2024-01-01 09:15:00'),
    (1, TIMESTAMP '2024-01-01 10:00:00'),
    (1, TIMESTAMP '2024-01-01 10:10:00')
  AS t(user_id, event_time);

> SELECT user_id, session_no, session_start, session_end, event_count
  FROM page_views
  MATCH_RECOGNIZE (
    PARTITION BY user_id
    ORDER BY event_time
    MEASURES MATCH_NUMBER()    AS session_no,
             FIRST(event_time) AS session_start,
             LAST(event_time)  AS session_end,
             COUNT(*)          AS event_count
    ONE ROW PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ( strt same_session* )
    DEFINE same_session AS event_time <= PREV(event_time) + INTERVAL 30 MINUTE ) AS T;
 user_id  session_no  session_start          session_end            event_count
 1        1           2024-01-01 09:00:00    2024-01-01 09:15:00    2
 1        2           2024-01-01 10:00:00    2024-01-01 10:10:00    2

Related articles

• DEFINE clause for MATCH_RECOGNIZE
• MEASURES clause for MATCH_RECOGNIZE
• PATTERN clause for MATCH_RECOGNIZE
• MATCH_RECOGNIZE functions
• table reference