Weblog for the AMIS Technology corner
Posts tagged analytical functions
Oracle Database 11gR2 – New analytical function NTH_VALUE
Sep 15th
You are probably familiar with the FIRST_VALUE and LAST_VALUE analytical functions that were introduced some time ago into the Oracle RDBMS, in the 9iR2 release I believe (or at least that is when they made their way into the Standard Edition). These values are used to find the first respectively last value in a window in a partition that has been ordered in a certain way.
Oracle introduces a new, related function in 11gR2, called NTH_VALUE. Instead of simply the first or last value in an analytical window, we can ask for a specific row number, such as 2nd row (ND_VALUE?), the 7th or the 223th. This article shows the syntax for this new function.
Reaching Milestones – some Analytical SQL-etics
Jun 28th
Last week, this weblog hit the 9M post reads mark. Since we started technology.amis.nl/blog in July 2004, it took us some 5 years to get to that point. And I started to wonder when we reached earlier milestones, like 100k, 1M and 5M. This took me to some SQL puzzles that before I started in anger might have been difficult to solve, but turned out to be rather straightforward – thanks to Analytical functions.
In this article a brief report on how to reconstruct history using Running Sums and a bit of LAG/LEAD analysis.
Simple Event Processing – Using SQL with Analytical Functions to detect traffic patterns
Oct 6th
One of the intriguing subjects at last weeks Oracle Open World conference for me was Complex Event Processing. The ability to process streams of real time events, and up to 1000s per second of them, opens up many new avenues of interesting opportunities. Detecting patterns and anomalies, aggregating and comparing – reporting only the noteworthy events or findings, thereby making sense (information) of enormous volumes of data is both great fun and tremendously useful in specific situations. The SQL like CQL language – Continuous Query Language – provides us with a syntax to analyze data streams in various ways – detecting trends and patterns, variations and aberrations. There are several key characteristics in the CEP architecture: handling large volumes of events, applying this special language for event analysis and executing the queries continuously. That last bit means that the query is registered with a stream of events, not executed at a single moment in time against the data as it is at that moment but looking for query results continously as the events keep streaming in. Query results can be reported for as long as the query registration remains active and the events keep streaming in.
Normal SQL is definitely not capable of being executed continuously nor is it particularly suited for dealing with large volumes of real time events that we typically do not want to store in a database table. The special pattern analyzing functionaliy of CQL also is not available in SQL. However – SQL can do more in this area than you may suspect. In this article I will give a small and hardly useful demonstration of those capabilities. We will investigate a stream of Car Sighting events. A Car Sighting event consists of a timestamp and the color of the car.
The analysis of the Car Sighting data stream we will do should answer the question: report all car sightings that are followed within five cars by a car of the same color. 
Read the rest of this entry »
