Additional OLAP Examples

This section provides additional examples using the OLAP functions.

Both start and end points of a window may vary as intermediate result rows are processed. For example, computing a cumulative sum involves a window with the start point fixed at the first row of each partition and an end point that slides along the rows of the partition to include the current row.

As another example, both the start and end points of the window can be variable yet define a constant number of rows for the entire partition. Such a construction lets users compose queries that compute moving averages; for example, a SQL query that returns a moving three-day average stock price.

• Example: Window Functions in Queries
  This query lists all products shipped in July and August 2005 and the cumulative shipped quantity by shipping date:
• Example: Window With Multiple Functions
  This query defines a single (named) window and computes multiple function results over it:
• Example: Calculate Cumulative Sum
  This query calculates a cumulative sum of salary per department and ORDER BY start_date.
• Example: Calculate Moving Average
  This query generates the moving average of sales in three consecutive months. The size of the window frame is three rows: two preceding rows plus the current row. The window slides from the beginning to the end of the partition.
• Example: ORDER BY Results
  In this example, the top ORDER BY clause of a query is applied to the final results of a window function. The ORDER BY in a window clause is applied to the input data of a window function.
• Example: Multiple Aggregate Functions in a Query
  This example calculates aggregate values against different windows in a query.
• Example: Window Frame Comparing ROWS and RANGE
  This query compares ROWS and RANGE. The data contain duplicate ROWS per the ORDER BY clause.
• Example: Window Frame Excludes Current Row
  In this example, you can define the window frame to exclude the current row. The query calculates the sum over four rows, excluding the current row.
• Example: Window Frame for RANGE
  This query illustrates the RANGE window frame. The number of rows used in the summation is variable.
• Example: Unbounded Preceding and Unbounded Following
  In this example, the window frame can include all rows in the partition. The query calculates max(sales) sale over the entire partition (no duplicate rows in a month).
• Example: Default Window Frame for RANGE
  This query illustrates the default window frame for RANGE: