Chapter 3. DB2 UDB’s statistics, analytic, and OLAP functions 123
A
sort-key-expression
is an expression used in defining the ordering of the rows
within the window. This clause is required when using the RANK and
DENSE_RANK functions.
There are two sorting sequences:
? ASC — Sorts the sort-key-expression in ascending order. Null values are
considered last in the order by default since in DB2 nulls are considered high
values.
? DESC — Sorts the sort-key-expression in descending order. Null values are
considered first in the order unless NULLS LAST is specified.
Window aggregation group clause
The window-aggregation-group clause defines the window to a set of rows with a
defined ordering relative to the rows in the window.
ROWS
ROWS indicates the window is defined by counting rows.
RANGE
RANGE indicates the window is defined by an offset from a sort key.
group-start, group-between and group-end
The group start, between and group-end functions define the ROWS or RANGE
window to be some number of rows or range of rows around the current row in
the window. These functions make it possible to compute moving average types
of calculations.
group-start
Specifies the starting point for this aggregation group. The window ends at the
current row when UNBOUNDED PRECEDING or PRECEDING is specified
(more later). Specification of the group-start clause is the equivalent to a
group-between clause of the form “BETWEEN group-start AND CURRENT
ROW”.
group-between
Specifies the aggregation group start and end based on either ROWS or RANGE
that fit within the specified group-bound1 (beginning) and group-bound2
(endpoint).
group-end
Specifies the ending point of the aggregation group. The aggregation group start
is the current row. Specification of a group-end clause is the equivalent to a
group-between clause of the form “BETWEEN CURRENT ROW AND
group-end”.
124 High-Function Business Intelligence in e-business
Figure 3-6 graphically depicts the relationships among the various window
bounds that follow.
Figure 3-6 Windowing relationships
Group-bounds one and two
A discussion of group-bounds follows.
? CURRENT ROW specifies the start or end of the window as the current row.
? UNBOUNDED PRECEDING includes the entire window preceding the
current row. This can be specified with either ROWS or RANGE.
? UNBOUNDED FOLLOWING includes the entire window following the current
row. This can be specified with either ROWS or RANGE.
? PRECEDING specifies either the range or number of rows preceding the
current row as being in the window. If ROWS is specified, then
value
is a
positive integer indicating a number of rows. If RANGE is specified, then the
data type of
value
must be comparable to the type of the sort-key-expression
of the window ORDER BY clause.
Note: N
First
is always equal to 1.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset