OLAP operations: GROUPING SETS

Ra quyết định dựa trên dữ liệu với SQL

Irene Ortner

Data Scientist at Applied Statistics

Overview of OLAP operators in SQL

Extensions in SQL to facilitate OLAP operations

GROUP BY CUBE
GROUP BY ROLLUP
GROUP BY GROUPING SETS

Table renting_extended

The first few rows of the table renting_extended:

| renting_id | country  | genre  | rating |
|------------|----------|--------|--------|
| 2          | Belgium  | Drama  | 10     |
| 32         | Belgium  | Drama  | 10     |
| 203        | Austria  | Drama  | 6      |
| 292        | Austria  | Comedy | 8      |
| 363        | Belgium  | Drama  | 7      |
| .......... | ........ | ...... | ...... |

GROUP BY GROUPING SETS

Example of a query with GROUPING SETS operator:

SELECT country, 
       genre, 
       COUNT(*)
FROM rentings_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

Column names surrounded by parentheses represent one level of aggregation.
GROUP BY GROUPING SETS returns a UNION over several GROUP BY queries.

GROUPING SETS and GROUP BY queries

SELECT country, 
       genre, 
       COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (country, genre);

Count movie rentals for each unique combination of country and genre.
Expression in GROUPING SETS: (country, genre)

SELECT country, 
       genre, 
       COUNT(*)
FROM renting_extended
GROUP BY country, genre;

| country | genre  | count  |
|---------|--------|--------|
| Austria | Comedy | 2      |
| Belgium | Drama  | 15     |
| Austria | Drama  | 4      |
| Belgium | Comedy | 1      |

GROUPING SETS and GROUP BY queries

SELECT country, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (country);

Count movie rentals for each country.
Expression in GROUPING SETS: (country)

SELECT country, COUNT(*)
FROM renting_extended
GROUP BY country;

| country | count |
|---------|-------|
| Austria | 16    |
| Belgium | 6     |

GROUPING SETS and GROUP BY queries

SELECT genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS (genre);

Count movie rentals for each genre.
Expression in GROUPING SETS: (genre)

SELECT genre, COUNT(*)
FROM renting_extended
GROUP BY genre;

| country | count |
|---------|-------|
| Comedy  | 3     |
| Drama   | 19    |

GROUPING SETS and GROUP BY queries

SELECT COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ();

Total aggregation - count all movie rentals.
Expression in GROUPING SETS: ()

SELECT COUNT(*)
FROM renting_extended;

| count |
|-------|
| 22    |

Notation for GROUP BY GROUPING SETS

GROUP BY GROUPING SETS (...)

SELECT country, genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

UNION over 4 previous queries.
Combine all information of a pivot table in one query.
This query is equivalent to GROUP BY CUBE (country, genre).

Result with GROUPING SETS operator

SELECT country, genre, COUNT(*)
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (country), (genre), ());

| country | genre  |count|
|---------|--------|-----|
| NULL    | NULL   | 22  |
| Austria | Comedy | 2   |
| Belgium | Drama  | 15  |
| Austria | Drama  | 4   |
| Belgium | Comedy | 1   |
| Belgium | NULL   | 16  |
| Austria | NULL   | 6   |
| NULL    | Comedy | 3   |
| NULL    | Drama  | 19  |

Calculate number of rentals and average rating

Combine only selected aggregations:
- country and genre
- genre

Use the number of movie rentals and the average ratings for aggregation.

SELECT country, 
     genre, 
     COUNT(*), 
     AVG(rating) AS avg_rating
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (genre));

Calculate number of rentals and average rating

SELECT country, genre, COUNT(*), AVG(rating) AS avg_rating
FROM renting_extended
GROUP BY GROUPING SETS ((country, genre), (genre));

| country | genre  | count| avg_rating |
|---------|--------|------|------------|
| Austria | Comedy | 2    | 8.00       |
| Belgium | Drama  | 15   | 9.17       |
| Austria | Drama  | 4    | 6.00       |
| Belgium | Comedy | 1    | NULL       |
| NULL    | Comedy | 3    | 8.00       |
| NULL    | Drama  | 19   | 8.38       |

Let's practice!

Ra quyết định dựa trên dữ liệu với SQL