The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference by The PostgreSQL Global Development Group Paperback (6"x9"), 454 pages ISBN 9781906966041 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 1A Buy the book here! >>> support free software

2.2.8 Window Function Calls

A window function call represents the application of an aggregate-like function over some portion of the rows selected by a query. Unlike regular aggregate function calls, this is not tied to grouping of the selected rows into a single output row--each row remains separate in the query output. However the window function is able to scan all the rows that would be part of the current row's group according to the grouping specification (PARTITION BY list) of the window function call. The syntax of a window function call is one of the following:

function_name ([expression [, expression ... ]]) OVER (
 window_definition )
function_name ([expression [, expression ... ]]) OVER window_name
function_name ( * ) OVER ( window_definition )
function_name ( * ) OVER window_name

where window_definition has the syntax

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS
 { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

and the optional frame_clause can be one of

[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end

where frame_start and frame_end can be one of

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

Here, expression represents any value expression that does not itself contain window function calls. The PARTITION BY and ORDER BY lists have essentially the same syntax and semantics as GROUP BY and ORDER BY clauses of the whole query, except that their expressions are always just expressions and cannot be output-column names or numbers. window_name is a reference to a named window specification defined in the query's WINDOW clause. Named window specifications are usually referenced with just OVER window_name, but it is also possible to write a window name inside the parentheses and then optionally supply an ordering clause and/or frame clause (the referenced window must lack these clauses, if they are supplied here). This latter syntax follows the same rules as modifying an existing window name within the WINDOW clause; see the SELECT reference page for details.

The frame_clause specifies the set of rows constituting the window frame, for those window functions that act on the frame instead of the whole partition. If frame_end is omitted it defaults to CURRENT ROW. Restrictions are that frame_start cannot be UNBOUNDED FOLLOWING, frame_end cannot be UNBOUNDED PRECEDING, and the frame_end choice cannot appear earlier in the above list than the frame_start choice--for example RANGE BETWEEN CURRENT ROW AND value PRECEDING is not allowed. The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; it sets the frame to be all rows from the partition start up through the current row's last peer in the ORDER BY ordering (which means all rows if there is no ORDER BY). In general, UNBOUNDED PRECEDING means that the frame starts with the first row of the partition, and similarly UNBOUNDED FOLLOWING means that the frame ends with the last row of the partition (regardless of RANGE or ROWS mode). In ROWS mode, CURRENT ROW means that the frame starts or ends with the current row; but in RANGE mode it means that the frame starts or ends with the current row's first or last peer in the ORDER BY ordering. The value PRECEDING and value FOLLOWING cases are currently only allowed in ROWS mode. They indicate that the frame starts or ends with the row that many rows before or after the current row. value must be an integer expression not containing any variables, aggregate functions, or window functions. The value must not be null or negative; but it can be zero, which selects the current row itself.

The built-in window functions are described in Table 7-44. Other window functions can be added by the user. Also, any built-in or user-defined aggregate function can be used as a window function.

The syntaxes using * are used for calling parameter-less aggregate functions as window functions, for example count(*) OVER (PARTITION BY x ORDER BY y). * is customarily not used for non-aggregate window functions. Aggregate window functions, unlike normal aggregate functions, do not allow DISTINCT or ORDER BY to be used within the function argument list.

Window function calls are permitted only in the SELECT list and the ORDER BY clause of the query.

More information about window functions can be found in Window Functions, section 7.19 Window Functions, section 5.2.4 Window Function Processing.