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

↑ 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.

0.00

(0 votes)

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