Operaciones OLAP: GROUPING SETS

Toma de decisiones basada en datos en SQL

Irene Ortner

Data Scientist at Applied Statistics

Resumen de operadores OLAP en SQL

Extensiones SQL para facilitar operaciones OLAP

GROUP BY CUBE
GROUP BY ROLLUP
GROUP BY GROUPING SETS

Tabla renting_extended

Primeras filas de la tabla 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

Ejemplo de consulta con el operador GROUPING SETS:

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

Los nombres de columnas entre paréntesis son un nivel de agregación.
GROUP BY GROUPING SETS devuelve un UNION de varias consultas GROUP BY.

Consultas con GROUPING SETS y GROUP BY

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

Cuenta alquileres por cada combinación única de país y género.
Expresión en 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      |

Consultas con GROUPING SETS y GROUP BY

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

Cuenta alquileres por país.
Expresión en GROUPING SETS: (country)

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

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

Consultas con GROUPING SETS y GROUP BY

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

Cuenta alquileres por género.
Expresión en GROUPING SETS: (genre)

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

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

Consultas con GROUPING SETS y GROUP BY

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

Agregación total: cuenta todos los alquileres.
Expresión en GROUPING SETS: ()

SELECT COUNT(*)
FROM renting_extended;

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

Notación de GROUP BY GROUPING SETS

GROUP BY GROUPING SETS (...)

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

UNION de 4 consultas previas.
Combina toda la info de una tabla dinámica en una sola consulta.
Equivale a GROUP BY CUBE (country, genre).

Resultado con el operador 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  |

Calcular nº de alquileres y nota media

Combina solo agregaciones seleccionadas:
- país y género
- género

Usa número de alquileres y la media de valoraciones.

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

Calcular nº de alquileres y nota media

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       |

¡Vamos a practicar!

Toma de decisiones basada en datos en SQL