René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

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

Ranking Functions

This family consists of rank, dense_rank, row_number, ntile, percent_rank and cume_dist.
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 non-null value.
first_value.html

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.

Examples

Top N Query.

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

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.