Window function (SQL)


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

Example

As an example, here is a query which uses a window function to compare the salary of each employee with the average salary of their department :
SELECT depname, empno, salary, avg OVER 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

The PARTITION BY clause groups rows into partitions, and the function is applied to each partition separately. If the PARTITION BY clause is omitted, then the entire result set is treated as a single partition. For this query, the average salary reported would be the average taken over all rows.
Window functions are evaluated after aggregation.

Syntax

According to the PostgreSQL documentation, a window function has the syntax of one of the following:
function_name OVER window_name
function_name OVER
function_name OVER window_name
function_name OVER
where window_definition has syntax:
]
]
frame_clause has the syntax of one of the following:
frame_start
BETWEEN frame_start AND frame_end
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.
Notation:
Window functions allow access to data in the records right before and after the current record. A window function defines a frame or window of rows with a given length around the current row, and performs a calculation across the set of data in the window.
NAME |
------------
Aaron| <-- Preceding
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following
In the above table, the next query extracts for each row the values of a window with one preceding and one following row:

SELECT
LAG
OVER "prev",
name,
LEAD
OVER "next"
FROM people
ORDER BY name

The result query contains the following values:
| PREV | NAME | NEXT |
|----------|----------|----------|
| | Aaron| Andrew|
| Aaron| Andrew| Amelia|
| Andrew| Amelia| James|
| Amelia| James| Jill|
| James| Jill| Johnny|
| Jill| Johnny| Michael|
| Johnny| Michael| Nick|
| Michael| Nick| Ophelia|
| Nick| Ophelia| Zach|
| Ophelia| Zach| |

History

Window functions were incorporated into the SQL:2003 standard and had functionality expanded in later specifications.
Support for particular database implementations was added as follows: