Grouping by ROLLUP, CUBE, and GROUPING SETS

1. Grouping by ROLLUP, CUBE, and GROUPING SETS

Before we wrap up this chapter on aggregating time series data, let's take a quick look at how three additional operators can help us refine our aggregates.

2. Hierarchical rollups with ROLLUP

The first operator is ROLLUP, which works best with hierarchical data. This SQL code shown performs a grouped aggregation on a table called Table and calculates the sum of events on each day, grouped by month. It then adds a WITH ROLLUP clause to add a sub-total row for each distinct month in the result set. WITH ROLLUP comes after GROUP BY. ROLLUP will take each combination of the first column--month in this case--followed by each matching value in the second column, and so on, showing our aggregates for each. ROLLUP is great for a summary of hierarchical data, but if you don't have that, you can use one of the other grouping operators.

3. Cartesian aggregation with CUBE

For cases where we want to see the full combination of all aggregations between columns, CUBE is at our disposal. The CUBE operator works just like ROLLUP, sliding in right after the GROUP BY clause. WITH CUBE specifies that the result set should include all possible combinations of the columns listed in the GROUP BY clause. On the right, we can see a sample of the rows returned from a CUBE operation. In any realistic scenario, we will probably get far more results than we really want. That's where the third option comes into play.

4. Define grouping sets with GROUPING SETS

With GROUPING SETS, we control the levels of aggregation and can include any combination of aggregates we need. For example, in this query, we define two grouping sets: one on the combination of incident type and office, and an empty grouping set to give us the grand total. This results in one row with the grand total followed by each of the specific combinations of incident type and office. If we then want to include separate aggregates like all of the incident types broken out regardless of office, we can add those as additional grouping sets. You can create any ROLLUP or CUBE operation with a series of GROUPING SETS, but for larger numbers of columns, it's definitely quicker to write WITH ROLLUP than to specify all of the grouping sets needed to replicate its behavior.

5. Let's practice!

Now that we've seen ROLLUP, CUBE, and GROUPING SETS in action, let's try them out on a few problems.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.