Opérations OLAP : GROUPING SETS

Prise de décision fondée sur les données avec SQL

Irene Ortner

Data Scientist at Applied Statistics

Aperçu des opérateurs OLAP en SQL

Extensions SQL pour faciliter les opérations OLAP

GROUP BY CUBE
GROUP BY ROLLUP
GROUP BY GROUPING SETS

Table renting_extended

Les premières lignes de la 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

Exemple de requête avec l'opérateur GROUPING SETS :

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

Les noms de colonnes entre parenthèses représentent un niveau d'agrégation.
GROUP BY GROUPING SETS renvoie une UNION de plusieurs requêtes GROUP BY.

GROUPING SETS et requêtes GROUP BY

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

Compter les locations de films pour chaque combinaison unique de pays et de genre.
Expression dans 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 et requêtes GROUP BY

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

Compter les locations de films pour chaque pays.
Expression dans GROUPING SETS : (country)

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

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

GROUPING SETS et requêtes GROUP BY

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

Compter les locations de films pour chaque genre.
Expression dans GROUPING SETS : (genre)

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

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

GROUPING SETS et requêtes GROUP BY

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

Agrégation totale : compter toutes les locations de films.
Expression dans GROUPING SETS : ()

SELECT COUNT(*)
FROM renting_extended;

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

Notation pour GROUP BY GROUPING SETS

GROUP BY GROUPING SETS (...)

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

UNION des 4 requêtes précédentes.
Combine toute l'information d'un tableau croisé en une seule requête.
Cette requête est équivalente à GROUP BY CUBE (country, genre).

Résultat avec l'opérateur GROUPING SETS

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  |

Calculer le nombre de locations et la cote moyenne

Combiner seulement certaines agrégations :
- pays et genre
- genre

Utiliser le nombre de locations et la moyenne des cotes pour agréger.

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

Calculer le nombre de locations et la cote moyenne

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       |

Passons à la pratique !

Prise de décision fondée sur les données avec SQL