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][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

1. ↑ 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.
2. ↑ 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.
3. ↑ "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/.
4. ↑ "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/.
5. ↑ "SQL Window Functions Introduction". https://drill.apache.org/docs/sql-window-functions-introduction/.
6. ↑ "PostgreSQL: Documentation: Window Functions" (in en). https://www.postgresql.org/docs/current/tutorial-window.html.

0.00

(0 votes)

|

Retrieved from "https://handwiki.org/wiki/index.php?title=SQL_window_function&oldid=212842"