SQL for Data Warehousing and Analytics

Picture courtesy of Pixabay Welcome to the third post in this deep-dive series on SQL pattern matching using the  MATCH_RECOGNIZE  feature that is part of Database 12c. In the  first part of this series  we looked at a wide range of topics including ensuring query consistency, how to correctly use predicates and how to manage sorting. In the second part we looked at using the built-in measures to understand how a data set is matched to a pattern. In this post I am going to review the concepts of greedy and reluctant quantifiers. I will breakdown this down into a number of areas: 1) Overview of regular expressions, 2) understanding quantifiers, and 3) greedy vs. reluctant quantifiers. The examples in this post use the built-in measures to help show the difference between greedy and reluctant matching. If you are not familiar with the  MATCH_NUMBER()  function or the  CLASSIFIER()  function then please take some time to read the  se...

We are starting to see a significant change in the way we analyze data as a result of the growth of interest in big data and the newer concept of Internet of Things. Ever since databases were first created everyone has been obsessed, quite rightly so, with ensuring queries returned the correct answer - i.e. precise, accurate answers. This key requirement is derived from the need to run operational, transactional applications. If we check our bank balance online we want the figure we see to be accurate right down to the last cent and for a good reason. Yet increasingly both as part of our online as well as offline experiences we deal with numbers that are not 100% accurate and somehow we manage to make good use of these approximate answers. Here are a couple of examples of where we already using approximations: route planning on our smartphones and crowd counting information in newspapers: How long does it take to get from A to B? If I need to get to San Francisco airport from our off...

Welcome to the second post in this deep dive series on SQL pattern matching using the new MATCH_RECOGNIZE feature that is part of Database 12c. In the first part of this series  we looked at the areas of ensuring query consistency, how to correctly use predicates and how to manage sorting. In this post I am going to review the two built-in measures that we have provided to help you understand how your data set is mapped to the pattern that you have defined. This post will breakdown into three areas: 1) a review of the built-in measures, 2) understanding how to control the output (number of rows returned) and lastly I will bring these two topics together with some examples.   Built-in measures We provide two built-in measures to help you understand how your data maps to a pattern: MATCH_NUMBER() and CLASSIFER () . These two functions are typically included as part of the MEASURE clause. It is possible to use them within other areas of the MATCH_RECOGNIZE clause but, in many case...