Jump to content

SQL window function

Add links
From Wikipedia, the free encyclopedia
This is an old revision of this page, as edited by DrStrauss (talk | contribs) at 10:46, 27 September 2017 (Added tags to the page using Page Curation (technical)). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

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". Java, SQL and jOOQ. 2013-11-03. Retrieved 2017-09-26.
  4. ^ "Window Functions in SQL - Simple Talk". Simple Talk. 2013-10-31. Retrieved 2017-09-26.
  5. ^ "SQL Window Functions Introduction". Apache Drill. {{cite web}}: Cite has empty unknown parameter: |dead-url= (help)
  6. ^ "PostgreSQL: Documentation: 9.1: Window Functions". www.postgresql.org. Retrieved 2017-09-26.
Stub icon

This computer science article is a stub. You can help Wikipedia by expanding it.

Retrieved from "https://en.wikipedia.org/w/index.php?title=SQL_window_function&oldid=802621149"