SQL window function
In the SQL database query language, window functions allow access to data in the records right before and after the current record.[1][2] 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.[3][4]
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
- ^ "Probably the Coolest SQL Feature: Window Functions". Java, SQL and jOOQ. 2013-11-03. Retrieved 2017-09-26.
- ^ "Window Functions in SQL - Simple Talk". Simple Talk. 2013-10-31. Retrieved 2017-09-26.
- ^ "SQL Window Functions Introduction". Apache Drill.
{{cite web}}: Cite has empty unknown parameter:|dead-url=(help) - ^ "PostgreSQL: Documentation: 9.1: Window Functions". www.postgresql.org. Retrieved 2017-09-26.
 | This computer science article is a stub. You can help Wikipedia by expanding it. |