SQL for Data Warehousing and Analytics

image courtesy of flicker: https://c1.staticflickr.com/1/55/185807556_21c547c02e_b.jpg I have been asked a number of times during and after presenting on this topic ( SQL Pattern Matching Deep Dive ) what is the difference between the various matching options such as EMPTY MATCHES and UNMATCHED ROWS . This is the area that I am going to cover in this particular blog post, which is No 4 in this deep dive series . When determining the type of output you want MATCH_RECOGNIZE to return most developers will opt for one of the following: ONE ROW PER MATCH - each match produces one summary row. This is the default. ALL ROWS PER MATCH - a match spanning multiple rows will produce one output row for each row in the match. The default behaviour for MATCH_RECOGNIZE is to return one summary row for each match. In the majority of use cases this is probably the ideal solution. However, there are also many use cases that require more detailed information to be returned. ...

If you always wanted to try our new SQL pattern matching feature, MATCH_RECOGNIZE , but never had access to a Database 12c instance then you really need to checkout the our great new LiveSQL playground environment. LiveSQL is a great place to learn about all the new features of Database 12c along with all the existing features from earlier releases. The new tutorial is called “ Log file sessionization analysis with MATCH_RECOGNIZE ” and you can view by clicking here . This tutorial is designed to help show how you can run sessionization analysis on application logs, web logs, etc. Using a simplified table of click data you create a sessionization data set which tracks each session, the duration of the session and the number of clicks/events. It’s all fully annotated and there are links to supporting topics where you can get more information. The objective is to introduce you to some of the important keywords and concepts that are part of the  MATCH_RECOGNIZE  clause. I have put t...