118 High-Function Business Intelligence in e-business
OLAP functions provide the ability to return the following information in a query
result:
? Ranking with RANK & DENSE_RANK.
? Numbering with ROW_NUMBER.
? Aggregation with existing column functions such as MAX, MIN, AVG etc.
Key to OLAP functions is the ability to define the set of rows over which the
function is applied, and the sequence in which the function is applied. This set of
rows is called a window. When an OLAP function is used with a column function,
like AVG, SUM, MAX, etc., the target rows can be further refined, relative to the
current row, as either a range, or a number of rows preceding and following the
current row. For example, within a window partitioned by month, a moving
average can be calculated over the previous three month period.
Besides windowing, the ability to group sets of rows is critical to OLAP
functionality. ROLLUP and CUBE are extensions to the GROUP BY clause to
provide OLAP functionality. ROLLUP and CUBE are called super-groups.
We discuss OLAP functionality as:
? Ranking, numbering and aggregate functions
? GROUPING capabilities ROLLUP & CUBE
We then follow it with examples.
3.3.1 Ranking, numbering and aggregation functions
Figure 3-3, Figure 3-4, and Figure 3-5 provide an overview of the syntax of some
of the OLAP functions.
Chapter 3. DB2 UDB’s statistics, analytic, and OLAP functions 119
Figure 3-3 Ranking, numbering and aggregate functions
120 High-Function Business Intelligence in e-business
Figure 3-4 Window partition and window order clauses
Chapter 3. DB2 UDB’s statistics, analytic, and OLAP functions 121
Figure 3-5 Window aggregation group clause
122 High-Function Business Intelligence in e-business
A brief explanation of some of the OLAP functions follows, with examples
provided as appropriate.
RANK
The RANK function assigns a sequential rank of a row within a window.
The RANK of a row is defined as one plus the number of rows that strictly
precede the row.
Rows that are not distinct within the ordering of the window are assigned equal
ranks.
If two or more rows are not distinct with respect to the ordering, then there will be
one or more gaps in the sequential rank numbering. That is, the results of RANK
may have gaps in the numbers resulting from duplicate values.
DENSE_RANK
Like the RANK function, DENSE_RANK assigns a sequential rank to a row in a
window. However, its DENSE_RANK is one plus the number of rows preceding it
that are distinct with respect to the ordering. Therefore, there will be no gaps in
the sequential rank numbering, with ties being assigned the same rank.
ROWNUMBER
ROWNUMBER computes the sequential row number of the row within the
window defined by an ordering clause (if one is specified), starting with 1 for the
first row and continuing sequentially to the last row in the window.
If an ordering clause, ORDER BY, is not specified in the window, the row
numbers are assigned to the rows in arbitrary order as returned by the
sub-select.
PARTITION BY
The PARTITION BY clause allows for subdividing the window into partitions. A
partitioning-expression
is used to define the partitioning of the result set.
ORDER BY
The ORDER BY clause defines the ordering of rows within a window that
determines the value of the OLAP function or the meaning of the ROW values in
the window-aggregation-group-clause (see the following section concerning the
window-aggregation-group).
The ORDER BY clause does not define the ordering of the query result set.
..................Content has been hidden....................

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