SQL window function

From Wikipedia, the free encyclopedia
Jump to navigation Jump to search

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

  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. arXiv:1208.0086. 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.
  6. ^ "PostgreSQL: Documentation: 9.1: Window Functions". www.postgresql.org. Retrieved 2017-09-26.