(This contrasts with an aggregate function, which returns a single value for multiple rows.)
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):[3]Output: 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 with an empty OVER() clause), then the entire result set is treated as a single partition.
According to the PostgreSQL documentation, a window function has the syntax of one of the following:[4]where window_definition has syntax:frame_clause has the syntax of one of the following:frame_start and frame_end can be UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, or UNBOUNDED FOLLOWING.
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: Window functions were incorporated into the SQL:2003 standard and had functionality expanded in later specifications.