Uses of Row Pattern Matching

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.

With 12c came 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 of different use cases of row pattern matching.

Presentation will briefly cover syntax of row pattern matching in general, and then go on 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:

• 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

Examples given of ticker search, grouping like tabibitosan, gap and overlap finding, hierarchical child count.

NOTE: This talk won Best Speaker Award for Database track of ODTUG Kscope19.