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