To demonstrate the power of
MATCH_RECOGNIZE I will use the Unemployment by State data set from BLS for this purpose. Use of this dataset was inspired by a blogpost by Kelsey Gonzales, Coronavirus Unemployment.
The goal of this analysis to take a look at the Unemployment data set and identify the States that are showing a decreasing Unemployment to date, and identify the State with the longest streak of decrease in Unemployment, month over month.
The Unemployment data look as following.
We will use MATCH_RECOGNIZE in Snowflake to find the decreasing Unemployment Rate pattern
PARTITIONed by State
MATCH_RECOGNIZE clause is used to search for a pattern in single field value spanning over multiple records. This clause enables you to define patterns using regular expressions and aggregate methods to verify and extract values from the match.
The following example shows the basic structure of a
SELECT STATE, MONTHS_OF_DECREASING_UNEMPLOYMENT, STREAK_START_DATE, STREAK_END_DATE FROM UNEMPLOYMENT_RATE MATCH_RECOGNIZE( PARTITION BY STATE ORDER BY YEARMONTH MEASURES FIRST(YEARMONTH) AS STREAK_START_DATE, LAST(YEARMONTH) AS STREAK_END_DATE, COUNT(*) AS MONTHS_OF_DECREASING_UNEMPLOYMENT ONE ROW PER MATCH PATTERN (DECREASE+$) DEFINE INCREASE AS UNEMPLOYMENT_RATE > LAG(UNEMPLOYMENT_RATE), DECREASE AS UNEMPLOYMENT_RATE < LAG(UNEMPLOYMENT_RATE) ) ORDER BY MONTHS_OF_DECREASING_UNEMPLOYMENT DESC;
PARTITION BY allows the match to be keyed and partitioned over a column name. A match will happen over every unique key specified by the partition statement. This enables a single query to be matched over all the keys and generate separate matches, one to every key.
MEASURES is used to define the projected values from the match using aggregate methods. For example,
LAST(YEARMONTH) AS STREAK_END_DATE will output the last
value that was found over all events that matched pattern named
DECREASE into field name
The pattern defines the regular expression of events to be searched over the data stream. Pattern variables are user-defined and separated by spaces. Regex modifiers like
* can be used to modify the frequency of a variable when matching events.
PATTERN (INIT+ (REVERSAL | MODIFICATION))
The pattern on this example defines a variable INIT at least once, followed by a concatenation of either REVERSAL or MODIFICATION.
Pattern quantifiers are used to change how a pattern is mapped in the data stream, defining how many times a pattern needs to match to be valid. The following quantifiers are available:
'*' - Zero or more times
'+' - One or more times
'?' – Zero or one time
'|' - One pattern or another
'^' - Regex Start Anchor
'$' - Regex End Anchor
This example defines INIT 1 time, and REVERSAL 1 or more times, ending in REVERSAL (Regex End Anchor $).
DEFINE specifies the rules used to match a pattern variable to an event. The rules are Boolean expressions over aggregated values from the sequence of records.
DEFINE INCREASE AS UNEMPLOYMENT_RATE > LAG(UNEMPLOYMENT_RATE), DECREASE AS UNEMPLOYMENT_RATE < LAG(UNEMPLOYMENT_RATE)
This example defines rules INCREASE and DECREASE.
INCREASE defines the pattern where current
UNEMPLOYMENT_RATE is greater than the previous
DECREASE defines the pattern where current
UNEMPLOYMENT_RATE is less than the previous
The following aggregate methods can be used in MEASURES and DEFINE:
MIN– The minimum number aggregated so far.
MAX– The maximum number aggregated so far.
FIRST– The first value aggregated.
LAST– The last value aggregated so far.
MATCH_RECOGNIZE query will produce the following results, which show the States with a Streak decreasing Unemployment Rate.
We see that Rhode Island has a decreasing Unemployment Rate starting July 2021.