ROLLUP y CUBE

Estadísticas resumidas y funciones de ventana de PostgreSQL

Michel Semaan

Data Scientist

Totales a nivel de grupo

Medallas de China y Rusia en los Juegos Olímpicos de Verano de 2008 por categoría de medalla

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | Total  | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | Total  | 143    |

La forma tradicional

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, Medal
ORDER BY Country ASC, Medal ASC

UNION ALL

SELECT
  Country, 'Total', COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, 2
ORDER BY Country ASC;

Introduce ROLLUP

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM Summer_Medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY Country, ROLLUP(Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP es una subcláusula GROUP BY que incluye más filas para agregaciones a nivel de grupo.
GROUP BY Country, ROLLUP(Medal) contará todos los totales de nivel Country y Medal, luego contará solo los totales de nivel Country y completará Medal con null para estas filas.

ROLLUP - Consulta

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY ROLLUP(Country, Medal)
ORDER BY Country ASC, Medal ASC;

ROLLUP es jerárquico y se desagrega desde la columna más a la izquierda hacia la más a la derecha.
- ROLLUP(Country, Medal) incluye los totales del nivel de Country.
- ROLLUP(Medal, Country) incluye los totales del nivel de Medal.
- Ambos incluyen los totales generales.

ROLLUP - Resultado

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | null   | 327    |

Los totales a nivel de grupo contienen nulls; la fila con todos los nulles el total general.
Ten en cuenta que no se han incluido los totales del nivel de Medal, ya que se trata de ROLLUP(Country, Medal) y no de ROLLUP(Medal, Country).

Introduce CUBE

SELECT
  Country, Medal, COUNT(*) AS Awards
FROM summer_medals
WHERE
  Year = 2008 AND Country IN ('CHN', 'RUS')
GROUP BY CUBE(Country, Medal)
ORDER BY Country ASC, Medal ASC;

CUBE es un sistema no jerárquico ROLLUP
Genera todas las agregaciones posibles a nivel de grupo.
- CUBE(Country, Medal) cuenta el nivel Country, el nivel Medal y los totales generales.

CUBE - Resultado

| Country | Medal  | Awards |
|---------|--------|--------|
| CHN     | Bronze | 57     |
| CHN     | Gold   | 74     |
| CHN     | Silver | 53     |
| CHN     | null   | 184    |
| RUS     | Bronze | 56     |
| RUS     | Gold   | 43     |
| RUS     | Silver | 44     |
| RUS     | null   | 143    |
| null    | Bronze | 113    |
| null    | Gold   | 117    |
| null    | Silver | 97     |
| null    | null   | 327    |

Ten en cuenta que se incluyen los totales del nivel Medal.

ROLLUP frente a CUBE

Origen

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | Q1      | 12    |
| 2008 | Q2      | 15    |
| 2009 | Q1      | 21    |
| 2009 | Q2      | 27    |

Utiliza ROLLUP cuando tengas datos jerárquicos (por ejemplo, partes de fechas) y no busques todas las agregaciones posibles a nivel de grupo.
Utiliza CUBE cuando desees todas las agregaciones posibles a nivel de grupo.

ROLLUP(Year, Quarter)

| Year | Quarter | Sales |
|------|---------|-------|
| 2008 | null    | 27    |
| 2009 | null    | 48    |
| null | null    | 75    |

CUBE(Year, Quarter)

Las filas anteriores + las siguientes

| Year | Quarter | Sales |
|------|---------|-------|
| null | Q1      | 33    |
| null | Q2      | 42    |

¡Vamos a practicar!

Estadísticas resumidas y funciones de ventana de PostgreSQL