Bloque 4 · Agregar PostgreSQL 17 northwind

Agregación avanzada

Este tema no está en las guías al uso. Verificado sobre el material de referencia: GROUPING SETS, ROLLUP y CUBE no aparecen ni una vez en sus 249 archivos. Y son exactamente lo que se pide en un informe real: "lo mismo, pero con subtotales".

1. Qué es

Con lo visto hasta aquí, un GROUP BY produce un solo nivel de agregación. Si además quieres subtotales y un total general, la salida ingenua es escribir tres consultas y unirlas con UNION ALL — tres pasadas sobre la misma tabla para responder a una sola pregunta.

Este tema son las cuatro herramientas que evitan eso:

Herramienta	Qué resuelve
`FILTER`	Agregar solo un subconjunto, por columna
`GROUPING SETS`	Varios niveles de agrupación en una sola pasada
`ROLLUP`	Subtotales jerárquicos + total general
`CUBE`	Todas las combinaciones posibles
`GROUPING()`	Distinguir un subtotal de un `NULL` real

Las cuatro primeras hacen una sola lectura de la tabla. Esa es la ventaja real, no la brevedad.

2. FILTER — agregar un subconjunto

agregación(...) FILTER (WHERE condición)

Aplica la condición solo a esa agregación, no a la consulta. Ya ha aparecido en temas anteriores; aquí está su explicación completa.

SELECT ship_country,
       count(*)                              AS pedidos,
       count(*) FILTER (WHERE freight > 100) AS caros,
       round(100.0 * count(*) FILTER (WHERE freight > 100) / count(*), 1) AS pct_caros,
       round(sum(freight) FILTER (WHERE extract(year FROM order_date) = 1997), 2) AS flete_1997
FROM orders
GROUP BY 1
ORDER BY pedidos DESC
LIMIT 5;

 ship_country | pedidos | caros | pct_caros | flete_1997
--------------+---------+-------+-----------+------------
 Germany      |     122 |    32 |      26.2 |    6232.55
 USA          |     122 |    40 |      32.8 |    5819.37
 Brazil       |      83 |    12 |      14.5 |    2226.01
 France       |      77 |    13 |      16.9 |    2467.76
 UK           |      56 |     9 |      16.1 |    1188.55

Cuatro métricas con cuatro criterios distintos, en una pasada. Alemania y EE. UU. empatan a 122 pedidos, pero el 32,8% de los de EE. UU. son caros frente al 26,2% de los alemanes. Esa comparación necesita FILTER: con un WHERE habrías perdido el denominador.

FILTER es del estándar SQL. SQL Server no lo implementa, y allí se escribe SUM(CASE WHEN ... THEN 1 ELSE 0 END) — que hace lo mismo, peor.

Regla mental: WHERE filtra la consulta entera; FILTER filtra una columna del resultado.

3. ROLLUP — subtotales jerárquicos

GROUP BY ROLLUP (a, b)

Agrupa por (a,b), luego por (a), y finalmente por () — el total general. Es lo que quieres cuando las columnas forman una jerarquía: año → mes, país → ciudad, categoría → producto.

SELECT extract(year FROM order_date)::int AS anio,
       ship_country,
       count(*)
FROM orders
WHERE ship_country IN ('Brazil', 'France')
GROUP BY ROLLUP (extract(year FROM order_date), ship_country)
ORDER BY anio, ship_country;

 anio | ship_country | count
------+--------------+-------
 1996 | Brazil       |    13
 1996 | France       |    15
 1996 |              |    28     ← subtotal de 1996
 1997 | Brazil       |    42
 1997 | France       |    39
 1997 |              |    81     ← subtotal de 1997
 1998 | Brazil       |    28
 1998 | France       |    23
 1998 |              |    51     ← subtotal de 1998
      |              |   160     ← total general

10 filas en una sola consulta. Sin ROLLUP serían tres consultas y un UNION ALL, con tres lecturas de orders.

Las celdas vacías marcan el nivel del subtotal. Cuanto más a la izquierda empieza el vacío, más agregada es la fila.

3.1 Etiquetar los subtotales

Un informe con celdas en blanco se lee mal. Se arregla con GROUPING() y coalesce:

SELECT CASE WHEN GROUPING(extract(year FROM order_date)) = 1 THEN 'TOTAL'
            ELSE extract(year FROM order_date)::text END AS anio,
       coalesce(ship_country, '— todos —')                AS pais,
       count(*)
FROM orders
WHERE ship_country IN ('Brazil', 'France')
GROUP BY ROLLUP (extract(year FROM order_date), ship_country)
ORDER BY 1, 2;

 anio  |   pais    | count
-------+-----------+-------
 1996  | — todos — |    28
 1996  | Brazil    |    13
 1996  | France    |    15
 1997  | — todos — |    81
 ...
 TOTAL | — todos — |   160

Gotcha: GROUPING() no acepta el alias de la columna. Hay que repetir la expresión entera (GROUPING(extract(year FROM order_date))). Con el alias da column "anio" does not exist.

4. CUBE — todas las combinaciones

GROUP BY CUBE (a, b)

Agrupa por (a,b), (a), (b) y (). Con n columnas produce 2ⁿ niveles.

SELECT extract(year FROM order_date)::int AS anio,
       ship_country,
       count(*),
       GROUPING(extract(year FROM order_date)) AS g_anio,
       GROUPING(ship_country)                  AS g_pais
FROM orders
WHERE ship_country IN ('Brazil', 'France')
GROUP BY CUBE (extract(year FROM order_date), ship_country)
ORDER BY g_anio, g_pais, anio, ship_country;

 anio | ship_country | count | g_anio | g_pais
------+--------------+-------+--------+--------
 1996 | Brazil       |    13 |      0 |      0
 1996 | France       |    15 |      0 |      0
 1997 | Brazil       |    42 |      0 |      0
 ...
 1996 |              |    28 |      0 |      1    ← por año
 1997 |              |    81 |      0 |      1
 1998 |              |    51 |      0 |      1
      | Brazil       |    83 |      1 |      0    ← por país  (esto ROLLUP no lo da)
      | France       |    77 |      1 |      0
      |              |   160 |      1 |      1    ← total

12 filas contra las 10 de ROLLUP. Las dos que añade son los totales por país sin desglosar por año — el nivel que ROLLUP se salta porque no está en la jerarquía.

Cuándo cada uno:

ROLLUP si las columnas son una jerarquía (año → mes). El total de "todos los meses de marzo de cualquier año" no tiene sentido.
CUBE si las dimensiones son independientes (país × categoría). Ahí sí quieres los totales por cada una.
CUBE con 4 columnas son 16 niveles. Crece rápido; con más de tres dimensiones casi siempre quieres GROUPING SETS.

5. GROUPING SETS — los niveles exactos que quieres

GROUP BY GROUPING SETS ((a, b), (a), ())

Es la forma general: ROLLUP y CUBE son atajos de esto. Se enumeran los niveles a mano y no sale ninguno de más.

SELECT ship_country,
       extract(year FROM order_date)::int AS anio,
       count(*)
FROM orders
WHERE ship_country = 'Brazil'
GROUP BY GROUPING SETS ((ship_country), (extract(year FROM order_date)), ())
ORDER BY 1, 2;

 ship_country | anio | count
--------------+------+-------
              | 1996 |    13
              | 1997 |    42
              | 1998 |    28
 Brazil       |      |    83     ← total por país
              |      |    83     ← total general

Tres niveles pedidos, tres bloques devueltos. Aquí los dos últimos coinciden porque el WHERE dejó un solo país.

6. GROUPING() — el subtotal contra el NULL de verdad

Este es el problema serio de ROLLUP, y en Northwind se puede tocar con la mano:

SELECT country, region, count(*)
FROM customers
WHERE country IN ('USA', 'UK')
GROUP BY ROLLUP (country, region)
ORDER BY country, region;

 country |    region     | count
---------+---------------+-------
 UK      | Isle of Wight |     1
 UK      |               |     6     ← ¿?
 UK      |               |     7     ← ¿?
 USA     | AK            |     1
 ...

Dos filas de UK con la región en blanco. ¿Cuál es cuál? Una es "los 6 clientes británicos cuya region es NULL" y la otra es "el subtotal de UK: 7". Indistinguibles a simple vista, y suman mal si alguien se equivoca.

GROUPING() lo resuelve: devuelve 1 si esa columna se agregó en ese nivel, y 0 si el NULL es un valor real del dato.

SELECT country, region,
       GROUPING(region) AS es_subtotal,
       count(*)
FROM customers
WHERE country IN ('USA', 'UK')
GROUP BY ROLLUP (country, region)
ORDER BY country, es_subtotal, region;

 country |    region     | es_subtotal | count
---------+---------------+-------------+-------
 UK      | Isle of Wight |           0 |     1
 UK      |               |           0 |     6     ← NULL real: 6 clientes sin región
 UK      |               |           1 |     7     ← subtotal de UK
 USA     | AK            |           0 |     1
 ...
 USA     |               |           1 |    13     ← subtotal de USA
         |               |           1 |    20     ← total general

Regla: si la columna del ROLLUP admite NULL, GROUPING() no es opcional. Sin él, el informe tiene dos filas idénticas que significan cosas distintas.

7. Errores comunes

Usar el alias dentro de GROUPING(). Hay que repetir la expresión.
ROLLUP sobre una columna con nulos, sin GROUPING(). Subtotales indistinguibles de los datos.
CUBE con muchas dimensiones. 2ⁿ niveles: con 5 columnas son 32 bloques y un resultado ilegible.
Sumar la columna del resultado en una hoja de cálculo. Las filas de subtotal ya están dentro: sumar la columna entera cuenta todo dos o tres veces. Es el error más caro de este tema.
ROLLUP con las columnas en el orden equivocado. ROLLUP (ciudad, pais) da subtotales por ciudad, no por país. El orden es la jerarquía.
Usar WHERE cuando querías FILTER. Te llevas el denominador por delante.
Ordenar sin tener en cuenta los subtotales. Un ORDER BY count(*) DESC mezcla totales y detalle: el total general sale el primero. Ordena por GROUPING() primero.

8. Diferencias con SQL Server

Necesidad	PostgreSQL	SQL Server
`GROUPING SETS`	✅	✅
`ROLLUP` / `CUBE`	✅	✅ (`GROUP BY ROLLUP(...)` desde 2008)
`GROUPING()`	✅	✅
`GROUPING_ID()`	✅	✅
`FILTER`	✅	❌ → `SUM(CASE WHEN ... THEN 1 ELSE 0 END)`

FILTER es la única diferencia, y es la que más se usa de las cinco. Si el código tiene que valer para los dos motores, escribe el CASE: funciona en ambos.

Nota histórica que puede caer en una entrevista: SQL Server tuvo WITH ROLLUP y WITH CUBE como sintaxis propia antes de 2008. Está obsoleta; la forma correcta hoy es GROUP BY ROLLUP(...) en los dos motores.

9. Ejercicios

Pedidos por año con subtotal por año y total general, usando ROLLUP.
Por categoría de producto: cuántos productos hay, cuántos descatalogados y qué porcentaje representan — con FILTER, en una sola pasada.
La misma tabla del ejercicio 1 pero etiquetando el total general como TOTAL.
Facturación por país y por año con todos los cruces posibles, usando CUBE. ¿Cuántas filas salen y por qué?
Explica por qué estas dos consultas devuelven un número de filas distinto: ```sql SELECT ship_country, extract(year FROM order_date), count(*) FROM orders GROUP BY ROLLUP (ship_country, extract(year FROM order_date));

SELECT ship_country, extract(year FROM order_date), count(*) FROM orders GROUP BY CUBE (ship_country, extract(year FROM order_date)); ```

10. Pregunta de negocio

Dirección quiere el informe de ventas del año: facturación por país, con subtotal por país, y el total general al final. En una sola tabla, para pegarla en una diapositiva. Y avisan de que el año pasado alguien les entregó un informe donde los números "no cuadraban con el total".

La segunda frase es el encargo de verdad.

? RespuestaRespóndela antes de abrir

SELECT CASE WHEN GROUPING(o.ship_country) = 1 THEN 'TOTAL GENERAL'
            ELSE o.ship_country END AS pais,
       count(DISTINCT o.order_id)   AS pedidos,
       round(sum(od.unit_price * od.quantity * (1 - od.discount)), 2) AS facturacion
FROM orders o
JOIN order_details od ON od.order_id = o.order_id
WHERE o.order_date >= '1997-01-01' AND o.order_date < '1998-01-01'
GROUP BY ROLLUP (o.ship_country)
ORDER BY GROUPING(o.ship_country), facturacion DESC;

     pais      | pedidos | facturacion
---------------+---------+-------------
 Germany       |      64 |   117320.16
 USA           |      60 |   114845.26
 Austria       |      21 |    57401.84
 France        |      39 |    45263.38
 Brazil        |      42 |    41941.19
 ...
 TOTAL GENERAL |     408 |   617085.20

Y lo que había que resolver era la última frase del encargo. "Los números no cuadraban con el total" tiene una causa casi segura y hay que decirla al entregar:

La fila de total ya contiene a todas las demás. Si alguien pega la tabla en una hoja y arrastra un SUMA() por la columna, obtiene el doble de la facturación real. Por eso la fila va etiquetada TOTAL GENERAL y no con una celda en blanco: una etiqueta explícita es lo que evita que se sume dos veces.
El count(DISTINCT o.order_id) es obligatorio aquí. Al unir con order_details, cada pedido aparece una vez por línea. Sin el DISTINCT, la columna "pedidos" contaría líneas —2,6 veces más— mientras la facturación seguiría siendo correcta. Una tabla con una columna inflada y otra correcta es exactamente la que hace que "los números no cuadren".
El ORDER BY GROUPING(...) primero deja el total abajo, donde se espera. Sin él, NULL ordena donde le toque y el total puede aparecer en medio de la lista, que es la otra forma de que alguien lo sume sin darse cuenta.

✓ SolucionesResuélvelos antes de abrir

SELECT extract(year FROM order_date)::int AS anio, count(*) AS pedidos
FROM orders
GROUP BY ROLLUP (extract(year FROM order_date))
ORDER BY anio;

 anio | pedidos
------+---------
 1996 |     152
 1997 |     408
 1998 |     270
      |     830

Con una sola columna, ROLLUP añade exactamente una fila: el total. 152 + 408 + 270 = 830. ✓

SELECT category_id,
       count(*)                                  AS productos,
       count(*) FILTER (WHERE discontinued)      AS descatalogados,
       round(100.0 * count(*) FILTER (WHERE discontinued) / count(*), 1) AS pct
FROM products
GROUP BY category_id
ORDER BY pct DESC;

 category_id | productos | descatalogados | pct
-------------+-----------+----------------+------
           6 |         6 |              4 | 66.7
           1 |        12 |              3 | 25.0
           7 |         5 |              1 | 20.0
           5 |         7 |              1 | 14.3
           2 |        12 |              1 |  8.3
           3 |        13 |              0 |  0.0
           4 |        10 |              0 |  0.0
           8 |        12 |              0 |  0.0

La categoría 6 tiene el 66,7% de sus productos descatalogados — dos tercios. Eso solo se ve porque FILTER conserva el denominador. Con WHERE discontinued habrías tenido el numerador y nada con qué compararlo.

SELECT CASE WHEN GROUPING(extract(year FROM order_date)) = 1 THEN 'TOTAL'
            ELSE extract(year FROM order_date)::text END AS anio,
       count(*) AS pedidos
FROM orders
GROUP BY ROLLUP (extract(year FROM order_date))
ORDER BY GROUPING(extract(year FROM order_date)), anio;

Fíjate en el ORDER BY: ordena primero por GROUPING() para que el total quede abajo. Ordenando solo por anio, el NULL del total se coloca según las reglas de nulos (ordenar-y-limitar) y puede salir el primero.

SELECT o.ship_country,
       extract(year FROM o.order_date)::int AS anio,
       round(sum(od.unit_price * od.quantity * (1 - od.discount)), 2) AS facturacion
FROM orders o
JOIN order_details od ON od.order_id = o.order_id
GROUP BY CUBE (o.ship_country, extract(year FROM o.order_date))
ORDER BY o.ship_country NULLS LAST, anio NULLS LAST;

Salen 87 filas. La tentación es calcular 21 países × 3 años = 63 combinaciones y sumar los subtotales, pero el resultado real es 62:

SELECT count(*) FROM (SELECT DISTINCT ship_country, extract(year FROM order_date) FROM orders) t;   -- 62

62 + 21 (subtotales por país) + 3 (por año) + 1 (total) = 87.

La combinación que falta es la clave del ejercicio:

SELECT ship_country, count(DISTINCT extract(year FROM order_date)) AS anios
FROM orders GROUP BY 1
HAVING count(DISTINCT extract(year FROM order_date)) < 3;

 ship_country | anios
--------------+-------
 Argentina    |     2

Argentina no tiene pedidos en 1996, y CUBE no inventa ese cruce vacío. Es la misma regla de los grupos vacíos de group-by: se agrupan las filas que hay, no las que podría haber. Nunca calcules el tamaño de un CUBE multiplicando cardinalidades.

5. ROLLUP (a, b) produce 3 niveles: (a,b), (a) y (). CUBE (a, b) produce 4: los mismos más (b), el total por año sin desglosar por país.

La diferencia son exactamente 3 filas — una por año. ROLLUP supone que ship_country está por encima de anio en una jerarquía; CUBE no supone nada. La elección no es de sintaxis: es una afirmación sobre si tus dimensiones son jerárquicas o independientes.