Qué son ROLLUP y CUBE
ROLLUP y CUBE calculan totales a varios niveles en una sola consulta: por tienda y categoría, por tienda y el total general. Evitan escribir tres consultas y pegarlas.
La dueña de una papelería quiere un informe con las ventas de cada tienda y categoría. Quiere también un subtotal por tienda y un total de toda la cadena.
Al terminar esta lección
Nota
Puedo calcular totales a varios niveles con ROLLUP, CUBE y FILTER en una sola consulta.
Palabras clave
| Palabra | Qué significa | Ejemplo |
|---|---|---|
| Subtotal | el total de un grupo dentro de un informe | |
| ROLLUP | añade un subtotal por nivel y un total general | GROUP BY ROLLUP (shop, category) |
| CUBE | añade los totales de todas las combinaciones | |
| FILTER | limita las filas que suma un agregado | FILTER (WHERE category = 'Papel') |
| Tabla de doble entrada | una tabla con una columna por cada valor de otra |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
ROLLUP añade un subtotal por nivel
GROUP BY ROLLUP (shop, category) da el total de cada tienda y categoría. Añade además el subtotal de cada tienda y un total general.
En una fila de subtotal, la columna que se resume vale NULL. Esa fila dice «todas las categorías». El orden de las columnas importa: se resume de derecha a izquierda.
Ejemplo resuelto: ventas con subtotales
| shop | category | total |
|---|---|---|
| Centro | Archivo | 50 |
| Centro | Escritura | 80 |
| Centro | Papel | 120 |
| Centro | NULL | 250 |
| Norte | Escritura | 70 |
| Norte | Papel | 90 |
| Norte | NULL | 160 |
| Sur | Archivo | 40 |
| Sur | Escritura | 30 |
| Sur | Papel | 60 |
| Sur | NULL | 130 |
| NULL | NULL | 540 |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
Pruébalo: subtotales con ROLLUP
-- Cada tienda y categoría, el subtotal de cada tienda y el total general.
select shop, category, sum(revenue) as total
from shop_sales
group by rollup (shop, category)
order by shop nulls last, category nulls last;
-- Cambia el orden a rollup (category, shop) y pulsa Run.GROUPING distingue un resumen de un NULL real
Un NULL en una fila de subtotal no es un dato que falta. Es la marca de «todos».
GROUPING(category) vale 1 cuando la fila resume todas las categorías y 0 en caso contrario. Así nunca confundes un subtotal con un valor que de verdad está vacío.
Pruébalo: GROUPING en cada fila
-- GROUPING vale 1 donde la columna está resumida.
select shop, category, sum(revenue) as total,
grouping(shop) as all_shops, grouping(category) as all_categories
from shop_sales
group by rollup (shop, category)
order by shop nulls last, category nulls last;
-- Mira qué filas tienen un 1 y cuáles no.CUBE añade todas las combinaciones
CUBE (shop, category) da lo mismo que ROLLUP y algo más: el total de cada categoría en todas las tiendas.
En este caso salen tres filas más, con NULL en shop: Archivo 90, Escritura 180 y Papel 270. Usa CUBE cuando necesites mirar el informe desde los dos lados.
Pruébalo: CUBE
-- Todas las combinaciones: también el total de cada categoría.
select shop, category, sum(revenue) as total
from shop_sales
group by cube (shop, category)
order by shop nulls last, category nulls last;
-- Cambia cube por rollup y cuenta cuántas filas desaparecen.FILTER suma solo las filas elegidas
sum(revenue) FILTER (WHERE category = 'Papel') suma solo las filas de papel. Con una columna por categoría, el resultado es una tabla de doble entrada hecha a mano.
Si ninguna fila cumple la condición, la suma no da 0 sino NULL. Es lo que ocurre con la categoría Archivo en la tienda Norte.
Ejemplo resuelto: una columna por categoría
| shop | total | paper | writing | filing |
|---|---|---|---|---|
| Centro | 250 | 120 | 80 | 50 |
| Norte | 160 | 90 | 70 | NULL |
| Sur | 130 | 60 | 30 | 40 |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
Pruébalo: FILTER en tres columnas
-- Una columna por categoría, hecha a mano con FILTER.
select shop,
sum(revenue) as total,
sum(revenue) filter (where category = 'Papel') as paper,
sum(revenue) filter (where category = 'Escritura') as writing,
sum(revenue) filter (where category = 'Archivo') as filing
from shop_sales
group by shop
order by shop;
-- Cambia 'Papel' por 'Archivo' en la columna paper.Un error frecuente
Advertencia
Mucha gente lee el NULL de un subtotal como un dato perdido. Es la marca de «todos», no un fallo. Si la columna puede tener NULL de verdad, usa GROUPING para distinguirlos. Para dar nombre a esas filas usa coalesce.
Pruébalo: un nombre para los subtotales
-- Un nombre para las filas de resumen, con coalesce.
select coalesce(shop, 'Todas') as shop,
coalesce(category, 'Todas') as category,
sum(revenue) as total
from shop_sales
group by rollup (shop, category)
order by grouping(shop), shop, grouping(category), category;
-- Cambia 'Todas' por 'Total' en la columna category.