Chapter 3. DB2 UDB’s statistics, analytic, and OLAP functions 125
? FOLLOWING specifies either the range or number of rows following 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.
3.3.2 GROUPING capabilities ROLLUP & CUBE
The result of a GROUP BY operation is a set of groups of rows. Each row in this
result represents the set of rows for which the grouping-expression is satisfied.
Complex forms of the GROUP BY clause include grouping-sets and
super-groups.
Figure 3-7 GROUP BY clause
A grouping sets specification allows multiple grouping clauses to be specified in a
single statement. This can be thought of as a union of two or more groups of
rows into a single result set. It is logically equivalent to the union of multiple
subselects with the group by clause in each subselect corresponding to one
grouping set. A grouping set can be a single element, or can be a list of elements
delimited by parentheses, where an element is either a grouping expression, or a
super-group.
GROUP BY a is equivalent to GROUP BY GROUPING SETS ((a))
GROUP BY a,b,c is equivalent to GROUP BY GROUPING SETS ((a,b,c))
In terms of OLAP functions we will confine our discussion to the two
super-groups ROLLUP and CUBE, whose syntax is shown in Figure 3-8.
Note: For grouping, all null values from a
grouping-expression
are considered
equal.
126 High-Function Business Intelligence in e-business
Figure 3-8 Super Groups ROLLUP & CUBE
ROLLUP
A ROLLUP group is an extension to the GROUP BY clause that produces a
result set that contains
sub-total
4
rows in addition to the ’regular’ grouped rows.
Sub-total
rows are ’super-aggregate’ rows that contain further aggregates whose
values are derived by applying the same column functions that were used to
obtain the grouped rows. A ROLLUP grouping is a series of
grouping-sets
.
For example:
GROUP BY ROLLUP (a,b,c)
is equivalent to
GROUP BY GROUPING SETS
(
(a,b,c)
(a,b)
(a)
()
)
Notice that the
n
elements of the ROLLUP translate to
n
+1 grouping sets.
4
These are called sub-total rows, because that is their most common use. However, any column
function can be used for the aggregation including MAX and AVG.
..................Content has been hidden....................

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