Window function (SQL)
Function over multiple rows in SQL
- 6 min read
- 4 chapters
Related on Lably
Through SQL, Aggregate function and MySQL
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):
<span id="mwHQ"></span><span class="k" id="mwHg">SELECT</span><span class="w" id="mwHw"> </span><span class="n" id="mwIA">depname</span><span class="p" id="mwIQ">,</span><span class="w" id="mwIg"> </span><span class="n" id="mwIw">empno</span><span class="p" id="mwJA">,</span><span class="w" id="mwJQ"> </span><span class="n" id="mwJg">salary</span><span class="p" id="mwJw">,</span><span class="w" id="mwKA"> </span><span class="n" id="mwKQ">avg</span><span class="p" id="mwKg">(</span><span class="n" id="mwKw">salary</span><span class="p" id="mwLA">)</span><span class="w" id="mwLQ"> </span><span class="k" id="mwLg">OVER</span><span class="w" id="mwLw"> </span><span class="p" id="mwMA">(</span><span class="k" id="mwMQ">PARTITION</span><span class="w" id="mwMg"> </span><span class="k" id="mwMw">BY</span><span class="w" id="mwNA"> </span><span class="n" id="mwNQ">depname</span><span class="p" id="mwNg">)</span><span class="w" id="mwNw"> </span><span class="k" id="mwOA">FROM</span><span class="w" id="mwOQ"> </span><span class="n" id="mwOg">empsalary</span><span class="p" id="mwOw">;</span>
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. 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).
Sign in to keep reading
3 more chapters are free with a Lably account.
Syntax
According to the PostgreSQL documentation, a window function has the syntax of one of the following:
<span id="mwVg"></span><span class="n" id="mwVw">function_name</span><span class="w" id="mwWA"> </span><span class="p" id="mwWQ">([</span><span class="k" id="mwWg">expression</span><span class="w" id="mwWw"> </span><span class="p" id="mwXA">[,</span><span class="w" id="mwXQ"> </span><span class="k" id="mwXg">expression</span><span class="w" id="mwXw"> </span><span class="mf" id="mwYA">...</span><span class="w" id="mwYQ"> </span><span class="p" id="mwYg">]])</span><span class="w" id="mwYw"> </span><span class="k" id="mwZA">OVER</span><span class="w" id="mwZQ"> </span><span class="n" id="mwZg">window_name</span> <span class="n" id="mwZw">function_name</span><span class="w" id="mwaA"> </span><span class="p" id="mwaQ">([</span><span class="k" id="mwag">expression</span><span class="w" id="mwaw"> </span><span class="p" id="mwbA">[,</span><span class="w" id="mwbQ"> </span><span class="k" id="mwbg">expression</span><span class="w" id="mwbw"> </span><span class="mf" id="mwcA">...</span><span class="w" id="mwcQ"> </span><span class="p" id="mwcg">]])</span><span class="w" id="mwcw"> </span><span class="k" id="mwdA">OVER</span><span class="w" id="mwdQ"> </span><span class="p" id="mwdg">(</span><span class="w" id="mwdw"> </span><span class="n" id="mweA">window_definition</span><span class="w" id="mweQ"> </span><span class="p" id="mweg">)</span> <span class="n" id="mwew">function_name</span><span class="w" id="mwfA"> </span><span class="p" id="mwfQ">(</span><span class="w" id="mwfg"> </span><span class="o" id="mwfw">*</span><span class="w" id="mwgA"> </span><span class="p" id="mwgQ">)</span><span class="w" id="mwgg"> </span><span class="k" id="mwgw">OVER</span><span class="w" id="mwhA"> </span><span class="n" id="mwhQ">window_name</span> <span class="n" id="mwhg">function_name</span><span class="w" id="mwhw"> </span><span class="p" id="mwiA">(</span><span class="w" id="mwiQ"> </span><span class="o" id="mwig">*</span><span class="w" id="mwiw"> </span><span class="p" id="mwjA">)</span><span class="w" id="mwjQ"> </span><span class="k" id="mwjg">OVER</span><span class="w" id="mwjw"> </span><span class="p" id="mwkA">(</span><span class="w" id="mwkQ"> </span><span class="n" id="mwkg">window_definition</span><span class="w" id="mwkw"> </span><span class="p" id="mwlA">)</span>
where window_definition has syntax:
<span id="mwmQ"></span><span class="p" id="mwmg">[</span><span class="w" id="mwmw"> </span><span class="n" id="mwnA">existing_window_name</span><span class="w" id="mwnQ"> </span><span class="p" id="mwng">]</span> <span class="p" id="mwnw">[</span><span class="w" id="mwoA"> </span><span class="k" id="mwoQ">PARTITION</span><span class="w" id="mwog"> </span><span class="k" id="mwow">BY</span><span class="w" id="mwpA"> </span><span class="k" id="mwpQ">expression</span><span class="w" id="mwpg"> </span><span class="p" id="mwpw">[,</span><span class="w" id="mwqA"> </span><span class="mf" id="mwqQ">...</span><span class="p" id="mwqg">]</span><span class="w" id="mwqw"> </span><span class="p" id="mwrA">]</span> <span class="p" id="mwrQ">[</span><span class="w" id="mwrg"> </span><span class="k" id="mwrw">ORDER</span><span class="w" id="mwsA"> </span><span class="k" id="mwsQ">BY</span><span class="w" id="mwsg"> </span><span class="k" id="mwsw">expression</span><span class="w" id="mwtA"> </span><span class="p" id="mwtQ">[</span><span class="w" id="mwtg"> </span><span class="k" id="mwtw">ASC</span><span class="w" id="mwuA"> </span><span class="o" id="mwuQ">|</span><span class="w" id="mwug"> </span><span class="k" id="mwuw">DESC</span><span class="w" id="mwvA"> </span><span class="o" id="mwvQ">|</span><span class="w" id="mwvg"> </span><span class="k" id="mwvw">USING</span><span class="w" id="mwwA"> </span><span class="k" id="mwwQ">operator</span><span class="w" id="mwwg"> </span><span class="p" id="mwww">]</span><span class="w" id="mwxA"> </span><span class="p" id="mwxQ">[</span><span class="w" id="mwxg"> </span><span class="k" id="mwxw">NULLS</span><span class="w" id="mwyA"> </span><span class="p" id="mwyQ">{</span><span class="w" id="mwyg"> </span><span class="k" id="mwyw">FIRST</span><span class="w" id="mwzA"> </span><span class="o" id="mwzQ">|</span><span class="w" id="mwzg"> </span><span class="k" id="mwzw">LAST</span><span class="w" id="mw0A"> </span><span class="p" id="mw0Q">}</span><span class="w" id="mw0g"> </span><span class="p" id="mw0w">]</span><span class="w" id="mw1A"> </span><span class="p" id="mw1Q">[,</span><span class="w" id="mw1g"> </span><span class="mf" id="mw1w">...</span><span class="p" id="mw2A">]</span><span class="w" id="mw2Q"> </span><span class="p" id="mw2g">]</span> <span class="p" id="mw2w">[</span><span class="w" id="mw3A"> </span><span class="n" id="mw3Q">frame_clause</span><span class="w" id="mw3g"> </span><span class="p" id="mw3w">]</span>frame_clause has the syntax of one of the following:
<span id="mw5A"></span><span class="p" id="mw5Q">{</span><span class="w" id="mw5g"> </span><span class="k" id="mw5w">RANGE</span><span class="w" id="mw6A"> </span><span class="o" id="mw6Q">|</span><span class="w" id="mw6g"> </span><span class="k" id="mw6w">ROWS</span><span class="w" id="mw7A"> </span><span class="o" id="mw7Q">|</span><span class="w" id="mw7g"> </span><span class="k" id="mw7w">GROUPS</span><span class="w" id="mw8A"> </span><span class="p" id="mw8Q">}</span><span class="w" id="mw8g"> </span><span class="n" id="mw8w">frame_start</span><span class="w" id="mw9A"> </span><span class="p" id="mw9Q">[</span><span class="w" id="mw9g"> </span><span class="n" id="mw9w">frame_exclusion</span><span class="w" id="mw-A"> </span><span class="p" id="mw-Q">]</span> <span class="p" id="mw-g">{</span><span class="w" id="mw-w"> </span><span class="k" id="mw_A">RANGE</span><span class="w" id="mw_Q"> </span><span class="o" id="mw_g">|</span><span class="w" id="mw_w"> </span><span class="k" id="mwAQA">ROWS</span><span class="w" id="mwAQE"> </span><span class="o" id="mwAQI">|</span><span class="w" id="mwAQM"> </span><span class="k" id="mwAQQ">GROUPS</span><span class="w" id="mwAQU"> </span><span class="p" id="mwAQY">}</span><span class="w" id="mwAQc"> </span><span class="k" id="mwAQg">BETWEEN</span><span class="w" id="mwAQk"> </span><span class="n" id="mwAQo">frame_start</span><span class="w" id="mwAQs"> </span><span class="k" id="mwAQw">AND</span><span class="w" id="mwAQ0"> </span><span class="n" id="mwAQ4">frame_end</span><span class="w" id="mwAQ8"> </span><span class="p" id="mwARA">[</span><span class="w" id="mwARE"> </span><span class="n" id="mwARI">frame_exclusion</span><span class="w" id="mwARM"> </span><span class="p" id="mwARQ">]</span>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. 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.
NAME | ------------ Aaron| <-- Preceding (unbounded) Amelia| Andrew| 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:
<span id="mwAUg"></span><span class="w" id="mwAUk"> </span><span class="k" id="mwAUo">SELECT</span> <span class="w" id="mwAUs"> </span><span class="n" id="mwAUw">LAG</span><span class="p" id="mwAU0">(</span><span class="n" id="mwAU4">name</span><span class="p" id="mwAU8">,</span><span class="w" id="mwAVA"> </span><span class="mi" id="mwAVE">1</span><span class="p" id="mwAVI">)</span><span class="w" id="mwAVM"> </span> <span class="w" id="mwAVQ"> </span><span class="k" id="mwAVU">OVER</span><span class="p" id="mwAVY">(</span><span class="k" id="mwAVc">ORDER</span><span class="w" id="mwAVg"> </span><span class="k" id="mwAVk">BY</span><span class="w" id="mwAVo"> </span><span class="n" id="mwAVs">name</span><span class="p" id="mwAVw">)</span><span class="w" id="mwAV0"> </span><span class="ss" id="mwAV4">"prev"</span><span class="p" id="mwAV8">,</span> <span class="w" id="mwAWA"> </span><span class="n" id="mwAWE">name</span><span class="p" id="mwAWI">,</span><span class="w" id="mwAWM"> </span> <span class="w" id="mwAWQ"> </span><span class="n" id="mwAWU">LEAD</span><span class="p" id="mwAWY">(</span><span class="n" id="mwAWc">name</span><span class="p" id="mwAWg">,</span><span class="w" id="mwAWk"> </span><span class="mi" id="mwAWo">1</span><span class="p" id="mwAWs">)</span><span class="w" id="mwAWw"> </span> <span class="w" id="mwAW0"> </span><span class="k" id="mwAW4">OVER</span><span class="p" id="mwAW8">(</span><span class="k" id="mwAXA">ORDER</span><span class="w" id="mwAXE"> </span><span class="k" id="mwAXI">BY</span><span class="w" id="mwAXM"> </span><span class="n" id="mwAXQ">name</span><span class="p" id="mwAXU">)</span><span class="w" id="mwAXY"> </span><span class="ss" id="mwAXc">"next"</span> <span class="w" id="mwAXg"> </span><span class="k" id="mwAXk">FROM</span><span class="w" id="mwAXo"> </span><span class="n" id="mwAXs">people</span> <span class="w" id="mwAXw"> </span><span class="k" id="mwAX0">ORDER</span><span class="w" id="mwAX4"> </span><span class="k" id="mwAX8">BY</span><span class="w" id="mwAYA"> </span><span class="n" id="mwAYE">name</span>
The result query contains the following values:
| PREV | NAME | NEXT | |----------|----------|----------| | (null)| Aaron| Amelia| | Aaron| Amelia| Andrew| | Amelia| Andrew| James| | Andrew| 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.
Support for particular database implementations was added as follows:
- Oracle - version 8.1.6 in 2000.
- PostgreSQL - version 8.4 in 2009.
- MySQL - version 8 in 2018.
- MariaDB - version 10.2 in 2016.
- SQLite - release 3.25.0 in 2018.