Invoking Analytic Functions

You invoke analytic functions as follows:

analytic-function (arguments) OVER(
      [ window-partition-clause ]
      [ window-order-clause [ window-frame-clause ] ] 
)

An analytic function's OVER clause can contain up to three sub-clauses, which specify how to partition and sort function input, and how to frame input with respect to the current row. Function input is the result set that the query returns after it evaluates FROM, WHERE, GROUP BY, and HAVING clauses.

Each function has its own OVER clause requirements. For example, some analytic functions do not support window order and window frame clauses.

Function Execution

An analytic function executes as follows:

  1. Takes the input rows that the query returns after it performs all joins, and evaluates FROM, WHERE, GROUP BY, and HAVING clauses.

  2. Groups input rows according to the window partition (PARTITION BY) clause. If this clause is omitted, all input rows are treated as a single partition.

  3. Sorts rows within each partition according to window order (ORDER BY) clause.

  4. If the OVER clause includes a window order clause, the function checks for a window frame clause and executes it as it processes each input row. If the OVER clause omits a window frame clause, the function treats the entire partition as a window frame.

Restrictions

  • Analytic functions are allowed only in a query's SELECT and ORDER BY clauses.
  • Analytic functions cannot be nested. For example, the following query throws an error:

    => SELECT MEDIAN(RANK() OVER(ORDER BY sal) OVER()).