Set-Based Row Pattern Matching

In this lesson, you will learn how to identify specific sequences of events within your data by treating rows as a connected chain rather than isolated records.

Moving Beyond Single Rows

Up until now, you have used window functions to look at a single row’s relationship to its neighbors. However, "Row Pattern Matching" allows us to define a logical "shape" or "sequence" across multiple rows—such as identifying a "Dip-Recovery-Peak" pattern in stock prices or a "Login-Attempt-Success" sequence in security logs.

Instead of writing complex self-joins or nested subqueries to compare row $N$ to row $N+1$, we use pattern matching to define a set-based rule that scans the data.

Defining the Pattern

Imagine we have a table of sensor_logs and we want to flag any instance where a device goes from OFF to WARMING to RUNNING. We define these states as symbols in our pattern:

SELECT *
FROM sensor_logs
MATCH_RECOGNIZE (
    ORDER BY timestamp
    MEASURES 
        STRT.timestamp AS start_time,
        RUN.timestamp AS end_time
    PATTERN (STRT WARM+ RUN)
    DEFINE
        STRT AS state = 'OFF',
        WARM AS state = 'WARMING',
        RUN  AS state = 'RUNNING'
) AS pattern_result;

How it Works

1. ORDER BY: Pattern matching requires a strict sequence, so we provide an anchor for the "flow" of time.
2. PATTERN: This uses a syntax similar to Regular Expressions. Here, (STRT WARM+ RUN) tells the database to look for an 'OFF' row, followed by one or more 'WARMING' rows, ending with a 'RUNNING' row.
3. DEFINE: This maps our logical labels (STRT, WARM, RUN) to the actual criteria in our data columns.
4. MEASURES: This extracts the specific data points from the start and end of the identified pattern to show us exactly when the sequence occurred.

By using this approach, we move away from procedural "if-then" logic and instead perform a declarative scan of the data set, letting the database engine handle the heavy lifting of identifying matches across the entire ordered relation.

Key Takeaway: Set-based row pattern matching allows you to define complex behavioral sequences as a single declarative rule, turning time-series data into actionable insights.