Opérations OLAP : GROUPING SETS

Prise de décision basée sur des données en SQL

Irene Ortner

Data Scientist at Applied Statistics

Présentation des opérateurs OLAP dans 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 requête UNION sur plusieurs requêtes GROUP BY.

Requêtes GROUPING SETS et 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      |

Requêtes GROUPING SETS et 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     |

Requêtes GROUPING SETS et 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    |

Requêtes GROUPING SETS et 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 sur les 4 requêtes précédentes.
Regrouper toutes les informations d'un tableau croisé dynamique dans une seule requête.
Cette requête équivaut à 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 moyenne des évaluations

Combiner uniquement les agrégations sélectionnées :
- pays et genre
- genre

Utiliser le nombre de locations de films et la moyenne des évaluations pour l'agrégation.

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 moyenne des évaluations

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 basée sur des données en SQL