Oracle Analytics Functions (note i)

Analytic functions are new types of functions that are introduced in the 9i version of the Oracle database and are continuously enhanced in later versions. Analytic functions provide the ability to aggregate reference values across rows and levels. The effects of analytic functions can be achieved using traditional SQL statements, but the implementation is more complex and less efficient.

The analytic function has three basic components: a partition clause, a sort clause, and a window-opening sentence. The basic syntax is

Function1 (ARGUMENT1,ARGUMENT2...ARGUMENTN) over    ([Partition-by-clause] [order-by-clause] [windowing-clause])-- Description--function1 is the invocation of an analytic function that accepts 0 or more parameters--the partition clause groups the data according to the values of the partitioning column, and the same columns in all the partition columns are placed in the same partition--the sort can be ascending or descending, or Nllls first and nulls can be used The last clause places the null value at the top or the end of the partition-The Open window clause specifies a subset of the data for the analytic function operation, with the following syntax:    [ROWS | RANGE] between <start expr> and <end expr>--where    <start expr> is [nubounded preceding | Current ROW | n Preceding | n preceding]     <end expr> is [nubounded following| Current ROW | n Preceding | The n following]--keyword preceding specifies the upper boundary of the Open-window statement, the following or current row clause specifies the bottom boundary--note that the parse function cannot be nested. However, you can nest the SQL statements it contains by placing them in a nested statement

List of common functions

adjourned

Oracle Analytics Functions (note i)