The CUBE
function is a subset of GROUPING SET. It takes different expressions (columns)
as input. It will then produce a set for every combination of input
expressions. It will also include the empty set.
ROLLUP is
another type of GROUPING SET. You use this function if the columns of interest
form a hierarchy. Then only the combinations that make sense based on the
hierarchy are chosen for grouping sets. Higher precedence columns which come earlier
in the list passed to ROLLUP get matched with all combinations of lower
priority columns. But not the other way around. Obviously, this produces a lot
less of grouping sets than the CUBE function.
To help makes sense of the results of a ROLLUP,
you can use SELECT the GROUPING function. It takes a single input element as a
parameter. It will return a 0 if that element is part is part of the grouping
set. It returns 1 otherwise. Another similar function is the GROUPING_ID. It
takes a list of grouped columns as input. It returns a bitmap of type integer
representing the columns that are part of the grouping set. The rightmost bit
is the rightmost element.
