All-round Tool for Cloud and On-Prem Development
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. Being part of SQL it should be part of any Cloud developer’s toolkit to avoid pulling the data to the client (which of course applies to on-
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.
From the middle of 2019 Oracle Autonomous DB Cloud offering includes development environments as APEX, ORDS and SQL Developer Web, making it very easy to develop Cloud applications where the raw data need never be dragged out of the database to client- or application server code. In such an environment SQL becomes the important API for working with your data, and as such you should use all of the powers of SQL to make your Cloud apps be very efficient. (Same principles has always applied on-premises, just even more relevant in the Cloud.)
Whichever type of app you have, join me for a ramble of different use cases of row pattern matching to start getting practice in a very powerful SQL tool.
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?
Examples given of ticker search, grouping like tabibitosan, gap and overlap finding, hierarchical child count.
In the Cloud and On-Prem SQL
Quite often you have data in rows and need to display or output it in columns, or vice versa. You can also often have delimited data in strings and need them separated out, or conversely aggregate rows of data into delimited strings. In a Cloud era it becomes more and more relevant to avoid dragging raw data from the database to do such twisting in client-
or application server code. Instead the time-tried powers of SQL (re-using your skills from on-premises apps) can be used to do the Data Twist of pivoting, unpivoting, splitting and aggregating.
Humans often can get a better overview of data when it is presented in columns, while working with the data in sets in SQL is easier having the data in rows. Being able to twist the data from columns to rows and vice versa is very useful. Learn different ways to do the Data Twist in this session:
The SELECT statement has builtin capabilities for doing this for fixed number of columns – UNPIVOT to turn columns into rows, PIVOT to turn rows into columns. For older database versions these can be simulated using row generators for unpivoting and grouping for pivoting.
When the number of columns are not known, you’re typically dealing with data in a string separated by some delimiter like comma, semicolon or tab. Other techniques can be used for turning separated data into rows and turning rows into separated data.
All of these methods have been useful for on-premises applications a long time and have been tried and tested there. From the middle of 2019 Oracle Autonomous DB Cloud offering includes development environments as APEX, ORDS and SQL Developer Web, making it very easy to develop Cloud applications where the raw data need never be dragged out of the database to client- or application server code. In such an environment SQL becomes the important API for working with your data, and as such you should use all of the powers of SQL to make your Cloud apps be very efficient.
Different techniques will be discussed and demonstrated:
UNPIVOT with single or multi-column dimensions
Unpivoting with row generators
PIVOT with single or multi-column dimensions, with or without grouping
Pivoting with GROUP BY and CASE
Turning comma-separated data into rows
ODCI dynamic table function parser
LISTAGG to turn rows into comma-separated data
Alternative methods for string aggregation
Kim Berg Hansen is a database developer from Middelfart in Denmark . Originally wanting to work with electronics, he almost coincidentally tried computer programming and discovered where his talent lay, as the programs he did worked well – unlike the electronics projects he soldered that often failed. After that experience he progressed from Commodore Basic on VIC-20 over Modula-2 and C at Odense University to Oracle SQL and PL/SQL, which last two languages he now has worked with extensively since the year 2000. His professional passion is to work with data inside the database utilizing the SQL language to the fullest to achieve the best application experience for the users.
Kim shares his experience and knowledge by blogging, presenting at various Oracle User Group conferences, and being the SQL quizmaster at the Oracle Dev Gym. His motivation is when peers go “now I understand” after his explanations, or when end users “can’t live without” his application coding. He is certified Oracle OCE in SQL as well as awarded Oracle ACE Director. Outside the coding world Kim is married, loves to cook, and is a card-carrying member of Danish Beer Enthusiasts association.