This section covers advanced SQL techniques for in-database analytics within Green-
The following techniques will be discussed in detail:
• Windows functions
• User-defined functions and aggregates
Window functions are a new class of functions introduced in Greenplum. The WINDOW
clause is used to define a window that can be used in the OVER() expression of a
window function such as rank or avg . For information on OLAP extensions and win-
dow functions refer to the Greenplum Database Reference guide. Window functions
allow application developers to more easily compose complex OLAP queries using
standard SQL commands. For example:
• Moving averages or sums can be calculated over various intervals.
• Aggregations and ranks can be reset as selected column values change.
• Complex ratios can be expressed in simple terms. Window functions can only
be used in the SELECT list, between the SELECT and FROM keywords of a
Unlike aggregate functions, which return a result value for each group of rows, win-
dow functions return a result value for every row, but that value is calculated with re-
spect to the rows in a particular window partition (grouping) or window frame (row po-
sition within the window).
What classifies a function as a window function is the use of an OVER clause. The
OVER clause defines the window of data to which the function will be applied.
There are three characteristics of a window function: