Window function (SQL)
{{Short description|Function over multiple rows in SQL}}
{{For|the term used in signal processing|Window function}}
In SQL, a window function or analytic function{{Cite web|title=Analytic function concepts in Standard SQL {{!}} BigQuery|url=https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts|access-date=2021-03-23|website=Google Cloud|language=en}} 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.{{Cite web|title=Window Functions|url=https://sqlite.org/windowfunctions.html|access-date=2021-03-23|website=sqlite.org}}
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 (example from the PostgreSQL documentation):{{Cite web|date=2021-02-11|title=3.5. Window Functions|url=https://www.postgresql.org/docs/13/tutorial-window.html|access-date=2021-03-23|website=PostgreSQL Documentation|language=en}}
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) 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
(10 rows)
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.{{Cite web|date=2021-02-11|title=4.2. Value Expressions|url=https://www.postgresql.org/docs/13/sql-expressions.html|access-date=2021-03-23|website=PostgreSQL Documentation|language=en}} For this query, the average salary reported would be the average taken over all rows.
Window functions are evaluated after aggregation (after the GROUP BY clause and non-window aggregate functions, for example).
Syntax
According to the PostgreSQL documentation, a window function has the syntax of one of the following:
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
where window_definition has syntax:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ 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 } BETWEEN frame_start AND frame_end [ frame_exclusion ]
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:
- Brackets [] indicate optional clauses
- Curly braces {} indicate a set of different possible options, with each option delimited by a vertical bar |
Example
Window functions allow access to data in the records right before and after the current record.{{Cite journal|last1=Leis|first1=Viktor|last2=Kundhikanjana|first2=Kan|last3=Kemper|first3=Alfons|last4=Neumann|first4=Thomas|date=June 2015|title=Efficient Processing of Window Functions in Analytical SQL Queries|journal=Proc. VLDB Endow.|volume=8|issue=10|pages=1058–1069|doi=10.14778/2794367.2794375|issn=2150-8097}}{{Cite journal|last1=Cao|first1=Yu|last2=Chan|first2=Chee-Yong|last3=Li|first3=Jie|last4=Tan|first4=Kian-Lee|date=July 2012|title=Optimization of Analytic Window Functions|journal=Proc. VLDB Endow.|volume=5|issue=11|pages=1244–1255|arxiv=1208.0086|doi=10.14778/2350229.2350243|issn=2150-8097}}{{Cite news|date=2013-11-03|title=Probably the Coolest SQL Feature: Window Functions|language=en-US|work=Java, SQL and jOOQ.|url=https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/|access-date=2017-09-26}}{{Cite news|date=2013-10-31|title=Window Functions in SQL - Simple Talk|language=en-US|work=Simple Talk|url=https://www.red-gate.com/simple-talk/sql/t-sql-programming/window-functions-in-sql/|access-date=2017-09-26}} 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.{{Cite web|last=|first=|date=|title=SQL Window Functions Introduction|url=https://drill.apache.org/docs/sql-window-functions-introduction/|archive-url=|archive-date=|access-date=|website=Apache Drill}}{{Cite web|title=PostgreSQL: Documentation: Window Functions|url=https://www.postgresql.org/docs/current/tutorial-window.html|access-date=2020-04-04|website=www.postgresql.org|language=en}}
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:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
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)|
History
Window functions were incorporated into the SQL:2003 standard and had functionality expanded in later specifications.{{Cite web|title=Window Functions Overview|url=https://mariadb.com/kb/en/window-functions-overview/|access-date=2021-03-23|website=MariaDB KnowledgeBase}}
Support for particular database implementations was added as follows:
- Oracle - version 8.1.6 in 2000.{{Cite web | title=Oracle 8i Release 2 (8.1.6) New Features|url=https://docs.oracle.com/cd/A81042_01/DOC/server.816/a76962/816.htm | access-date=2025-01-23 | website=www.oracle.com }}{{Cite web | title=Analytic Functions in Oracle 8i|url=http://infolab.stanford.edu/infoseminar/archive/SpringY2000/speakers/agupta/paper.pdf | access-date=2025-01-23 | website=www.stanford.edu }}
- PostgreSQL - version 8.4 in 2009.{{Cite web | title=PostgreSQL Release 8.4 |url=https://www.postgresql.org/docs/8.4/release-8-4.html | access-date=2024-03-10 | website=www.postgresql.org|date=24 July 2014 }}
- MySQL - version 8 in 2018.{{Cite web |title=MySQL :: What's New in MySQL 8.0? (Generally Available) |url=https://dev.mysql.com/blog-archive/whats-new-in-mysql-8-0-generally-available/ |access-date=2022-11-21 |website=dev.mysql.com}}{{Cite web |title=MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax |url=https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html |website=dev.mysql.com}}
- MariaDB - version 10.2 in 2016.{{Cite web | title=MariaDB 10.2.0 Release Notes |url=https://mariadb.com/kb/en/mariadb-1020-release-notes/ | access-date=2024-03-10 | website=mariadb.com}}
- SQLite - release 3.25.0 in 2018.{{cite web |title=SQLite Release 3.25.0 On 2018-09-15 |url=https://www.sqlite.org/releaselog/3_25_0.html |website=www.sqlite.org |access-date=5 February 2025}}