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. (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.
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 (example from the PostgreSQL documentation): 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 <code>PARTITION BY</code> clause groups rows into partitions, and the function is applied to each partition separately. If the <code>PARTITION BY</code> clause is omitted (such as with an empty <code>OVER()</code> clause), 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 (after the <code>GROUP BY</code> clause and non-window aggregate functions, for example).
According to the PostgreSQL documentation, a window function has the syntax of one of the following:where <code>window_definition</code> has syntax:<code>frame_clause</code> has the syntax of one of the following:<code>frame_start</code> and <code>frame_end</code> can be <code>UNBOUNDED PRECEDING</code>, <code>offset PRECEDING</code>, <code>CURRENT ROW</code>, <code>offset FOLLOWING</code>, or <code>UNBOUNDED FOLLOWING</code>. <code>frame_exclusion</code> can be <code>EXCLUDE CURRENT ROW</code>, <code>EXCLUDE GROUP</code>, <code>EXCLUDE TIES</code>, or <code>EXCLUDE NO OTHERS</code>.
<code>expression</code> 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 (unbounded) Amelia| Andrew| James| Jill| Johnny| <-- 1st preceding row Michael| <-- Current row Nick| <-- 1st following row Ophelia| Zach| <-- Following (unbounded) In the above table, the next query extracts for each row the values of a window with one preceding and one following row:
The result query contains the following values: | PREV | NAME | NEXT | |----------|----------|----------| | (null)| Aaron| Amelia| | Aaron| Amelia| Andrew| | Amelia| Andrew| James| | Andrew| James| Jill| | James| Jill| Johnny| | Jill| Johnny| Michael| | Johnny| Michael| Nick| | Michael| Nick| Ophelia| | Nick| Ophelia| Zach| | Ophelia| Zach| (null)|
Window functions were incorporated into the standard and had functionality expanded in later specifications.
Support for particular database implementations was added as follows: