Encyclopedia of Database Systems

2018 Edition
| Editors: Ling Liu, M. Tamer Özsu

Window Operator in RDBMS

  • Chee-Yong Chan
Reference work entry
DOI: https://doi.org/10.1007/978-1-4614-8265-9_80628

Synonyms

Analytic OLAP functions; Window functions; Window operator

Definition

Window functions, also known as analytic OLAP functions, were introduced in SQL to simplify the formulation of many useful queries that require computations such as ranking, cumulative sums, and moving averages. Window functions are most commonly used in the select clause of SQL queries to compute additional column values for each output tuple, and they could also be used in the order-by clause to compute column values for ordering.

As an example, consider a relation Sales (branch, day, amount) which records the daily sales figures for each branch of a company. To compute the seven-day moving average sales for each branch, one could express the query using a self-join as follows:

SELECT

s1.branch, s1.day, s1.amount,

 

(SELECT AVG(x.amount) FROM

 

(SELECT s2.amount AS amount

 

FROM Sales s2

 

WHERE s2.branch = s1.branch

 

AND s2.day <= s1.day

 

ORDER BY s2.day DESC LIMIT

 

7) AS x) AS movingAvg

FROM

Sales s1

This is a preview of subscription content, log in to check access.

Recommended Reading

  1. 1.
    Bellamkonda S, Ahmed R, Witkowski A, Amor A, Zait M, Lin C-C. Enhanced subquery optimizations in Oracle. Proc VLDB Endow. 2009;2(2):1366–77.CrossRefGoogle Scholar
  2. 2.
    Bellamkonda S, Bozkaya T, Ghosh B, Gupta A, Haydu J, Subramanian S, Witkowski A. Analytic functions in Oracle 8i. Technical report, Oracle Corporation. 2000.Google Scholar
  3. 3.
    Bellamkonda S, Li H-G, Jagtap U, Zhu Y, Liang V, Cruanes T. Adaptive and big data scale parallel execution in Oracle. Proc VLDB Endow. 2013;6(11): 1102–13.CrossRefGoogle Scholar
  4. 4.
    Cao Y, Chan C-Y, Li J, Tan K-L. Optimization of analytic window functions. Proc VLDB Endow. 2012;5(11):1244–55.CrossRefGoogle Scholar
  5. 5.
    Eisenberg A, Melton J, Kulkarni K, Michels J-E, Zemke F. SQL:2003 has been published. SIGMOD Rec. 2004;33(1):119–26.CrossRefGoogle Scholar
  6. 6.
    Leis V, Kundhikanjana K, Kemper A, Neumann T. Efficient processing of window functions in analytical SQL queries. Proc VLDB Endow. 2015;8(10): 1058–69.CrossRefGoogle Scholar
  7. 7.
    Ma J, Cao Y, Wang X, Wang C, Jin C, Zhou A. PGWinFunc: optimizing window aggregate functions in PostgreSQL and its application for trajectory data. In: Proceedings of the 31st International Conference on Data Engineering; 2015. p. 1448–51.Google Scholar
  8. 8.
    PostgreSQL Global Development Group. Window Functions, Chapter 3.5. PostgreSQL Documentation. http://www.postgresql.org/docs/9.5/static/tutorial-window.html.
  9. 9.
    Ramakrishnan R, Donjerkovic D, Ranganathan A, Beyer K, Krishnaprasad M. SRQL: sorted relational query language. In: Proceedings of the 10th International Conference on Scientific and Statistical Database Management; 1998. p. 84–95.Google Scholar
  10. 10.
    Zemke F. What’s new in SQL:2011. SIGMOD Rec. 2012;41(1):67–73.CrossRefGoogle Scholar
  11. 11.
    Zuzarte C, Pirahesh H, Ma W, Cheng Q, Liu L, Wong K. WinMagic: subquery elimination using window aggregation. In: Proceedings of the ACM SIGMOD International Conference on Management of Data; 2003. p. 652–56Google Scholar

Copyright information

© Springer Science+Business Media, LLC, part of Springer Nature 2018

Authors and Affiliations

  1. 1.National University of SingaporeSingaporeSingapore