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
GROUP BY, and
Each function has its own
OVER clause requirements. For example, some analytic functions do not support window order and window frame clauses.
An analytic function executes as follows:
Takes the input rows that the query returns after it performs all joins, and evaluates
GROUP BY, and
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.
Sorts rows within each partition according to window order (
ORDER BY) clause.
OVERclause includes a window order clause, the function checks for a window frame clause and executes it as it processes each input row. If the
OVERclause omits a window frame clause, the function treats the entire partition as a window frame.
- Analytic functions are allowed only in a query's
Analytic functions cannot be nested. For example, the following query throws an error:
=> SELECT MEDIAN(RANK() OVER(ORDER BY sal) OVER()).