René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

Analytical Functions in Oracle  
select ... analyticfunction (...) over (partition by ...) .. select ... analyticfunction (...) over (order by ...) .. select ... analyticfunction (...) over (partition by ... order by ...) .. Types of Analytical FunctionsRanking Functions
See also dense rank vs rank vs row_number.
Window Functions
Window Functions consist of all agregate functions (sum,max,avg...)
Cumulative SUM shows how to use
sum to calculate a comulative sum.
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 nonnull value.
Reporting Functions
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
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.
ExamplesEstablishing 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
Analytical functions are processed after where, group by and having, but before
the order by. Hence, analytical functions are only permitted in the select list and in the group by clause. See also
order of select processing.
