InSQL, awindow function oranalytic function[1] is a function which uses values from one or multiplerows to return a value for each row. (This contrasts with anaggregate 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.[2]
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 thePostgreSQL documentation):[3]
SELECTdepname,empno,salary,avg(salary)OVER(PARTITIONBYdepname)FROMempsalary;
Output:
depname | empno | salary | avg ----------+-------+--------+----------------------develop | 11 | 5200 | 5020.0000000000000000develop | 7 | 4200 | 5020.0000000000000000develop | 9 | 4500 | 5020.0000000000000000develop | 8 | 6000 | 5020.0000000000000000develop | 10 | 5200 | 5020.0000000000000000personnel | 5 | 3500 | 3700.0000000000000000personnel | 2 | 3900 | 3700.0000000000000000sales | 3 | 4800 | 4866.6666666666666667sales | 1 | 5000 | 4866.6666666666666667sales | 4 | 4800 | 4866.6666666666666667(10 rows)
ThePARTITION BY clause groups rows into partitions, and the function is applied to each partition separately. If thePARTITION BY clause is omitted (such as with an emptyOVER() clause), then the entireresult set is treated as a single partition.[4] For this query, the average salary reported would be the average taken over all rows.
Window functions are evaluated after aggregation (after theGROUP BY clause and non-window aggregate functions, for example).[1]
According to the PostgreSQL documentation, a window function has the syntax of one of the following:[4]
function_name([expression[,expression...]])OVERwindow_namefunction_name([expression[,expression...]])OVER(window_definition)function_name(*)OVERwindow_namefunction_name(*)OVER(window_definition)
wherewindow_definition has syntax:
[existing_window_name][PARTITIONBYexpression[,...]][ORDERBYexpression[ASC|DESC|USINGoperator][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}BETWEENframe_startANDframe_end[frame_exclusion]
frame_start andframe_end can beUNBOUNDED PRECEDING,offset PRECEDING,CURRENT ROW,offset FOLLOWING, orUNBOUNDED FOLLOWING.frame_exclusion can beEXCLUDE CURRENT ROW,EXCLUDE GROUP,EXCLUDE TIES, orEXCLUDE 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.[5][6][7][8] A window function defines aframe orwindow of rows with a given length around the current row, and performs a calculation across the set of data in the window.[9][10]
NAME |------------ Aaron| <-- Preceding (unbounded) Andrew| Amelia| 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:
SELECTLAG(name,1)OVER(ORDERBYname)"prev",name,LEAD(name,1)OVER(ORDERBYname)"next"FROMpeopleORDERBYname
The result query contains the following values:
| PREV | NAME | NEXT ||----------|----------|----------|| (null)| 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| (null)|
Window functions were incorporated into theSQL:2003 standard and had functionality expanded in later specifications.[11]
Support for particular database implementations was added as follows: