|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Analytical Functions in Oracle
select ... analytic-function (...) over (partition by ...) .. select ... analytic-function (...) over (order by ...) .. select ... analytic-function (...) over (partition by ... order by ...) ..
Types of Analytical Functions
See also dense rank vs rank vs row_number.
Window Functions consist of all agregate functions (sum,max,avg...)
Cumulative SUM shows how to use
This example show how to sum a value of the last, the current and the next row.
This is a very clever example that shows how to select the last non-null value.
These are queries that ask something like: find the sales that contribute 10 % or more to total sales. The most prominent function for this is ratio_to_report.
Lag/Lead functions make it possible to access values in other rows than the current one. The corresponding functions are lag and lead.
The partition clause
An important part in analytic functions is the Partition Clause. This example shows how to use this clause to find the last date of each month that is in a table.
Establishing a window
Analytic functions allow also to define a 'window' on the result set. This is done using rows between x preceding and y following. See an example of a rolling average here.
Permitted places for a analytical function