Windowing analytical

Definition

An analytic function computes values over a group of rows and returns a single result for each row. This is different from an aggregate function, which returns a single result for a group of rows.

Parameters

1. Partition by (+add):

   • Def: Specify the column for grouping.

2. Order by (+add):

   • Def: Specify the column for ordering.

3. Function:

   • Def: Specify the function to use.

4. On column:

   • Def: Specify the column to use for function.

5. N:

   • Def: Specify the size of rows.

6. Column output:

   • Def: Specify the name of the column.

Additionnal Comments

Functions:

1. cume_dist:

   • Def: Returns cumulative distribution of a value within a group of values; that is, the percentage of partition values less than or equal to the value in the current row.

2. lag:

   • Def: Returns the value of the umpteenth row before the current row.

3. lead:

   • Def: Returns the value of the umpteenth row after the current row.