type
Post
Created date
Oct 2, 2022 07:26 PM
category
Data Science
tags
Data Warehousing
status
Published
Language
English
From
School
summary
slug
password
Author
Jason Ching Yuen Siu
Priority
Featured
Featured
Cover
Origin
Type
URL
Youtube
Youtube
icon
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);
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F1aa9d391-bf4d-4f97-8dd7-20f5c214cce1%2FUntitled.png%3Ftable%3Dblock%26id%3Dfb3a09b8-235c-4688-96b9-5daceea7a3b1%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725724800000%26signature%3DogpfKY53--R0ebVfojebgeLlS-BNB-t65OekyfBvYIA?table=block&id=fb3a09b8-235c-4688-96b9-5daceea7a3b1&cache=v2)
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);
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fe27b29a1-3d17-4bcc-8d86-d8ac37eafbf1%2FUntitled.png%3Ftable%3Dblock%26id%3Db621d776-629b-4c07-9111-ff68541a4a55%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725724800000%26signature%3DzxfIu9zGsdhyb48apbfD53jYv4cfQAJx6-L6HU7QoCg?table=block&id=b621d776-629b-4c07-9111-ff68541a4a55&cache=v2)
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);
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2F8de61c85-e2bf-4383-9f74-a1f6df2b64c1%2FUntitled.png%3Ftable%3Dblock%26id%3D776d514b-4440-4ca7-8a54-332173d6bee1%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725724800000%26signature%3DiXTnill3qL3C0ol4ckaWS2q-3T_jsCYKIfQw2Sk7SLI?table=block&id=776d514b-4440-4ca7-8a54-332173d6bee1&cache=v2)
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);
![notion image](https://www.notion.so/image/https%3A%2F%2Ffile.notion.so%2Ff%2Ff%2Fe1c5ba0e-562a-49b0-8505-ce5e38fc061b%2Fcab71232-e7d2-4c2b-9314-ffb22724a44a%2FUntitled.png%3Ftable%3Dblock%26id%3D3dbab430-6821-45ed-99a7-7fb2a31f645d%26spaceId%3De1c5ba0e-562a-49b0-8505-ce5e38fc061b%26expirationTimestamp%3D1725724800000%26signature%3D9GFi-zIi1-v7bqZP6RpvQDm5IgtUTvkSpJaCjgCHuPg?table=block&id=3dbab430-6821-45ed-99a7-7fb2a31f645d&cache=v2)
Rollup vs. Cube
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.
- Author:Jason Siu
- URL:https://jason-siu.com/article%2Ff5739aa7-08bd-4e14-a4e4-0665d4a57549
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts