Content deleted Content added
Fix redirect Tag: Redirect target changed |
m →Example: lang="tsql" |
||
| (23 intermediate revisions by 15 users not shown) | |||
Line 1:
{{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'''<ref name=":1">{{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}}</ref> is a function which uses values from one or multiple [[Row (database)|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.<ref>{{Cite web|title=Window Functions|url=https://sqlite.org/windowfunctions.html|access-date=2021-03-23|website=sqlite.org}}</ref>
==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):<ref>{{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}}</ref><syntaxhighlight lang="psql">
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
</syntaxhighlight>
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.<ref name=":0">{{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}}</ref> 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 (SQL)|<code>GROUP BY</code>]] clause and non-window aggregate functions, for example).<ref name=":1" />
== Syntax ==
According to the PostgreSQL documentation, a window function has the syntax of one of the following:<ref name=":0" /><syntaxhighlight lang="psql">
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
</syntaxhighlight>where <code>window_definition</code> has syntax:<syntaxhighlight lang="psql">
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
</syntaxhighlight><code>frame_clause</code> has the syntax of one of the following:<syntaxhighlight lang="psql">
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
</syntaxhighlight><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:
* 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.<ref>{{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}}</ref><ref>{{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}}</ref><ref>{{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}}</ref><ref>{{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}}</ref> 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.<ref>{{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}}</ref><ref>{{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}}</ref>
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:
<syntaxhighlight lang="tsql">
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
</syntaxhighlight>
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.<ref>{{Cite web|title=Window Functions Overview|url=https://mariadb.com/kb/en/window-functions-overview/|access-date=2021-03-23|website=MariaDB KnowledgeBase}}</ref>
Support for particular database implementations was added as follows:
* [[Oracle Database|Oracle]] - version 8.1.6 in 2000.<ref>{{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 }}</ref><ref>{{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 }}</ref>
* [[PostgreSQL]] - version 8.4 in 2009.<ref>{{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 }}</ref>
* [[MySQL]] - version 8 in 2018.<ref>{{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}}</ref><ref>{{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}}</ref>
* [[MariaDB]] - version 10.2 in 2016.<ref>{{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}}</ref>
* [[SQLite]] - release 3.25.0 in 2018.<ref>{{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}}</ref>
== See also ==
* [[Select (SQL)#Limiting result rows|Select (SQL) § Limiting result rows]]
== References ==
{{Reflist}}{{SQL}}
[[Category:Articles with example SQL code]]
[[Category:SQL]]
| |||