Agrupamiento y búsqueda

Sucede a menudo que después de utilizar alguna función de agregación, como MAX y MIN, deseamos saber que valores de otros campos tienen asociados esos valores máximos o mínimos. Pongamos un ejemplo utilizando el Modelo de base de datos introducido en la primera entrada.

Si queremos hallar el precio máximo entre todos los productos, basta ejecutar la siguiente consulta:

SELECT MAX(precio)
FROM productos

Si quisiéramos saber cuál es el producto que tiene el precio máximo, una posible solución pudiera ser obtener el primer producto ordenando los resultados descendentemente por precio, en PostgreSQL sería:

SELECT id_producto, nombre, precio
FROM productos
ORDER BY precio DESC
LIMIT 1

Pero esta solución tiene un problema: si existe más de un producto con el máximo precio, solo obtendríamos uno de ellos. Para poder obtenerlos todos tendríamos que buscar todos los productos cuyo precio coincide con el precio máximo:

SELECT id_producto, nombre, precio
FROM productos
WHERE precio = (SELECT MAX(precio) FROM productos)

Analicemos otros ejemplos más complejos.

Se nos solicita ahora devolver los clientes que han ordenado la mayor cantidad total (monetariamente) en el año 2011, y digo clientes, porque puede darse el caso de que existan al menos dos clientes con igual cantidad total.

La siguiente consulta me devolvería la máxima cantidad total que ha sido ordenada por un mismo cliente en el año 2011:

SELECT MAX(importe_total) as importe_total_maximo
FROM ( 
 SELECT o.cliente, SUM(a.precio_unitario*a.cantidad) AS importe_total
 FROM ordenes o INNER JOIN articulos a ON (a.orden=o.id_orden)
 WHERE EXTRACT(YEAR FROM o.fecha) = 2011
 GROUP BY o.cliente
) v

Notemos aquí la necesidad de utilizar una subconsulta por el hecho de que no se pueden anidar llamadas a funciones de agregación (al menos en PostgreSQL y la gran mayoría de los gestores de base de datos), y se necesita primero computar la suma para después buscar el máximo.
Aunque en lugar de esa solución también podemos emplear esta:

SELECT SUM(a.precio_unitario*a.cantidad) AS importe_total
FROM ordenes o INNER JOIN articulos a ON (a.orden=o.id_orden)
WHERE EXTRACT(YEAR FROM o.fecha) = 2011
GROUP BY o.cliente
ORDER BY importe_total DESC
LIMIT 1

Debemos entonces, por analogía con el problema anterior, buscar todos los clientes cuyo importe total ordenado en el año 2011 coincide con el máximo valor.

SELECT c.id_cliente, c.nombre,
       SUM(a.precio_unitario*a.cantidad) AS importe_total
FROM clientes c INNER JOIN ordenes o ON (c.id_cliente=o.cliente)
     INNER JOIN articulos a ON (a.orden=o.id_orden)
WHERE EXTRACT(YEAR FROM o.fecha) = 2011
GROUP BY  c.id_cliente, c.nombre
HAVING SUM(a.precio_unitario*a.cantidad) = 
(
 SELECT MAX(importe_total) as importe_total_maximo
 FROM ( 
  SELECT o.cliente, SUM(a.precio_unitario*a.cantidad) AS importe_total
  FROM ordenes o INNER JOIN articulos a ON (a.orden=o.id_orden)
  WHERE EXTRACT(YEAR FROM o.fecha) = 2011
  GROUP BY o.cliente
 ) v 
)

Nótese la necesidad de utilizar HAVING, pues la comparación con el valor máximo debe hacerse después de haber computado la suma. En la subconsulta que devuelve el máximo puede utilizarse cualquiera de las dos variantes mostradas anteriormente, los resultados prácticos no dieron diferencias significativas en cuanto a la velocidad de ejecución de las mismas. En la consulta externa incorporamos además la tabla clientes para poder obtener el nombre de los clientes, y evidentemente debemos incluir ese campo en la cláusula GROUP BY. Utilicé además el campo id_cliente de la tabla clientes, pero de igual manera podía haber utilizado el campo cliente de la tabla ordenes.

Si se deseara solamente el cliente que primero alcanzó la máxima cantidad, entonces  (suponiendo que es casi improbable que dos clientes alcanzaron la máxima cantidad el mismo día) no necesitaríamos complicarnos tanto, y bastaría la siguiente consulta:

SELECT c.id_cliente, c.nombre, MAX(o.fecha) AS ultima_fecha, 
       SUM(a.precio_unitario*a.cantidad) AS importe_total
FROM clientes c INNER JOIN ordenes o ON (c.id_cliente=o.cliente)
     INNER JOIN articulos a ON (a.orden=o.id_orden)
WHERE EXTRACT(YEAR FROM o.fecha) = 2011
GROUP BY  c.id_cliente, c.nombre
ORDER BY importe_total DESC, ultima_fecha ASC
LIMIT 1

Aún así, no crean en probabilidades nulas, y aunque se compliquen, utilicen consultas que no den lugar a errores por suposiciones. En este caso una solución correcta sería (en PostgreSQL):

SELECT c.id_cliente, c.nombre, MAX(o.fecha) AS ultima_fecha, 
       SUM(a.precio_unitario*a.cantidad) AS importe_total
FROM clientes c INNER JOIN ordenes o ON (c.id_cliente=o.cliente)
     INNER JOIN articulos a ON (a.orden=o.id_orden)
WHERE EXTRACT(YEAR FROM o.fecha) = 2011
GROUP BY  c.id_cliente, c.nombre
HAVING (SUM(a.precio_unitario*a.cantidad), MAX(o.fecha)) = 
(
 SELECT SUM(a.precio_unitario*a.cantidad) AS importe_total, 
        MAX(o.fecha) AS ultima_fecha
 FROM ordenes o  INNER JOIN articulos a ON (a.orden=o.id_orden)
 WHERE EXTRACT(YEAR FROM o.fecha) = 2011
 GROUP BY o.cliente
 ORDER BY importe_total DESC, ultima_fecha ASC
 LIMIT 1
)

En la subconsulta se obtiene el importe total entre todos los clientes, y la fecha mínima en la que se alcanzó, debido a que se ordena el importe total descendentemente y el máximo de fecha ascendentemente, agrupados por clientes, y tomando solamente el primer resultado. Luego se buscan los clientes cuyo importe total y última fecha de orden coinciden con esos valores, a partir de una comparación de ambos valores en la cláusula HAVING con los resultados obtenidos en la subconsulta.

En la próxima entrada estaremos hablando sobre como insertar y actualizar desde una subconsulta.