Identifying Sequences of Rows That Match a Pattern¶

Setting Up the Data For This Example¶

To set up the data used in this example, run the following SQL statements:

create table stock_price_history (company TEXT, price_date DATE, price INT);

Copy

insert into stock_price_history values
    ('ABCD', '2020-10-01', 50),
    ('XYZ' , '2020-10-01', 89),
    ('ABCD', '2020-10-02', 36),
    ('XYZ' , '2020-10-02', 24),
    ('ABCD', '2020-10-03', 39),
    ('XYZ' , '2020-10-03', 37),
    ('ABCD', '2020-10-04', 42),
    ('XYZ' , '2020-10-04', 63),
    ('ABCD', '2020-10-05', 30),
    ('XYZ' , '2020-10-05', 65),
    ('ABCD', '2020-10-06', 47),
    ('XYZ' , '2020-10-06', 56),
    ('ABCD', '2020-10-07', 71),
    ('XYZ' , '2020-10-07', 50),
    ('ABCD', '2020-10-08', 80),
    ('XYZ' , '2020-10-08', 54),
    ('ABCD', '2020-10-09', 75),
    ('XYZ' , '2020-10-09', 30),
    ('ABCD', '2020-10-10', 63),
    ('XYZ' , '2020-10-10', 32);

Copy

Step 1: Specifying the Order and Grouping of Rows¶

The first step in identifying a sequence of rows is defining the grouping and sort order of the rows that you want to search. For the example of finding a “V” pattern in the stock price for a company:

• The rows should be grouped by company, since you want to find a pattern in the price for a given company.

• Within each group of rows (the prices for a given company), the rows should be sorted by date in ascending order.

In a MATCH_RECOGNIZE clause, you use the PARTITION BY and ORDER BY subclauses to specify the grouping and order of rows. For example:

MATCH_RECOGNIZE(
  PARTITION BY company
  ORDER BY price_date
  ...
)

Copy

Step 2: Defining the Pattern to Match¶

Next, determine the pattern that matches the sequence of rows that you want to find.

To specify this pattern, you use something similar to a regular expression. In regular expressions, you use a combination of literals and metacharacters to specify a pattern to match in a string.

For example, to find a sequence of characters that includes:

• any single character, followed by

• one or more uppercase letters, followed by

• one or more lowercase letters

you can use the following Perl-compatible regular expression:

.[A-Z]+[a-z]+

where:

• . matches any single character.

• [A-Z]+ matches one or more uppercase letters.

• [a-z]+ matches one or more lowercase letters.

+ is a quantifier that specifies that one or more of the preceding characters need to match.

For example, the regular expression above matches sequences of characters like:

• 1Stock

• @SFComputing

• %Fn

In a MATCH_RECOGNIZE clause, you use a similar expression to specify the pattern of rows to match. In this case, finding rows that match a “V” pattern involves finding a sequence of rows that includes:

• the row before the stock price decreases, followed by

• one or more rows where the stock price decreases, followed by

• one or more rows where the stock price increases

You can express this as the following row pattern:

row_before_decrease row_with_price_decrease+ row_with_price_increase+

Row patterns consist of pattern variables, quantifiers (which are similar to those used in regular expressions), and operators. A pattern variable defines an expression that is evaluated against a row.

In this row pattern:

• row_before_decrease, row_with_price_decrease, and row_with_price_increase are pattern variables. The expressions for these pattern variables should evaluate to:

  • any row (the row before the stock price decreases)

  • a row where the stock price decreases

  • a row where the stock price increases

  row_before_decrease is similar to . in a regular expression. In the following regular expression, . matches any single character that appears before the first uppercase letter in the pattern.

  .[A-Z]+[a-z]+
  

  Copy

  Similarly, in the row pattern, row_before_decrease matches any single row that appears before the first row with a price decrease.

• The + quantifiers after row_with_price_decrease and row_with_price_increase specify that one or more rows of each of these must match.

In a MATCH_RECOGNIZE clause, you use the PATTERN subclause to specify the row pattern to match:

MATCH_RECOGNIZE(
  ...
  PATTERN(row_before_decrease row_with_price_decrease+ row_with_price_increase+)
  ...
)

To specify the expressions for the pattern variables, you use the DEFINE subclause:

MATCH_RECOGNIZE(
  ...
  DEFINE
    row_with_price_decrease AS price < LAG(price)
    row_with_price_increase AS price > LAG(price)
  ...
)

Copy

where:

• row_before_decrease does not need to be defined here because it should evaluate to any row.

• row_with_price_decrease is defined as an expression for a row with a price decrease.

• row_with_price_increase is defined as an expression for a row with a price increase.

To compare the prices in different rows, the definitions of these variables use the navigational function LAG() to specify price for the previous row.

The row pattern matches two sequences of rows, as illustrated below:

For the first matching sequence of rows:

• row_before_decrease matches the row with the stock price 50.

• row_with_price_decrease matches the next row with the stock price 36.

• row_with_price_increase matches the next two rows with the stock prices 39 and 42.

For the second matching sequence of rows:

• row_before_decrease matches the row with the stock price 42. (This is the same row that is at the end of the first matching sequence of rows.)

• row_with_price_decrease matches the next row with the stock price 30.

• row_with_price_increase matches the next two rows with the stock prices 47, 71, and 80.

Step 3: Specifying the Rows to Return¶

MATCH_RECOGNIZE can either return:

• a single row that summarizes each matching sequence, or

• each row in each matching sequence

For this example, you want to return a summary of each matching sequence. Use the ONE ROW PER MATCH subclause to specify that one row should be returned for each matching sequence.

MATCH_RECOGNIZE(
  ...
  ONE ROW PER MATCH
  ...
)

Step 4: Specifying the Measures to Select¶

When you use ONE ROW PER MATCH, MATCH_RECOGNIZE does not return any of the columns in the table (except for the column specified by PARTITION BY), even when MATCH_RECOGNIZE is in a SELECT * statement. To specify the data to be returned by this statement, you must define measures. Measures are additional columns of data that are calculated for each matching sequence of rows (e.g. the starting date of the sequence, the ending date of the sequence, the number of days in the sequence, etc.).

Use the MEASURES subclause to specify these additional columns to return in the output. The general format for defining a measure is:

<expression> AS <column_name>

where:

• expression specifies the information about the sequence that you want to return. For the expression, you can use functions with columns from the table and pattern variables that you defined earlier.

• column_name specifies the name of the column that will be returned in the output.

For this example, you can define the following measures:

• A number that identifies the sequence (the first matching sequence, the second matching sequence, etc.).

  For this measure, use the MATCH_NUMBER() function, which returns the number of the match. The numbers start with 1 for the first match for a partition of rows. If there are multiple partitions, the number starts with 1 for each partition.

• The day before the stock price decreased.

  For this measure, use the FIRST() function, which returns the value of the expression for the first row in the matching sequence. In this example, FIRST(price_date) returns the value of the price_date column in the first row in each matching sequence, which is the date before the stock price decreased.

• The last day when the stock price increased.

  For this measure, use the LAST() function, which returns the value of the expression for the last row in the matching sequence.

• The number of days in the “V” pattern.

  For this measure, use COUNT(*). Because you are specifying COUNT(*) in the definition of a measure, the asterisk (*) specifies that you want to count all of the rows in a matching sequence (not all of the rows in the table).

• The number of days when the stock decreased.

  For this measure, use COUNT(row_with_price_decrease.*). The period followed by an asterisk (.*) specifies that you want to count all of the rows in a matching sequence that match the pattern variable row_with_price_decrease.

• The number of days when the stock increased.

  For this measure, use COUNT(row_with_price_increase.*).

The following is the MEASURES subclause that defines the measures above:

MATCH_RECOGNIZE(
  ...
  MEASURES
    MATCH_NUMBER() AS match_number,
    FIRST(price_date) AS start_date,
    LAST(price_date) AS end_date,
    COUNT(*) AS num_matching_rows,
    COUNT(row_with_price_decrease.*) AS num_decreases,
    COUNT(row_with_price_increase.*) AS num_increases
  ...
)

The following shows an example of the output with the selected measures:

+---------+--------------+------------+------------+-------------------+---------------+---------------+
| COMPANY | MATCH_NUMBER | START_DATE | END_DATE   | NUM_MATCHING_ROWS | NUM_DECREASES | NUM_INCREASES |
|---------+--------------+------------+------------+-------------------+---------------+---------------|
| ABCD    |            1 | 2020-10-01 | 2020-10-04 |                 4 |             1 |             2 |
| ABCD    |            2 | 2020-10-04 | 2020-10-08 |                 5 |             1 |             3 |
+---------+--------------+------------+------------+-------------------+---------------+---------------+

As mentioned earlier, the output includes the company column because the PARTITION BY clause specifies that column.

Step 5: Specifying Where to Continue Finding the Next Match¶

After finding a matching sequence of rows, MATCH_RECOGNIZE continues to find the next matching sequence. You can specify where MATCH_RECOGNIZE should start searching for the next matching sequence.

As shown in the illustration of matching sequences, a row can be part of more than one matching sequence. In this example, the row for 2020-10-04 is part of two “V” patterns.

For this example, to find the next matching sequence, you can start from a row where the price increased. To specify this in the MATCH_RECOGNIZE clause, use AFTER MATCH SKIP:

MATCH_RECOGNIZE(
  ...
  AFTER MATCH SKIP TO LAST row_with_price_increase
  ...
)

where TO LAST row_with_price_increase specifies that you want to start searching at the last row where the price increased.

Using Quantifiers With Pattern Variables¶

In the PATTERN subclause, you use a regular expression to specify a pattern of rows to match. You use pattern variables to identify rows in the sequence that meet specific conditions.

If you need to match multiple rows that meet a specific condition, you can use a quantifier, as you would in a regular expression.

For example, you can use the quantifier + to specify that pattern must include one or more rows in which the stock price decreases by 10%, followed by one or more rows in which the stock price increases by 5%:

pattern (decreased_10_percent+ increased_05_percent+)
define
    decreased_10_percent as lag(price) * 0.90 >= price,
    increased_05_percent as lag(price) * 1.05 <= price

Matching Patterns Relative to the Beginning or End of a Partition¶

To find a sequence of rows relative to the beginning or end of a partition, you can use the metacharacters ^ and $ in the PATTERN subclause. These metacharacters in a row pattern have a similar purpose as the same metacharacters have in a regular expression:

• ^ represents the beginning of a partition.

• $ represents the end of a partition.

The following pattern matches a stock with a price greater than 75.00 at the beginning of the partition:

PATTERN (^ GT75)
DEFINE
    GT75 AS price > 75.00

Note that ^ and $ specify positions and do not represent the rows at those positions (much like ^ and $ in a regular expression specify the position and not the characters at those positions). In PATTERN (^ GT75), the first row (not the second row) must have a price greater than 75.00. In PATTERN (GT75 $), the last row (not the second-to-last row) must be greater than 75.

Here is a complete example with ^. Note that although the XYZ stock has a price higher than 60.00 in more than one row in this partition, only the row at the start of the partition is considered a match.

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #"
           ALL ROWS PER MATCH
           PATTERN (^ GT60)
           DEFINE
               GT60 AS price > 60.00
           )
    ORDER BY "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # |
|---------+------------+-------+---------+------------------|
| XYZ     | 2020-10-01 |    89 |       1 |                1 |
+---------+------------+-------+---------+------------------+

Copy

Here is a complete example with $. Note that although the ABCD stock has a price higher than 50.00 in more than one row in this partition, only the row at the end of the partition is considered a match.

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #"
           ALL ROWS PER MATCH
           PATTERN (GT50 $)
           DEFINE
               GT50 AS price > 50.00
           )
    ORDER BY "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # |
|---------+------------+-------+---------+------------------|
| ABCD    | 2020-10-10 |    63 |       1 |                1 |
+---------+------------+-------+---------+------------------+

Copy

Generating One Row for Each Match vs Generating All Rows for Each Match¶

When MATCH_RECOGNIZE finds a match, the output can be either one summary row for the entire match, or one row for each data point in the pattern.

• ALL ROWS PER MATCH specifies that the output include all rows in the match.

• ONE ROW PER MATCH specifies that the output include only one row for each match in each partition.

  The projection clause of the SELECT statement can use only the output of the MATCH_RECOGNIZE. Effectively, this means that the SELECT statement can only use columns from the following subclauses of MATCH_RECOGNIZE:

  • The PARTITION BY subclause.

    All rows in a match are from the same partition, and therefore have the same value for the PARTITION BY subclause expressions.

  • The MEASURES clause.

    When you use MATCH_RECOGNIZE ... ONE ROW PER MATCH, the MEASURES subclause generates not only expressions that return the same value for all rows in the match (e.g. MATCH_NUMBER()), but also expressions that can return different values for different rows in the match (e.g. MATCH_SEQUENCE_NUMBER()). If you use expressions that can return different values for different rows in the match, the output is not deterministic.

  If you are familiar with aggregate functions and GROUP BY, the following analogy might be helpful in understanding ONE ROW PER MATCH:

  • The PARTITION BY clause in MATCH_RECOGNIZE groups data similarly to the way that GROUP BY groups data in a SELECT.

  • The MEASURES clause in a MATCH_RECOGNIZE ... ONE ROW PER MATCH allows aggregate functions, such as COUNT(), that return the same value for each row in the match, as MATCH_NUMBER() does.

  If you use only aggregate functions and expressions that return the same value for each row in the match, then ... ONE ROW PER MATCH behaves similarly to GROUP BY and aggregate functions.

The default is ONE ROW PER MATCH.

The following examples show the difference in outputs between ONE ROW PER MATCH and ALL ROWS PER MATCH. These two code examples are almost identical except for the ...ROW(S) PER MATCH clause. (In typical usage, a SQL statement with ONE ROW PER MATCH has different MEASURES subclauses than a SQL statement with ALL ROWS PER MATCH.)

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #",
               COUNT(*) AS "Num Rows In Match"
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 UP UP)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP AS price > LAG(price)
           )
    WHERE company = 'ABCD'
    ORDER BY "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+-------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # | Num Rows In Match |
|---------+------------+-------+---------+------------------+-------------------|
| ABCD    | 2020-10-02 |    36 |       1 |                1 |                 1 |
| ABCD    | 2020-10-03 |    39 |       1 |                2 |                 2 |
| ABCD    | 2020-10-04 |    42 |       1 |                3 |                 3 |
| ABCD    | 2020-10-05 |    30 |       2 |                1 |                 1 |
| ABCD    | 2020-10-06 |    47 |       2 |                2 |                 2 |
| ABCD    | 2020-10-07 |    71 |       2 |                3 |                 3 |
+---------+------------+-------+---------+------------------+-------------------+

-- As you can see, the MATCH_SEQUENCE_NUMBER isn't useful when using
-- "ONE ROW PER MATCH". But the COUNT(*), which wasn't very useful in
-- "ALL ROWS PER MATCH", is useful here.
SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #",
               COUNT(*) AS "Num Rows In Match"
           ONE ROW PER MATCH
           PATTERN (LESS_THAN_45 UP UP)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP AS price > LAG(price)
           )
    WHERE company = 'ABCD'
    ORDER BY "Match #", "Match Sequence #";
+---------+---------+------------------+-------------------+
| COMPANY | Match # | Match Sequence # | Num Rows In Match |
|---------+---------+------------------+-------------------|
| ABCD    |       1 |                3 |                 3 |
| ABCD    |       2 |                3 |                 3 |
+---------+---------+------------------+-------------------+

Excluding Rows from the Output¶

For some queries, you might want to include only part of the pattern in the output. For example, you might want to find patterns in which stocks rose many days in a row, but display only the peaks and some summary information (for example, the number of days of price increases before each peak).

You can use exclusion syntax in the pattern to tell MATCH_RECOGNIZE to search for a particular pattern variable but not include it in the output. To include a pattern variable as part of the pattern to search for, but not as part of the output, use the {- <pattern_variable> -} notation.

Here is a simple example that shows the difference between using exclusion syntax and not using it. This example contains two queries, each of which searches for a stock price that started at less than $45, then decreased, and then increased. The first query does not use exclusion syntax, and therefore shows all the rows. The second query uses the exclusion syntax and does not show the day that the stock price fell.

SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 DECREASED_10_PERCENT INCREASED_05_PERCENT)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               DECREASED_10_PERCENT AS LAG(price) * 0.90 >= price,
               INCREASED_05_PERCENT AS LAG(price) * 1.05 <= price
           )
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| ABCD    | 2020-10-04 |    42 |
| ABCD    | 2020-10-05 |    30 |
| ABCD    | 2020-10-06 |    47 |
+---------+------------+-------+

Copy

SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 {- DECREASED_10_PERCENT -} INCREASED_05_PERCENT)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               DECREASED_10_PERCENT AS LAG(price) * 0.90 >= price,
               INCREASED_05_PERCENT AS LAG(price) * 1.05 <= price
           )
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| ABCD    | 2020-10-04 |    42 |
| ABCD    | 2020-10-06 |    47 |
+---------+------------+-------+

Copy

The next example is more realistic. It searches for patterns in which a stock price rose one or more days in a row, and then fell one or more days in a row. Because the output could be quite large, this uses exclusion to show only the first day the stock rose (if it rose more than one day in a row) and only the first day it dropped (if it dropped more than one day in a row). The pattern is shown below:

PATTERN(LESS_THAN_45 UP {- UP* -} DOWN {- DOWN* -})

This pattern looks for the following events in order:

• A starting price less than 45.

• An UP, possibly followed immediately by others that are not included in the output.

• A DOWN, possibly followed immediately by others that are not included in the output.

Here are the code and output for versions of the preceding pattern without exclusion and with exclusion:

SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN ( LESS_THAN_45 UP UP* DOWN DOWN* )
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP   AS price > LAG(price),
               DOWN AS price < LAG(price)
           )
    WHERE company = 'XYZ'
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| XYZ     | 2020-10-02 |    24 |
| XYZ     | 2020-10-03 |    37 |
| XYZ     | 2020-10-04 |    63 |
| XYZ     | 2020-10-05 |    65 |
| XYZ     | 2020-10-06 |    56 |
| XYZ     | 2020-10-07 |    50 |
+---------+------------+-------+

Copy

SELECT company, price_date, price
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           ALL ROWS PER MATCH
           PATTERN ( {- LESS_THAN_45 -}  UP  {- UP* -}  DOWN  {- DOWN* -} )
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               UP   AS price > LAG(price),
               DOWN AS price < LAG(price)
           )
    WHERE company = 'XYZ'
    ORDER BY price_date;
+---------+------------+-------+
| COMPANY | PRICE_DATE | PRICE |
|---------+------------+-------|
| XYZ     | 2020-10-03 |    37 |
+---------+------------+-------+

Copy

Basic Match Information¶

In many cases, you want your query to list not only information from the table that contains the data, but also information about the patterns that were found. When you want information about the matches themselves, you specify that information in the MEASURES clause.

The MEASURES clause can include the following functions, which are specific to MATCH_RECOGNIZE:

• MATCH_NUMBER(): Each time a match is found, it is assigned a sequential match number, starting from one. This function returns that match number.

• MATCH_SEQUENCE_NUMBER(): Because a pattern usually involves more than one data point, you might want to know which data point is associated with each value from the table. This function returns the sequential number of the data point within the match.

• CLASSIFIER(): The classifier is the name of the pattern variable that the row matched.

The query below includes a MEASURES clause with the match number, match sequence number, and classifier.

SELECT company, price_date, price,
       "Match #", "Match Sequence #", "Symbol Matched"
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #",
               CLASSIFIER AS "Symbol Matched"
           ALL ROWS PER MATCH
           PATTERN (LESS_THAN_45 DECREASED_10_PERCENT INCREASED_05_PERCENT)
           DEFINE
               LESS_THAN_45 AS price < 45.00,
               DECREASED_10_PERCENT AS LAG(price) * 0.90 >= price,
               INCREASED_05_PERCENT AS LAG(price) * 1.05 <= price
           )
    ORDER BY company, "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+----------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # | Symbol Matched       |
|---------+------------+-------+---------+------------------+----------------------|
| ABCD    | 2020-10-04 |    42 |       1 |                1 | LESS_THAN_45         |
| ABCD    | 2020-10-05 |    30 |       1 |                2 | DECREASED_10_PERCENT |
| ABCD    | 2020-10-06 |    47 |       1 |                3 | INCREASED_05_PERCENT |
+---------+------------+-------+---------+------------------+----------------------+

Copy

The MEASURES subclause can produce much more information than this. For more details, see the MATCH_RECOGNIZE reference documentation.

Windows, Window Frames, and Navigational Functions¶

The MATCH_RECOGNIZE clause operates on a “window” of rows. If the MATCH_RECOGNIZE contains a PARTITION subclause, then each partition is one window. If there is no PARTITION subclause, then the entire input is one window.

The PATTERN subclause of MATCH_RECOGNIZE specifies the symbols in order from left to right. For example:

PATTERN (START DOWN UP)

If you picture the data as a sequence of rows in ascending order from left to right, you can think of MATCH_RECOGNIZE as moving rightward (e.g. from the earliest date to the latest date in the stock price example), searching for a pattern in the rows inside each window.

MATCH_RECOGNIZE starts with the first row in the window and checks whether that row and the subsequent rows match the pattern.

In the simplest case, after determining whether there’s a pattern match starting at the first row in the window, MATCH_RECOGNIZE moves rightward one row and repeats the process, checking whether the 2nd row is the beginning of an occurrence of the pattern. MATCH_RECOGNIZE continues moving rightward until it reaches the end of the window.

(MATCH_RECOGNIZE can move rightward by more than one row. For example, you can tell MATCH_RECOGNIZE to start searching for the next pattern after the end of the current pattern.)

You can picture this loosely as though there were a “frame” moving rightward inside the window. The left-hand edge of that frame is at the first row in the set of rows currently being checked for a match. The right-hand edge of the frame is not defined until a match is found; once a match is found, the right-hand edge of the frame is the last row in the match. For example, if the search pattern were pattern (start down up) then the row that matches the up is the last row before the right-hand edge of the frame.

(If no match is found, then the right-hand edge of the frame is never defined and is never referenced.)

In simple cases, you can picture a sliding window frame as illustrated below:

You have already seen navigational functions such as LAG() used in expressions in the DEFINE subclause (e.g. DEFINE down_10_percent as LAG(price) * 0.9 >= price). The following query shows that navigational functions can also be used in the MEASURES subclause. In this example, the navigational functions show the edges (and thus the size) of the window frame that contains the current match.

Each output row from this query includes the values of the LAG(), LEAD(), FIRST(), and LAST() navigational functions for that row. The size of the window frame is the number of rows between FIRST() and LAST(), including the first and last rows themselves.

The DEFINE and PATTERN clauses in the query below select groups of three rows (October 1-3, October 2-4, October 3-5, etc.).

SELECT company, price_date,
       "First(price_date)", "Lag(price_date)", "Lead(price_date)", "Last(price_date)",
       "Match#", "MatchSeq#", "Classifier"
    FROM stock_price_history
        MATCH_RECOGNIZE (
            PARTITION BY company
            ORDER BY price_date
            MEASURES
                -- Show the "edges" of the "window frame".
                FIRST(price_date) AS "First(price_date)",
                LAG(price_date) AS "Lag(price_date)",
                LEAD(price_date) AS "Lead(price_date)",
                LAST(price_date) AS "Last(price_date)",
                MATCH_NUMBER() AS "Match#",
                MATCH_SEQUENCE_NUMBER() AS "MatchSeq#",
                CLASSIFIER AS "Classifier"
            ALL ROWS PER MATCH
            AFTER MATCH SKIP TO NEXT ROW
            PATTERN (CURRENT_ROW T2 T3)
            DEFINE
                CURRENT_ROW AS TRUE,
                T2 AS TRUE,
                T3 AS TRUE
            )
    ORDER BY company, "Match#", "MatchSeq#"
    ;
+---------+------------+-------------------+-----------------+------------------+------------------+--------+-----------+-------------+
| COMPANY | PRICE_DATE | First(price_date) | Lag(price_date) | Lead(price_date) | Last(price_date) | Match# | MatchSeq# | Classifier  |
|---------+------------+-------------------+-----------------+------------------+------------------+--------+-----------+-------------|
| ABCD    | 2020-10-01 | 2020-10-01        | NULL            | 2020-10-02       | 2020-10-01       |      1 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-02 | 2020-10-01        | 2020-10-01      | 2020-10-03       | 2020-10-02       |      1 |         2 | T2          |
| ABCD    | 2020-10-03 | 2020-10-01        | 2020-10-02      | NULL             | 2020-10-03       |      1 |         3 | T3          |
| ABCD    | 2020-10-02 | 2020-10-02        | NULL            | 2020-10-03       | 2020-10-02       |      2 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-03 | 2020-10-02        | 2020-10-02      | 2020-10-04       | 2020-10-03       |      2 |         2 | T2          |
| ABCD    | 2020-10-04 | 2020-10-02        | 2020-10-03      | NULL             | 2020-10-04       |      2 |         3 | T3          |
| ABCD    | 2020-10-03 | 2020-10-03        | NULL            | 2020-10-04       | 2020-10-03       |      3 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-04 | 2020-10-03        | 2020-10-03      | 2020-10-05       | 2020-10-04       |      3 |         2 | T2          |
| ABCD    | 2020-10-05 | 2020-10-03        | 2020-10-04      | NULL             | 2020-10-05       |      3 |         3 | T3          |
| ABCD    | 2020-10-04 | 2020-10-04        | NULL            | 2020-10-05       | 2020-10-04       |      4 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-05 | 2020-10-04        | 2020-10-04      | 2020-10-06       | 2020-10-05       |      4 |         2 | T2          |
| ABCD    | 2020-10-06 | 2020-10-04        | 2020-10-05      | NULL             | 2020-10-06       |      4 |         3 | T3          |
| ABCD    | 2020-10-05 | 2020-10-05        | NULL            | 2020-10-06       | 2020-10-05       |      5 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-06 | 2020-10-05        | 2020-10-05      | 2020-10-07       | 2020-10-06       |      5 |         2 | T2          |
| ABCD    | 2020-10-07 | 2020-10-05        | 2020-10-06      | NULL             | 2020-10-07       |      5 |         3 | T3          |
| ABCD    | 2020-10-06 | 2020-10-06        | NULL            | 2020-10-07       | 2020-10-06       |      6 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-07 | 2020-10-06        | 2020-10-06      | 2020-10-08       | 2020-10-07       |      6 |         2 | T2          |
| ABCD    | 2020-10-08 | 2020-10-06        | 2020-10-07      | NULL             | 2020-10-08       |      6 |         3 | T3          |
| ABCD    | 2020-10-07 | 2020-10-07        | NULL            | 2020-10-08       | 2020-10-07       |      7 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-08 | 2020-10-07        | 2020-10-07      | 2020-10-09       | 2020-10-08       |      7 |         2 | T2          |
| ABCD    | 2020-10-09 | 2020-10-07        | 2020-10-08      | NULL             | 2020-10-09       |      7 |         3 | T3          |
| ABCD    | 2020-10-08 | 2020-10-08        | NULL            | 2020-10-09       | 2020-10-08       |      8 |         1 | CURRENT_ROW |
| ABCD    | 2020-10-09 | 2020-10-08        | 2020-10-08      | 2020-10-10       | 2020-10-09       |      8 |         2 | T2          |
| ABCD    | 2020-10-10 | 2020-10-08        | 2020-10-09      | NULL             | 2020-10-10       |      8 |         3 | T3          |
| XYZ     | 2020-10-01 | 2020-10-01        | NULL            | 2020-10-02       | 2020-10-01       |      1 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-02 | 2020-10-01        | 2020-10-01      | 2020-10-03       | 2020-10-02       |      1 |         2 | T2          |
| XYZ     | 2020-10-03 | 2020-10-01        | 2020-10-02      | NULL             | 2020-10-03       |      1 |         3 | T3          |
| XYZ     | 2020-10-02 | 2020-10-02        | NULL            | 2020-10-03       | 2020-10-02       |      2 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-03 | 2020-10-02        | 2020-10-02      | 2020-10-04       | 2020-10-03       |      2 |         2 | T2          |
| XYZ     | 2020-10-04 | 2020-10-02        | 2020-10-03      | NULL             | 2020-10-04       |      2 |         3 | T3          |
| XYZ     | 2020-10-03 | 2020-10-03        | NULL            | 2020-10-04       | 2020-10-03       |      3 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-04 | 2020-10-03        | 2020-10-03      | 2020-10-05       | 2020-10-04       |      3 |         2 | T2          |
| XYZ     | 2020-10-05 | 2020-10-03        | 2020-10-04      | NULL             | 2020-10-05       |      3 |         3 | T3          |
| XYZ     | 2020-10-04 | 2020-10-04        | NULL            | 2020-10-05       | 2020-10-04       |      4 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-05 | 2020-10-04        | 2020-10-04      | 2020-10-06       | 2020-10-05       |      4 |         2 | T2          |
| XYZ     | 2020-10-06 | 2020-10-04        | 2020-10-05      | NULL             | 2020-10-06       |      4 |         3 | T3          |
| XYZ     | 2020-10-05 | 2020-10-05        | NULL            | 2020-10-06       | 2020-10-05       |      5 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-06 | 2020-10-05        | 2020-10-05      | 2020-10-07       | 2020-10-06       |      5 |         2 | T2          |
| XYZ     | 2020-10-07 | 2020-10-05        | 2020-10-06      | NULL             | 2020-10-07       |      5 |         3 | T3          |
| XYZ     | 2020-10-06 | 2020-10-06        | NULL            | 2020-10-07       | 2020-10-06       |      6 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-07 | 2020-10-06        | 2020-10-06      | 2020-10-08       | 2020-10-07       |      6 |         2 | T2          |
| XYZ     | 2020-10-08 | 2020-10-06        | 2020-10-07      | NULL             | 2020-10-08       |      6 |         3 | T3          |
| XYZ     | 2020-10-07 | 2020-10-07        | NULL            | 2020-10-08       | 2020-10-07       |      7 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-08 | 2020-10-07        | 2020-10-07      | 2020-10-09       | 2020-10-08       |      7 |         2 | T2          |
| XYZ     | 2020-10-09 | 2020-10-07        | 2020-10-08      | NULL             | 2020-10-09       |      7 |         3 | T3          |
| XYZ     | 2020-10-08 | 2020-10-08        | NULL            | 2020-10-09       | 2020-10-08       |      8 |         1 | CURRENT_ROW |
| XYZ     | 2020-10-09 | 2020-10-08        | 2020-10-08      | 2020-10-10       | 2020-10-09       |      8 |         2 | T2          |
| XYZ     | 2020-10-10 | 2020-10-08        | 2020-10-09      | NULL             | 2020-10-10       |      8 |         3 | T3          |
+---------+------------+-------------------+-----------------+------------------+------------------+--------+-----------+-------------+

The output of this query also illustrates that LAG() and LEAD() functions return NULL for expressions that attempt to reference rows outside the match group (i.e. outside the window frame).

The rules for navigational functions in DEFINE clauses are slightly different from the rules for navigational functions in MEASURES clauses. For example, the PREV() function is available in the MEASURES clause but currently not in the DEFINE clause. Instead, you can use LAG() in the DEFINE clause. The reference documentation for MATCH_RECOGNIZE lists the corresponding rule for each navigational function.

The MEASURES subclause can also include the following:

• Aggregate functions. For example, if the pattern can match a varying number of rows (e.g. because it matches 1 or more falling stock prices), then you might want to know the total number of rows in the match; you can show this by using COUNT(*).

• General expressions that operate on values in each row in the match. These can be mathematical expressions, logical expressions, etc. For example, you could look at values in the row and print text descriptors such as “ABOVE AVERAGE”.

  Remember that if you group rows (ONE ROW PER MATCH), and if a column has different values for different rows in the group, the value selected for that column for that match is non-deterministic, and expressions based on that value are also likely to be non-deterministic.

For more information about the MEASURES subclause, see the reference documentation for MATCH_RECOGNIZE.

Correlation vs Causality¶

Correlation does not guarantee causality. MATCH_RECOGNIZE can return “false positives” (cases where you see a pattern, but it is just a coincidence).

Pattern matching can also result in “false negatives” (cases where there is a pattern in the real world, but the pattern does not appear in the data sample).

In most cases, finding a match (for example, finding a pattern that suggests insurance fraud) is just the first step in an analysis.

The following factors typically increase the number of false positives:

• Large data sets.

• Searching for a large number of patterns.

• Searching for short or simple patterns.

The following factors typically increase the number of false negatives.

• Small data sets.

• Not searching for all the possible relevant patterns.

• Searching for patterns that are more complex than necessary.

Order-Insensitive Patterns¶

Although most pattern matching requires that the data be in order (for example, by time), there are exceptions. For example, if a person commits insurance fraud both in an automobile accident and in a home burglary, it doesn’t matter which order the frauds occur in.

If the pattern you’re looking for is not order-sensitive, then you can use operators such as “alternative” (|) and PERMUTE to make your searches less order-sensitive.

Find Multi-Day Price Increases¶

The following query finds all the patterns in which the price of company ABCD rose two days in a row:

SELECT *
    FROM stock_price_history
       MATCH_RECOGNIZE (
           PARTITION BY company
           ORDER BY price_date
           MEASURES
               MATCH_NUMBER() AS "Match #",
               MATCH_SEQUENCE_NUMBER() AS "Match Sequence #"
           ALL ROWS PER MATCH
           PATTERN (MINIMUM_37 UP UP)
           DEFINE
               MINIMUM_37 AS price >= 37.00,
               UP AS price > LAG(price)
           )
    ORDER BY company, "Match #", "Match Sequence #";
+---------+------------+-------+---------+------------------+
| COMPANY | PRICE_DATE | PRICE | Match # | Match Sequence # |
|---------+------------+-------+---------+------------------|
| ABCD    | 2020-10-06 |    47 |       1 |                1 |
| ABCD    | 2020-10-07 |    71 |       1 |                2 |
| ABCD    | 2020-10-08 |    80 |       1 |                3 |
| XYZ     | 2020-10-03 |    37 |       1 |                1 |
| XYZ     | 2020-10-04 |    63 |       1 |                2 |
| XYZ     | 2020-10-05 |    65 |       1 |                3 |
+---------+------------+-------+---------+------------------+

Copy

Demonstrate the SKIP TO NEXT ROW Option¶

This example demonstrates the SKIP TO NEXT ROW option. This query searches for W-shaped curves in each company’s chart. The matches can overlap.

select * from stock_price_history match_recognize(
    partition by company
    order by price_date
    measures
        match_number() as "MATCH_NUMBER",
        first(price_date) as "START",
        last(price_date) as "END",
        count(*) as "PRICE_COUNT"
    after match skip to next row
    pattern(ANY_ROW DOWN+ UP+ DOWN+ UP+)
    define
        ANY_ROW AS TRUE,
        UP as price > lag(price),
        DOWN as price < lag(price)
)
order by company, match_number;
+---------+--------------+------------+------------+-------------+
| COMPANY | MATCH_NUMBER | START      | END        | PRICE_COUNT |
|---------+--------------+------------+------------+-------------|
| ABCD    |            1 | 2020-10-01 | 2020-10-08 |           8 |
| XYZ     |            1 | 2020-10-01 | 2020-10-08 |           8 |
| XYZ     |            2 | 2020-10-05 | 2020-10-10 |           6 |
| XYZ     |            3 | 2020-10-06 | 2020-10-10 |           5 |
+---------+--------------+------------+------------+-------------+

Copy

Search for Patterns in Non-Adjacent Rows¶

In some situations, you might want to look for patterns in non-contiguous rows. For example, if you are analyzing log files, you might want to search for all patterns in which a fatal error was preceded by a particular sequence of warnings. There might not be a natural way to partition and sort the rows so that all of the relevant messages (rows) are in a single window and adjacent. In that situation, you might need a pattern that looks for particular events, but doesn’t require that the events be contiguous in the data.

Below is an example of DEFINE and PATTERN clauses that recognizes either contiguous or non-contiguous rows that fit the pattern. The symbol ANY_ROW is defined as TRUE (so it matches any row). The * after each occurrence of ANY_ROW says to allow 0 or more of occurrences of ANY_ROW between the first warning and the second warning, and between the second warning and the fatal error log message. Thus the entire pattern says to search for WARNING1, followed by any number of rows, followed by WARNING2, followed by any number of rows, followed by FATAL_ERROR. To omit the irrelevant rows from the output, the query uses exclusion syntax ({- and -}).

MATCH_RECOGNIZE (
    ...
    ORDER BY log_message_timestamp
    ...
    ALL ROWS PER MATCH
    PATTERN ( WARNING1  {- ANY_ROW* -}  WARNING2  {- ANY_ROW* -}  FATAL_ERROR )
    DEFINE
        ANY_ROW AS TRUE,
        WARNING1 AS SUBSTR(log_message, 1, 42) = 'WARNING: Available memory is less than 10%',
        WARNING2 AS SUBSTR(log_message, 1, 41) = 'WARNING: Available memory is less than 5%',
        FATAL_ERROR AS SUBSTR(log_message, 1, 11) = 'FATAL ERROR'
    )
...

Errors When Using ONE ROW PER MATCH and Specifying Columns in the Select Clause¶

The ONE ROW PER MATCH clause acts similarly to an aggregate function. This limits the output columns you can use. For example, if you use ONE ROW PER MATCH and each match contains three rows with different dates, then you can’t specify the date column as an output column in the SELECT clause because no single date is correct for all three rows.

Unexpected Results¶

• Check for typographical errors in the PATTERN and DEFINE clauses.

  If a pattern variable name used in the PATTERN clause is not defined in the DEFINE clause (e.g. because the name is typed incorrectly in either the PATTERN or DEFINE clause), then no error is reported. Instead, the pattern variable name is simply assumed to be true for each row.

• Review the SKIP clause to make sure that it is appropriate, for example to include or exclude overlapping patterns.