Find Your Match – MATCH_RECOGNIZE to the rescue!

Row pattern matching is useful for recognizing patterns in rows of data, as the name implies. But the uses of MATCH_RECOGNIZE extends beyond the obvious - it can be applied to grouping consecutive data, merging overlapping periods, bin fitting, child number calculations in hierarchies, and more.From version 12c and onwards, you have the ability to search efficiently for patterns within your rows of data using a syntax similar to searching for characters patterns in strings with regular expressions, but instead of searching text it searches for data patterns.This is useful for pattern recognition like finding ups and downs in values over time (i.e. stock tickers) or transaction patterns in accounts that may look like fraudulent behaviour. But it can also be used for grouping consecutive data, finding gaps, finding or merging overlapping periods, calculating number of children in hierarchical data, and more.Join me for a ramble through row pattern matching, first briefly covering syntax of row pattern matching in general, then proceeding to show more details by demonstrating various use cases, where each use case highlights different ways of using different parts of the MATCH_RECOGNIZE clause.Topics to be covered include:• DEFINE - what to define, PREV, NEXT, FIRST, LAST, referring other variables, order of evaluation• PATTERN - regular expression style syntax• MEASURES - RUNNING, FINAL, CLASSIFIER, MATCH_NUMBER• ONE / ALL ROWS PER MATCH• AFTER MATCH - where to go to next?• ORDER BY• PARTITION BY