Implementing Analytics with Greenplum UAP - Getting Started with Greenplum for Big Data Analytics - page 144

• zPartitions ( groupings ): A window function calculates the results for a row
with respect to its partition
• Ordering of rows within a window partition : Some window functions such
as RANK require ordering
• Framing : For ordered result sets, you can define a window frame that ana-
lyzes each row with respect to the rows directly above or below it
All window functions must have an OVER() clause. The window function specifies
the window of data to which the function applies it defines:
• Window partitions using the PARTITION BY clause
• Ordering within a window partition using the ORDER BY clause
• Framing within a window partition ( ROWS / RANGE clauses)
The PARTITION BY clause
The PARTITION BY clause performs the following functions:
• It can be used by all window functions. However, it is not a required clause.
Windows that do not use the PARTITION BY clause present the entire result
set as a single window partition.
• It organizes the result set into groupings based on the unique values of the
specified expression or column.
• It allows the function to be applied to each partition independently.
The ORDER BY clause
The ORDER BY clause is used to order the resulting data set based on an expression
or column. It is always allowed in windows functions and is required by some window
functions, including RANK . The ORDER BY clause specifies ordering within a window
partition.
The RANK function is a built-in function that calculates the rank of a row in an ordered
group of values. Rows with equal values for the ranking criteria receive the same
rank. The number of tied rows is added to the rank number to calculate the next rank
value. In this case, ranks may not be consecutive numbers.