Window function (SQL)

In SQL, a window function or analytic function is a function which uses information from one or multiple rows to return a value for each row. (This contrasts with an aggregate function, which returns a single value for multiple rows.) Window functions have an OVER clause; any function without an OVER clause is not a window function, but rather an aggregate or single-row (scalar) function.

This is an query which uses a window function to compare each employee with the average salary of their department (example from the PostgreSQL documentation):

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

Output:

  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

The PARTITION BY clause groups rows into partitions, and the function is applied to each partition separately. If the PARTITION BY clause is omitted (such as if we have an empty OVER() clause), then the entire result set treated as a single partition. For this query, the average salary reported would be the average taken over all rows.

Syntax

According to the PostgreSQL documentation, a window function has the syntax of one of the following:

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

where window_definition has syntax:

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

frame_clause has the syntax of one of the following:

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

frame_start and frame_end can be UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, or UNBOUNDED FOLLOWING. frame_exclusion can be EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, or EXCLUDE NO OTHERS.

expression refers to any expression that does not contain a call to a window function.

See also

• Select (SQL)#Limiting result rows