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 |
The above...
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Recommended Reading
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.
Bellamkonda S, Bozkaya T, Ghosh B, Gupta A, Haydu J, Subramanian S, Witkowski A. Analytic functions in Oracle 8i. Technical report, Oracle Corporation. 2000.
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.
Cao Y, Chan C-Y, Li J, Tan K-L. Optimization of analytic window functions. Proc VLDB Endow. 2012;5(11):1244–55.
Eisenberg A, Melton J, Kulkarni K, Michels J-E, Zemke F. SQL:2003 has been published. SIGMOD Rec. 2004;33(1):119–26.
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.
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.
PostgreSQL Global Development Group. Window Functions, Chapter 3.5. PostgreSQL Documentation. http://www.postgresql.org/docs/9.5/static/tutorial-window.html.
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.
Zemke F. What’s new in SQL:2011. SIGMOD Rec. 2012;41(1):67–73.
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–56
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2018 Springer Science+Business Media, LLC, part of Springer Nature
About this entry
Cite this entry
Chan, CY. (2018). Window Operator in RDBMS. In: Liu, L., Özsu, M.T. (eds) Encyclopedia of Database Systems. Springer, New York, NY. https://doi.org/10.1007/978-1-4614-8265-9_80628
Download citation
DOI: https://doi.org/10.1007/978-1-4614-8265-9_80628
Published:
Publisher Name: Springer, New York, NY
Print ISBN: 978-1-4614-8266-6
Online ISBN: 978-1-4614-8265-9
eBook Packages: Computer ScienceReference Module Computer Science and Engineering