The best SQL function you never heard of

There are a two things in IT that made me feel like my brain turned inside out when learning them. One of them is the SQL concept of row pattern matching.

Think of row pattern matching as doing regex between rows. Instead of finding single rows where some column has some value (or, more often, some combination of values like WHERE col_a>1 AND col_b='puzzle'), we can find sets of rows that together constitute some interesting pattern.

A basic example

The canonical example is analyzing stock prices: Can we find stock that have seen a 5 or more day rally? That have rebounded for at least 5 days after declining for at least 5 days? The longest stretch of continuous price increase? None of these questions can be answered by a WHERE clause alone, and even though the lag() function might get you far, it gets messy quickly even if it can give you an answer.

Row pattern matching introduces some new concepts though, that we need to go through carefully in order to grasp them.

Instead of stock prices we will use currency rates, which have all the same properties. A basic row pattern matching query can look something like this:

SELECT currency, tstamp, value FROM sdr_rates
MATCH_RECOGNIZE(
    PARTITION BY currency
    ORDER BY tstamp
    ALL ROWS PER MATCH
    PATTERN ( up+ )
    DEFINE up AS LAG(value)<value
);

We recognize the first line, but beyond that there are many new keywords.

All the action happens within the MATCH_RECOGNIZE clause, which is the row pattern matching function. The first thing we do is to partition the data by the stock symbol. Partitioning here works similar to partitioning elsewhere, like in an OVER() clause. We want to split the data by stock, we aren't interested in comparing different stocks to each other. Each stock should be seen as a separate time series. ORDER BY is also quite simple, we specify how we should order the data. Here, we order the data by date (ascending, earliest first).

We will come back to the ALL ROWS PER MATCH clause, but for now, I'll note that row pattern matching by default aggregates the data and only shows one line per match. Right now, we want to see all the rows.

The PATTERN clause is the main regex-like clause. We specify we want to select one or more (the + sign is basically regex) of something we call up, which is defined on the next line.

The last line, DEFINE, is where we define up by a set of criteria. Any row that matches these criteria is labeled up, and will match in the pattern we define. This definition uses yet another new keyword, but one we can probably guess PREV. We specify UP to be any row where the price is higher than the previous row.

The output might look something like this:

This might not be what you had expected though. The price is jumping all over the place. But it is exactly what we asked for. The currency LKR had several runs of increasing value, the longest stretch seems to be from the 17th to the 19th of April.

But what about the 12th? That's just one row!?! Well, trust me that the value on the 11th was lower than on the 12th, but we didn't actually ask to get that first row returned. We referenced it in the DEFINE clause, but it isn't in the PATTERN clause and so it isn't returned.

Expanding the example

In order to make the output a little more informative, let's expand the pattern we are looking for and introduce a specialized type of calculated field called MEASURES.

SELECT * FROM sdr_rates
MATCH_RECOGNIZE(
    PARTITION BY currency
    ORDER BY tstamp
    MEASURES CLASSIFIER() AS clf,
    MATCH_NUMBER() AS mnum
    ALL ROWS PER MATCH
    PATTERN (strt up+)
    DEFINE up AS LAG(value)<value
)

We have introduced a new item, strt, in the PATTERN clause, but it isn't defined in the DEFINE clause. This might be unintuitive, but any clause not defined will reluctantly match all rows. So now, we return the "first" row of the pattern returned, and we can see the increase from start to finish. In other words, the 11th should be included in the result.

The MEASURES field defines two extra columns, using special match recognize related functions. CLASSIFIER() actually returns which part of the pattern the row matched - in our case either strt or up. MATCH_NUMBER() enumerates the matches, so that we can easily see which rows relate to each other.

The resulting table is as follows:

Going further

Patterns can be a lot more advanced, we could for instance find W-patterns by defining down, and searching for PATTERN ( STRT (DOWN UP){2,}).

SELECT * FROM sdr_rates
MATCH_RECOGNIZE(
    PARTITION BY currency
    ORDER BY tstamp
    MEASURES CLASSIFIER() AS clf,
    MATCH_NUMBER() AS mnum,
    MAX(up.value) AS max_up,
    FIRST(tstamp) AS start_at,
    FINAL LAST(tstamp) AS end_at,
    FINAL COUNT(1) AS n_days
    ALL ROWS PER MATCH
    PATTERN ( strt ( down up ){2,} )
    DEFINE up AS LAG(value)<value,
        down AS LAG(value)>value
);

In addition to the expanded pattern, we now define some more measures: The max value of rows classified as UP, the first timestamp of each match, the (final) last timestamp of the match (measures by default do not look ahead, the FINAL keyword finds the actual last value. Similarly, we take the final row count of each match.

References

Row pattern matching was originally introduced by Oracle in 12c. It became part of ANSI SQL 2016, but it is not widely implemented. Snowflake is one of only a handful of other databases that have this feature.

Snowflake reference: https://docs.snowflake.com/en/sql-reference/constructs/match_recognize.html

Oracle reference: https://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG8956