1. ROLLUP
Great job using CUBE! ROLLUP is the next operator that we will introduce to facilitate the execution of OLAP queries.
2. Table renting_extended
Again, we will focus on the table renting_extended to demonstrate the ROLLUP operator.
3. Query with ROLLUP
The ROLLUP operator is always used in combination with a GROUP BY statement. Parentheses around the two column names have to be used.
This query returns a table with different levels of aggregation. We get the number of movie rentals for each combination of country and genre, the number of rentals for each country and the total number of rentals.
4. Query with ROLLUP
In the ROLLUP operator the order of the column names between parentheses is important. The first column is aggregated on two levels, but the second column is aggregated on only one level.
Here we have the resulting table. Note that, now, the aggregation for each genre is not reported. This is the key difference with the CUBE operator, where the aggregates for all combinations of values of the selected attribute types are shown.
5. Order in ROLLUP
When we change the order and put genre first and country second we obtain this table. The last two lines are now changed: instead of the aggregation for country we now have the aggregation for genre.
6. Summary ROLLUP
The motivation for this type of aggregation is to present data on different levels of detail. ROLLUP returns aggregates for a hierarchy of value. When we go from aggregation for each country and genre to aggregation for each country alone, and then to the total number of movie rentals, we drop a level of detail at each step.
Because we have these levels of aggregation the order of the column names in the ROLLUP clause is important.
7. Number of rentals and ratings
We can also include more than one aggregation in the table. Here we count the number of movie rentals and the number of ratings. In the result table we can see the corresponding numbers in columns n_rentals and n_ratings.
8. Let's practice!
Now, it's your turn to aggregate data with the ROLLUP operator!