{"history":[],"article":{"id":"d98a849e-cc57-4207-8324-c4a1353bf585","title":"Window function (SQL)","slug":"window-function-sql","content":":::summary\n:::\n\n# \nIn [SQL](https://bliptext.com/articles/sql), a **window function** or **analytic function** is a function which uses values from one or multiple [rows](https://bliptext.com/articles/row-database) to return a value for each row.\n(This contrasts with an [aggregate function](https://bliptext.com/articles/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.\n\n# Example\nAs 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](https://bliptext.com/articles/postgresql) documentation):\nOutput:\ndepname | empno | salary | avg\n--+---++-- develop | 11 | 5200 | 5020.0000000000000000\ndevelop | 7 | 4200 | 5020.0000000000000000\ndevelop | 9 | 4500 | 5020.0000000000000000\ndevelop | 8 | 6000 | 5020.0000000000000000\ndevelop | 10 | 5200 | 5020.0000000000000000\npersonnel | 5 | 3500 | 3700.0000000000000000\npersonnel | 2 | 3900 | 3700.0000000000000000\nsales | 3 | 4800 | 4866.6666666666666667\nsales | 1 | 5000 | 4866.6666666666666667\nsales | 4 | 4800 | 4866.6666666666666667\n(10 rows)\nThe clause groups rows into partitions, and the function is applied to each partition separately.\nIf the clause is omitted (such as with an empty clause), then the entire [result set](https://bliptext.com/articles/result-set) is treated as a single partition.\nFor this query, the average salary reported would be the average taken over all rows.\nWindow[ ](https://bliptext.com/articles/group-by-sql)functions[ ](https://bliptext.com/articles/group-by-sql)are[ ](https://bliptext.com/articles/group-by-sql)evaluated[ ](https://bliptext.com/articles/group-by-sql)after[ ](https://bliptext.com/articles/group-by-sql)aggregation[ ](https://bliptext.com/articles/group-by-sql)(after[ ](https://bliptext.com/articles/group-by-sql)the[ ](https://bliptext.com/articles/group-by-sql)[ ](https://bliptext.com/articles/group-by-sql)clause[ ](https://bliptext.com/articles/group-by-sql)and[ ](https://bliptext.com/articles/group-by-sql)non-window[ ](https://bliptext.com/articles/group-by-sql)aggregate[ ](https://bliptext.com/articles/group-by-sql)functions,[ ](https://bliptext.com/articles/group-by-sql)for[ ](https://bliptext.com/articles/group-by-sql)example).\n\n# Syntax\nAccording to the PostgreSQL documentation, a window function has the syntax of one of the following: where has syntax: has the syntax of one of the following: and can be , , , , or.\ncan be, , , or.\nrefers to any expression that does not contain a call to a window function.\nNotation:\n\n# Example\nWindow functions allow access to data in the records right before and after the current record.\nA 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.\nNAME |\nAaron| <-- Preceding (unbounded)\nAndrew|\nAmelia|\nJames|\nJill|\nJohnny| <-- 1st preceding row\nMichael| <-- Current row\nNick| <-- 1st following row\nOphelia|\nZach| <-- Following (unbounded)\nIn 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:\n| PREV | NAME | NEXT |\n|--|--|--| | (null)| Aaron| Andrew|\n| Aaron| Andrew| Amelia|\n| Andrew| Amelia| James|\n| Amelia| James| Jill|\n| James| Jill| Johnny|\n| Jill| Johnny| Michael|\n| Johnny| Michael| Nick|\n| Michael| Nick| Ophelia|\n| Nick| Ophelia| Zach|\n| Ophelia| Zach| (null)|\n\n# History\nWindow functions were introduced to the [SQL:2003](https://bliptext.com/articles/sql-2003) standard and had functionality expanded in later specifications.\nSupport for particular database implementations was added as follows:","current_revision":null,"created_by":"kACnuQmH5k9Nr5VNVEpVeg7XpvUmMctD","created_at":"2025-01-26T10:25:39.397Z","updated_at":"2025-01-26T10:25:39.397Z","search_vector":"'function':2 'sql':3 'window':1","search_content":" \n \n\n# \nIn [SQL](https://bliptext.com/articles/sql), a **window function** or **analytic function** is a function which uses values from one or multiple [rows](https://bliptext.com/articles/row-database) to return a value for each row.\n(This contrasts with an [aggregate function](https://bliptext.com/articles/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.\n\n# Example\nAs 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](https://bliptext.com/articles/postgresql) documentation):\nOutput:\ndepname | empno | salary | avg\n--+---++-- develop | 11 | 5200 | 5020.0000000000000000\ndevelop | 7 | 4200 | 5020.0000000000000000\ndevelop | 9 | 4500 | 5020.0000000000000000\ndevelop | 8 | 6000 | 5020.0000000000000000\ndevelop | 10 | 5200 | 5020.0000000000000000\npersonnel | 5 | 3500 | 3700.0000000000000000\npersonnel | 2 | 3900 | 3700.0000000000000000\nsales | 3 | 4800 | 4866.6666666666666667\nsales | 1 | 5000 | 4866.6666666666666667\nsales | 4 | 4800 | 4866.6666666666666667\n(10 rows)\nThe clause groups rows into partitions, and the function is applied to each partition separately.\nIf the clause is omitted (such as with an empty clause), then the entire [result set](https://bliptext.com/articles/result-set) is treated as a single partition.\nFor this query, the average salary reported would be the average taken over all rows.\nWindow[ ](https://bliptext.com/articles/group-by-sql)functions[ ](https://bliptext.com/articles/group-by-sql)are[ ](https://bliptext.com/articles/group-by-sql)evaluated[ ](https://bliptext.com/articles/group-by-sql)after[ ](https://bliptext.com/articles/group-by-sql)aggregation[ ](https://bliptext.com/articles/group-by-sql)(after[ ](https://bliptext.com/articles/group-by-sql)the[ ](https://bliptext.com/articles/group-by-sql)[ ](https://bliptext.com/articles/group-by-sql)clause[ ](https://bliptext.com/articles/group-by-sql)and[ ](https://bliptext.com/articles/group-by-sql)non-window[ ](https://bliptext.com/articles/group-by-sql)aggregate[ ](https://bliptext.com/articles/group-by-sql)functions,[ ](https://bliptext.com/articles/group-by-sql)for[ ](https://bliptext.com/articles/group-by-sql)example).\n\n# Syntax\nAccording to the PostgreSQL documentation, a window function has the syntax of one of the following: where has syntax: has the syntax of one of the following: and can be , , , , or.\ncan be, , , or.\nrefers to any expression that does not contain a call to a window function.\nNotation:\n\n# Example\nWindow functions allow access to data in the records right before and after the current record.\nA 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.\nNAME |\nAaron| <-- Preceding (unbounded)\nAndrew|\nAmelia|\nJames|\nJill|\nJohnny| <-- 1st preceding row\nMichael| <-- Current row\nNick| <-- 1st following row\nOphelia|\nZach| <-- Following (unbounded)\nIn 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:\n| PREV | NAME | NEXT |\n|--|--|--| | (null)| Aaron| Andrew|\n| Aaron| Andrew| Amelia|\n| Andrew| Amelia| James|\n| Amelia| James| Jill|\n| James| Jill| Johnny|\n| Jill| Johnny| Michael|\n| Johnny| Michael| Nick|\n| Michael| Nick| Ophelia|\n| Nick| Ophelia| Zach|\n| Ophelia| Zach| (null)|\n\n# History\nWindow functions were introduced to the [SQL:2003](https://bliptext.com/articles/sql-2003) standard and had functionality expanded in later specifications.\nSupport for particular database implementations was added as follows:","revisionCount":0},"hasMore":false,"nextCursor":null}