Window Operator in RDBMS

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