This section covers advanced SQL techniques for in-database analytics within Greenplum.
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 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
SELECT
list, between theSELECT
andFROM
keywords of a query.
Unlike aggregate functions...