FIT3003 - W8 - CUBE & ROLLUP in SQL (With partial CUBE and partial ROLLUP) (W8)

type

Post

Created date

Oct 2, 2022 07:26 PM

1. CUBE & Partial CUBE

What is a full cube?

We need to understand what it means by a full cube cube(A,B,C) in the first place. cube(A,B,C) means that each of the attributes A, B and C (and their combinations) will have a sub-total in the query results; the attribute will have a (null) entry in the respective column. When an attribute (e.g. attribute A) is taken out from the cube, as in group by A cube(B,C), it means that attribute A will not have a sub-total anymore, as it is not in the cube any longer.

What is a partial cube?

Partial cube or partial rollup means that one or more attributes are taken out from the cube and rollup. For example, if the full cube is group by cube(A,B,C) where A, B and C are attributes, an example of a partial cube is attribute A, which is taken out from the cube but is still within the group by group by A cube(B,C).

Let’s compare group by cube(A,B,C) ( i.e., FULL CUBE) with group by A cube(B,C) ( i.e., PARTIAL CUBE). Where:

Example of a full cube

The following SQL uses three attributes (e.g. channel_desc, calendar_month_desc in the cube. The results are shown below.

The results show all possible sub-totals are included: individual Cahnnel sales, Country , Time and combinations of these


SELECT channel_desc, 
calendar_month_desc AS calendar,
country_id AS co, 
SUM(amount_sold) AS SALES$
FROM sales, customers, times, channels
WHERE sales.time_id = times.time_id
AND sales.cust_id = customers.cust_id
AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_id IN ('UK', 'US')
GROUP BY CUBE(channel_desc, calendar_month_desc, country_id);

Example of a partial cube


SELECT channel_desc, calendar_month_desc AS calendar,
country_id AS co, SUM(amount_sold) AS SALES$
FROM sales, customers, times, channels
WHERE sales.time_id = times.time_id
AND sales.cust_id = customers.cust_id
AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_id IN ('UK', 'US')
GROUP BY channel_desc, CUBE(calendar_month_desc, country_id);

2. ROLLUP & Partial ROLLUP

What is a partial Rollup?

Rollup to include only some of the subtotals GROUP BY expr1, ROLLUP (expr2, expr3)

First-level subtotals aggregating across expr3 for each combination of expr2 and expr1

Second-level subtotals aggregating across expr2 and expr3 for each expr1 value

No grand total aggregating across all expr1, expr2, expr

Example of a full rollup

The following SQL uses three attributes (e.g. channel_desc, calendar_month_desc in the cube. The results are shown below.

The results show all possible sub-totals are included: individual Channel sales, Country , Time and combinations of these


SELECT channel_desc, 
calendar_month_desc AS calendar,
country_id AS co, 
SUM(amount_sold) AS SALES$
FROM sales, customers, times, channels
WHERE sales.time_id = times.time_id
AND sales.cust_id = customers.cust_id
AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_id IN ('UK', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc,
 country_id);

Example of a partial rollup


SELECT channel_desc, calendar_month_desc AS calendar,
country_id AS co, SUM(amount_sold) AS SALES$
FROM sales, customers, times, channels
WHERE sales.time_id = times.time_id
AND sales.cust_id = customers.cust_id
AND sales.channel_id = channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_id IN ('UK', 'US')
GROUP BY channel_desc, ROLLUP(calendar_month_desc, 
country_id);

Rollup vs. Cube

Understanding ROLLUP in SQL - Stack Overflow

1. Order matters for `ROLLUP` !

GROUP BY ROLLUP(channel_desc, calendar_month_desc, country_id); In this line of code, the order matters! The result will be different if you putGROUP BY ROLLUP(calendar_month_desc, channel_desc, country_id); That is, result of Rollup of A, B, C and Rollup of A, C, B are different.

2. `CUBE` and `ROLLUP` are the extension of `GROUP BY` clause.

CUBE generates information in cross-tabulation format within a single query.

ROLLUP generates aggregations at increasing levels of granularity from the most detailed to a grand total.

Partial CUBE vs Partial ROLLUP

Partial CUBE resembles partial ROLLUP in that you can limit it to certain dimensions and precede it with columns outside the CUBE operator. In this case, subtotals of all possible combinations are limited to the dimensions within the cube list (in parentheses), and they are combined with the preceding items in the GROUPBY list.