Database Reference
In-Depth Information
In-database
analytics
options
(Greenplum-specific)
This section covers advanced SQL techniques for in-database analytics within Green-
plum.
The following techniques will be discussed in detail:
• Windows functions
• User-defined functions and aggregates
Window functions
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
query.
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:
Search WWH ::
Custom Search