Window function (SQL): Difference between revisions

Revision as of 02:41, 8 January 2024

Latest revision as of 05:22, 13 August 2025

(7 intermediate revisions by 6 users not shown)

Line 22:

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 if we havewith 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" />

Line 50:

== Example ==

Window functions allow access to data in the records right before and after the current record.<ref>{{Cite journal|lastlast1=Leis|firstfirst1=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|lastlast1=Cao|firstfirst1=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 |

------------

Line 63:

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="psqltsql">

SELECT

LAG(name, 1)

Line 72 ⟶ 73:

FROM people

ORDER BY name

</syntaxhighlight>
The result query contains the following values:

| PREV | NAME | NEXT |

|----------|----------|----------|

Line 87 ⟶ 89:

== History ==

Window functions were introducedincorporated ininto 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:

[[MySQL]] added support for window functions in version 8 in 2018, and [[MariaDB]] introduced first window functions with version 10.2.<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>

* [[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]] added support for window functions in- version 8 in 2018, and [[MariaDB]] introduced first window functions with version 10.2.<ref>{{Cite web |title=MySQL :: What’sWhat'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 ==