The following techniques will be discussed in detail:
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
avg. For information on OLAP extensions and window 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
SELECTlist, between the
FROMkeywords of a query.
Unlike aggregate functions...