Funciones de agregación

1. Qué es

Una agregación coge muchas filas y devuelve un solo valor. Es lo contrario de las funciones escalares del tema anterior, que devuelven un valor por fila.

77 filas de products  →  avg(unit_price)  →  1 valor: 28.83

Sin GROUP BY, la tabla entera es un solo grupo y la consulta devuelve exactamente una fila. Con GROUP BY, devuelve una fila por grupo — ese es el tema siguiente, group-by.

Las dos reglas que gobiernan todo:

1. Las agregaciones ignoran los NULL (salvo count(*)).
2. Sobre cero filas, count devuelve 0 y todas las demás devuelven NULL.

2. Las funciones

min y max funcionan también sobre texto y fechas, no solo números: dan el primero y el último por orden.

3. Ejemplos sobre Northwind

Los tres count, que no son lo mismo:

SELECT count(*)                   AS filas,
       count(shipped_date)        AS enviados,
       count(DISTINCT customer_id) AS clientes
FROM orders;

 filas | enviados | clientes
-------+----------+----------
   830 |      809 |       89

Tres números de la misma tabla que responden a tres preguntas distintas: cuántos pedidos hay, cuántos se enviaron, y cuántos clientes compraron. Confundirlos es el error de métrica más común que existe.

El resumen de una columna numérica:

SELECT min(unit_price)         AS minimo,
       max(unit_price)         AS maximo,
       round(avg(unit_price),2) AS media,
       sum(units_in_stock)     AS stock_total
FROM products;

 minimo | maximo | media | stock_total
--------+--------+-------+-------------
   2.50 | 263.50 | 28.83 |        3119

Media 28,83 y máximo 263,50: la media está muy lejos del máximo, señal de que hay unos pocos productos caros arrastrando la cola. Ese contraste entre avg y max es lo primero que se mira para saber si una media es representativa.

SELECT round(avg(units_in_stock),2) AS media, round(stddev(units_in_stock),2) AS desviacion
FROM products;

 media | desviacion
-------+------------
 40.51 |      36.15

Desviación casi igual que la media: los valores están dispersísimos. Una media de stock de 40 no describe a ningún producto real.

3.1 avg ignora los nulos — con datos reales

SELECT count(*)                              AS pedidos,
       count(shipped_date - order_date)      AS con_dato,
       round(avg(shipped_date - order_date),2) AS dias_medios
FROM orders;

 pedidos | con_dato | dias_medios
---------+----------+-------------
     830 |      809 |        8.49

8,49 días de media para enviar un pedido — calculado sobre 809, no sobre 830. Los 21 sin enviar no cuentan.

¿Está bien? Depende de la pregunta. Si es "cuánto tardamos en enviar", sí: los que no se han enviado no tienen tiempo de envío. Si es "cuánto espera un cliente", no: los 21 que llevan meses esperando son precisamente los peores casos, y quedan fuera. La media sale bonita porque excluye a los que van mal.

3.2 El vacío: count da 0, el resto da NULL

SELECT count(*) AS filas, sum(freight) AS suma, avg(freight) AS media, max(freight) AS maximo
FROM orders WHERE ship_country = 'Narnia';

 filas | suma | media | maximo
-------+------+-------+--------
     0 |      |       |

count(*) = 0; sum, avg y max = NULL. No es un capricho: la suma de nada es discutible (¿0? ¿nada?), pero la media de nada no existe, y SQL prefiere ser coherente y devolver NULL en las cuatro.

Consecuencia práctica: si ese sum alimenta un cálculo posterior, el NULL se propaga y anula todo (null-y-la-logica-de-tres-valores). La defensa es coalesce(sum(freight), 0).

Y ojo — esto pasa de verdad en Northwind:

SELECT count(*), sum(freight) FROM orders WHERE ship_country = 'Peru';

Devuelve 0 y NULL: no hay ningún pedido enviado a Perú, aunque Peru sí existe como país de cliente. Un informe por país que use sum sin coalesce mostrará una celda vacía en vez de un cero.

3.3 min y max sobre texto

SELECT min(company_name) AS primero, max(company_name) AS ultimo FROM customers;

       primero       |     ultimo
---------------------+----------------
 Alfreds Futterkiste | Wolski  Zajazd

Primero y último por orden alfabético, con las reglas del collation (ordenar-y-limitar). Es útil para auditar rangos, y peligroso si crees que te da "el más importante": max sobre texto no significa nada de negocio.

3.4 string_agg — juntar filas en un texto

SELECT string_agg(DISTINCT country, ', ' ORDER BY country) AS paises FROM suppliers;

 Australia, Brazil, Canada, Denmark, Finland, France, Germany, Italy, Japan,
 Netherlands, Norway, Singapore, Spain, Sweden, UK, USA

16 países de proveedores en una celda. Es la agregación que más se usa en informes: "los productos de este pedido, separados por coma". El ORDER BY va dentro de la función, que es sintaxis propia de las agregaciones.

3.5 bool_and y bool_or

SELECT bool_and(NOT discontinued) AS todos_activos,
       bool_or(discontinued)      AS alguno_de_baja
FROM products;

 todos_activos | alguno_de_baja
---------------+----------------
 f             | t

Responden a "¿todos cumplen?" y "¿alguno cumple?" en una sola pasada. Casi nadie las conoce y ahorran subconsultas enteras.

3.6 La media que engaña: simple contra ponderada

SELECT round(avg(unit_price), 2)                          AS media_simple,
       round(sum(unit_price * quantity) / sum(quantity), 2) AS media_ponderada
FROM order_details;

 media_simple | media_ponderada
--------------+-----------------
        26.22 |           26.39

avg(unit_price) trata igual una línea de 1 unidad y otra de 100. La ponderada pesa cada precio por lo que se vendió. Aquí la diferencia es pequeña (17 céntimos), pero el patrón es el importante:

avg() de un precio casi nunca es el precio medio del negocio. El precio medio de venta es sum(importe) / sum(unidades). Si el informe se llama "ticket medio" o "precio medio", pregúntate qué se está promediando.

Y la variante con el mismo problema:

SELECT round(avg(discount), 4)                        AS media_todas,
       round(avg(discount) FILTER (WHERE discount > 0), 4) AS media_con_descuento,
       count(*) FILTER (WHERE discount > 0)           AS lineas_con_descuento,
       count(*)                                       AS lineas
FROM order_details;

 media_todas | media_con_descuento | lineas_con_descuento | lineas
-------------+---------------------+----------------------+--------
      0.0562 |              0.1444 |                  838 |   2155

5,6% contra 14,4%. Son las dos respuestas correctas a dos preguntas distintas: "¿cuánto descuento damos de media?" (5,6%, incluyendo las 1.317 líneas sin descuento) y "¿de cuánto es un descuento cuando lo damos?" (14,4%). Entregar una sin decir cuál es la que engaña.

Aquí los ceros sí entran en la media, porque 0 no es NULL. Esa es la diferencia clave entre "no hubo descuento" guardado como 0 y guardado como NULL: con 0 baja la media, con NULL no la toca. El diseño de la tabla decide el resultado del informe.

4. Errores comunes

1. count(*) cuando querías count(DISTINCT ...). La métrica dice "clientes" y devuelve pedidos.
2. count(columna) creyendo que cuenta filas. Cuenta las no nulas.
3. No blindar sum contra el vacío. coalesce(sum(x), 0).
4. Interpretar una media sin mirar la dispersión. Mira también min, max y stddev.
5. Confundir media simple con ponderada.
6. Poner una agregación en el WHERE. No se puede: va en HAVING (having-vs-where).
7. Mezclar columna suelta y agregación sin GROUP BY. SELECT product_name, max(unit_price) FROM products da error, y es el error correcto: no hay un product_name para toda la tabla.
8. sum(DISTINCT ...) para arreglar un JOIN que duplica. Ver distinct-y-duplicados.

5. Diferencias con SQL Server

El gotcha silencioso: AVG sobre una columna entera en SQL Server devuelve un entero. AVG(quantity) sobre 1, 2 y 2 da 1, no 1,67. PostgreSQL devuelve numeric y da 1,6667. La misma consulta, dos resultados, ningún error. Al migrar informes, ese es de los primeros números que hay que cuadrar.

6. Ejercicios

1. Cuántos productos hay, cuántos tienen proveedor asignado y cuántos proveedores distintos aparecen en products.
2. El pedido más antiguo, el más reciente, y cuántos días separan ambos.
3. La facturación total de order_details, con y sin aplicar el descuento. ¿Cuánto se ha dejado de facturar en descuentos?
4. El nombre de todos los cargos de employees en una sola celda, separados por " | " y sin repetir.
5. Explica por qué estas dos consultas dan números distintos: sql SELECT avg(units_in_stock) FROM products; SELECT sum(units_in_stock) / count(*) FROM products;

7. Pregunta de negocio

Dirección pide "el ticket medio de la empresa" para compararlo con el del sector. Un compañero ha entregado 26,22. ¿Es correcto?