SQL window function
From HandWiki
In the SQL database query language, window functions allow access to data in the records right before and after the current record.[1][2][3][4] 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.[5][6]
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)|
References
- ↑ Leis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas (June 2015). "Efficient Processing of Window Functions in Analytical SQL Queries". Proc. VLDB Endow. 8 (10): 1058–1069. doi:10.14778/2794367.2794375. ISSN 2150-8097.
- ↑ Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee (July 2012). "Optimization of Analytic Window Functions". Proc. VLDB Endow. 5 (11): 1244–1255. doi:10.14778/2350229.2350243. ISSN 2150-8097.
- ↑ "Probably the Coolest SQL Feature: Window Functions" (in en-US). Java, SQL and jOOQ.. 2013-11-03. https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/.
- ↑ "Window Functions in SQL - Simple Talk" (in en-US). Simple Talk. 2013-10-31. https://www.red-gate.com/simple-talk/sql/t-sql-programming/window-functions-in-sql/.
- ↑ "SQL Window Functions Introduction". https://drill.apache.org/docs/sql-window-functions-introduction/.
- ↑ "PostgreSQL: Documentation: Window Functions" (in en). https://www.postgresql.org/docs/current/tutorial-window.html.
 |
