Introduction
CUBE
Instruction
As you could see, the following rows were created:
GROUP BY CUBE(location, gender, risk) = GROUP BY location, gender, risk + GROUP BY location, gender + GROUP BY gender, risk + GROUP BY location, risk + GROUP BY location + GROUP BY gender + GROUP BY risk + GROUP BY ()
Quite a lot! For n columns passed in as parameters, CUBE creates 2^n grouping levels, while ROLLUP only creates n + 1 levels:
| No. columns | No. grouping levels | |
|---|---|---|
| ROLLUP | CUBE | |
| 1 | 2 | 2 |
| 2 | 3 | 4 |
| 3 | 4 | 8 |
| 4 | 5 | 16 |
| 5 | 6 | 32 |
| 6 | 7 | 64 |
You need to be aware that CUBE can significantly lower the performance of your queries. As few as three columns in CUBE create eight different types of groupings. Even though a query with CUBE is faster than separate grouping queries merged with UNION, performance can still be an issue for large tables.
Exercise
Show the sum of damage_repair_cost (as sum_damage_repair_cost) for all possible grouping combinations based on the year, month, and cause columns.
Show the following columns in the query result: year, month, cause, and sum_damage_repair_cost.
Stuck? Here's a hint!
Use the following template:
SELECT ... FROM ... GROUP BY CUBE (x, y, z)
