Windowing

Oracle has added to its database some functionality that allows you to calculate values based on a window. The window is a period of time. The functions in this class can be used to compute moving, cumulative, and centered aggregates. They include moving averages, moving sums, moving MIN/MAX, cumulative SUM, and LAG/LEAD. These functions create a value that is based on values that precede or follow the record. The windowing functions can be used in the SELECT and ORDER BY clauses.

The following is a syntax template that can be used for the functions:

				{Sum|Avg|Max|Min|Count|Stddev|Variance|
				First_value|Last_value} ({value expression1> | * })
				Over ({partition by <value expression2>[, …]]
				Order by <value expression3>[collate clause>] ...

Get Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial