Database Reference

In-Depth Information

•
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.

Search WWH ::

Custom Search