Limitar los resultados solamente a un lado de la relación

Sucede que en ocasiones queremos obtener información de dos o más tablas, pero limitando la cantidad de resultados solamente a un lado de la relación, normalmente en la tabla maestra. 

Supongamos que adicionamos al modelo con el que hemos trabajado, la posibilidad de  almacenar varios teléfonos asociados a un mismo cliente, indicando además que tipo de teléfono es (fax, trabajo, móvil, casa), y detalles sobre días de la semana y horas en que se aconseja llamar a ese teléfono.

Luego nos solicitan obtener los datos de los 3 clientes que más han aportado a la compañía en el año 2012 conjuntamente con el valor total del importe, mostrando además todos sus teléfonos con las características adicionales.

Muestro a continuación las mejoras al modelo que hemos estado utilizando, donde se incluyen  dos nuevas tablas, una donde se especifican los tipos de teléfonos y otra donde se almacenarán propiamente los teléfonos asociados a cada cliente. Pierde sentido mantener el campo telefono de la tabla clientes.

Evidentemente para dar solución a este problema no podemos limitar la consulta a solo 3 registros, pues no obtendríamos el resultado correcto, ya que un cliente puede tener más de un teléfono.

Obtengamos primeramente los 3 clientes solicitados (problema similar abordado en otro post), y luego veamos cómo obtener sus teléfonos en la misma consulta. Está claro que para la presentación de esta información, sea cual sea la plataforma o aplicación se necesita luego  de un procesamiento previo, muchas veces incluidas implícitamente en los motores de plantillas de reportes, o de generación de páginas web dinámicas, a partir de un agrupamiento del conjunto de datos.

En postgres esta sería una solución a esa primera consulta:

SELECT c.id_cliente, c.nombre, c.direccion, c.ciudad, 
       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) = 2012
GROUP BY  c.id_cliente, c.nombre, c.direccion, c.ciudad
ORDER BY importe_total DESC
LIMIT 3

Si utilizáramos un enlace de la tabla clientes con la tabla telefonos_clientes para obtener los teléfonos, no tendríamos forma de conocer a cuántos registros debemos limitar el resultado, por lo que una posible solución es utilizar la consulta anterior como subconsulta a la que haremos un enlace con la tabla telefonos_clientes para obtener todos los telefonos de los clientes obtenidos en la subconsulta.

SELECT c.id_cliente, c.nombre, c.direccion, c.ciudad, c.importe_total, 
       t.numero as telefono, tt.nombre as tipo_telefono, t.detalles
FROM (
  SELECT c.id_cliente, c.nombre, c.direccion, c.ciudad, 
         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) = 2012
  GROUP BY  c.id_cliente, c.nombre, c.direccion, c.ciudad
  ORDER BY importe_total DESC
  LIMIT 3
) c 
LEFT JOIN telefonos_clientes t ON (c.id_cliente=t.cliente)
LEFT JOIN tipos_telefonos tt ON (t.tipo=tt.id_tipo)
ORDER BY c.importe_total DESC

Note que el enlace con telefonos_clientes se hace utilizando un LEFT JOIN, pues puede suceder que algunos clientes no tengan teléfonos asociados. Note además que para garantizar que el resultado final nos muestre los clientes ordenados por importe de mayor a menor, es obligatorio volver a ordenar por ese valor en la consulta exterior.

Esto es todo por ahora, en la próxima entrada estaremos hablando sobre cómo definir y obtener un índice de popularidad basado en resultados históricos.

Insertar y actualizar desde una subconsulta

Muchas veces necesitamos actualizar tablas con información procedente de otras tablas; en estos casos puede ser de mucha utilidad el uso de subconsultas anidadas en sentencias INSERT o UPDATE.

Utilizaremos nuevamente el Modelo de base de datos introducido en la primera entrada, y supondremos además que nos solicitan adicionar una nueva tabla que contendrá los datos computados de las ventas totales por sucursales, mensualmente. Presento está nueva tabla, la cual estará enlazada a la tabla sucursales.

Para popular esta nueva tabla utilizaremos una consulta de inserción, donde los valores serán obtenidos de una subconsulta que utiliza funciones de agregación.

INSERT INTO resumen_sucursales (sucursal,anno,mes,ventas)
SELECT o.sucursal, EXTRACT(YEAR FROM o.fecha) AS anno, 
       EXTRACT(MONTH FROM o.fecha) AS mes,
       SUM(a.cantidad*a.precio_unitario)
FROM ordenes o INNER JOIN articulos a ON (o.id_orden=a.orden)
GROUP BY sucursal, anno, mes

Es importante destacar que la subconsulta debe devolver tantas columnas como las contenidas en la cláusula INSERT, y los tipos de datos deben coincidir en el orden en que se presentan.

Supongamos que luego de haber populado la tabla resumen_sucursles, nos solicitan adicionar la columna ordenes, donde se almacenará la cantidad de ordenes realizadas por cada sucursal mensualmente, y queremos actualizar su valor sin tener que eliminar los registros existentes e insertar nuevamente.

En este caso debemos usar una consulta de actualización, utilizando una subconsulta para computar los valores y proveer el dato necesario para la actualización. Una posible solución es la siguiente:

UPDATE resumen_sucursales SET ordenes = (SELECT count(*)
 FROM ordenes
 WHERE EXTRACT(YEAR FROM fecha)=resumen_sucursales.anno 
   AND EXTRACT(MONTH FROM fecha)=resumen_sucursales.mes
   AND sucursal=resumen_sucursales.sucursal)

En este tipo de soluciones se precisa que la subconsulta devuelva solamente un registro, aunque se realizarán internamente tantas subconsultas como combinaciones de valores existan en la tabla resumen_sucursales para las columnas anno, mes y sucursal.

Existen otras variantes para escribir esta consulta de actualización, personalmente prefiero utilizar esta:

UPDATE resumen_sucursales SET ordenes=s.ordenes
FROM 
( SELECT sucursal, EXTRACT(YEAR FROM fecha) AS anno, 
         EXTRACT(MONTH FROM fecha) AS mes, COUNT(*) AS ordenes 
  FROM ordenes
  GROUP BY sucursal, anno, mes
) s 
WHERE s.anno = resumen_sucursales.anno 
  AND s.mes = resumen_sucursales.mes
  AND s.sucursal=resumen_sucursales.sucursal

Algunos gestores permiten utilizar un alias para la tabla que va a ser actualizada, pero otros no, así que tenga cuidado si va a hacer uso del mismo. Por otro lado, la subconsulta si requiere siempre de un alias pues el mismo debe ser utilizado en la cláusula WHERE que enlaza la tabla a actualizar con la subconsulta que contiene los datos que serán utilizados para actualizar. Note que aquí no hay restricciones en cuanto a la cantidad de registros que devuelve la subconsulta, pero si se quieren obtener los resultados correctos debe construirse correctamente la subconsulta y utilizar cuidadosamente la cláusula WHERE. Esta variante de consulta de actualización puede generalizarse para cualquier cantidad de campos, como veremos a continuación, en el que se actualizará además de la cantidad de órdenes, el total de ventas.

UPDATE resumen_sucursales SET ventas=s.ventas, ordenes=s.ordenes
FROM 
( SELECT sucursal, EXTRACT(YEAR FROM fecha) AS anno, 
         EXTRACT(MONTH FROM fecha) AS mes, 
         SUM(a.cantidad*a.precio_unitario) AS ventas, 
         COUNT(DISTINCT o.id_orden) AS ordenes 
  FROM ordenes o INNER JOIN articulos a ON (o.id_orden=a.orden)
  GROUP BY sucursal, anno, mes
) s 
WHERE s.anno = resumen_sucursales.anno 
  AND s.mes = resumen_sucursales.mes 
  AND s.sucursal=resumen_sucursales.sucursal

Note que en este caso debe utilizarse la cláusula DISTINCT al contar la cantidad de órdenes, porque se incluye la tabla articulos que puede contener varios registros por cada orden. En este caso también puede remplazarse la primea línea de la consulta por la siguiente:

UPDATE resumen_sucursales SET (ventas, ordenes)=(s.ventas, s.ordenes)

lo cual es válido también para cualquier cantidad de columnas.

Es todo por hoy, en el próxima entrada estaremos hablando sobre como Limitar los resultados solamente a un lado de la relación cuando se tienen dos o más tablas relacionadas.

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.

COALESCE al rescate

En muchas ocasiones debemos tratar con valores NULL, los cuales necesitamos remplazar por otro valor. En este caso es útil la función COALESCE, la cual retorna el primero de sus argumentos que no es NULL. Evidentemente solo retornará NULL cuando todos sus argumentos son iguales a NULL. Como comentaba, se utiliza normalmente para sustituir los valores NULL por un valor por defecto.

A continuación planteo un problema en el que será muy útil su uso, y en el que además de utilizarla para reemplazar los posibles valores NULL de un campo por un valor predeterminado, se utilizará para determinar el valor de un campo obtenido como resultado de combinar dos conjuntos de datos usando FULL OUTER JOIN.

En el modelo de base de datos descrito en el post anterior, se nos solicita un informe donde mostremos para el mes 11 del año 2012, un resumen de todos los productos, con el valor planificado y el valor real de ventas. Solo se pide mostrar aquellos productos que han sido planificados y/o que han sido vendidos en ese mes, teniendo en cuenta que algunos productos pueden haber sido planificados sin haber tenido ventas, o pueden haber tenido ventas si haber sido planificados. Se conoce además que en la tabla plan_ventas los campos anno, mes y producto presentan una restricción de unicidad.

Presento una posible solución para PostgreSQL, aunque para otros gestores de base de datos la solución es similar, solamente intercambiando el uso de la función EXTRACT para extraer el año y el mes de la fecha de venta por sus equivalentes, aunque también puede utilizarse un filtro de comparación de fechas.

SELECT COALESCE(v.producto,pv.producto) AS producto, 
       COALESCE(v.ventas,0) AS ventas, 
       COALESCE(pv.plan,0) AS plan
FROM
(
  SELECT a.producto, SUM(a.cantidad) as ventas
  FROM articulos a INNER JOIN ordenes o ON (a.orden=o.id_orden)
  WHERE EXTRACT(YEAR FROM o.fecha)=2012 
    AND EXTRACT(MONTH FROM o.fecha)=11
  GROUP BY a.producto
) v
FULL OUTER JOIN plan_ventas pv 
  ON (pv.producto=v.producto AND pv.anno=2012 AND pv.mes=11)
ORDER BY ventas DESC

Notemos varios puntos

• Se utiliza una subconsulta para obtener los resultados parciales asociados a la venta. Existen otras soluciones en las que no se precisa del uso de subconsultas, pero las pruebas arrojaron que eran menos eficientes, pues el agrupamiento se realiza después de haber realizado la combinación entre todas las tablas.
• Se utiliza FULL OUTER JOIN para combinar ambos conjuntos de datos, pues se requieren todos los registros de ambas tablas aun cuando no haya coincidencia de productos.
• La restricción de Año y Mes de la tabla plan_ventas se coloca como parte de la condición en la que se basa la combinación con la subconsulta y no en la cláusula WHERE. Al colocarla en la cláusula ON estamos especificando cuales filas de la tabla plan_ventas serán tomadas en consideración para combinar con la subconsulta, pues los predicados de la cláusula ON se aplican a la tabla antes de la combinación. Si colocamos esta restricción en la cláusula WHERE estamos restringiendo las filas al resultado de la combinación, y evidentemente aquellas filas de la subconsulta (ventas) que contengan productos  que no estén en plan_ventas no serán mostrados, pues el valor del campo anno y mes serán NULL; una posibilidad sería utilizar en la cláusula WHERE la siguiente restricción:  

  WHERE (pv.anno=2012 OR pv.anno IS NULL)
    AND (pv.mes=11 OR pv.mes IS NULL)
  

  pero es menos intuitiva. Otra variante sería utilizar una subconsulta para seleccionar las filas que nos interesan de la tabla plan_ventas, antes de combinarla con la subconsulta de ventas.
• Se utiliza la función COALESCE en tres ocasiones, la primera para determinar el código del producto en cada fila resultante, pues existirán casos en que uno de los dos valores indistintamente puede ser NULL. En los otros dos casos se utiliza para asignar valor 0 cuando el valor de ventas o del plan sean NULL.

Esta consulta nos devuelve solamente los identificadores de los productos, si quisiéramos los nombres de los productos deberíamos entonces combinar ese resultado con la tabla productos, de la siguiente manera:

SELECT p.id_producto, p.nombre AS nombre_producto, 
      COALESCE(v.ventas,0) AS ventas, 
      COALESCE(pv.plan,0) AS plan
FROM
(
  SELECT a.producto, SUM(a.cantidad) as ventas
  FROM articulos a INNER JOIN ordenes o ON (a.orden=o.id_orden)
  WHERE EXTRACT(YEAR FROM o.fecha)=2012 
    AND EXTRACT(MONTH FROM o.fecha)=11
  GROUP BY a.producto
) v
FULL OUTER JOIN plan_ventas pv 
  ON (pv.producto=v.producto AND pv.anno=2012 AND pv.mes=11)
INNER JOIN productos p 
  ON (COALESCE(v.producto, pv.producto)=p.id_producto)
ORDER BY ventas DESC

Nótese que se utiliza la función COALESCE como parte de la condición en la que se basa la combinación de productos con el resultado previo, aunque en su defecto también pudiera utilizarse:

ON (v.producto=p.id_producto OR pv.producto=p.id_producto)

Evidentemente hay varias soluciones para este problema, intenté mostrar la más intuitiva y una de las más eficientes.

En la próxima entrada estaremos hablando sobre: Agrupamientoy búsqueda, donde veremos casos prácticos en los que se necesita localizar las filas asociadas a campos con valores máximos o mínimos.

El Modelo

Durante mis 15 años como desarrollador de software, he tenido que modelar y diseñar decenas de bases de datos, enfrentándome a diversos problemas en los que he tenido que hacer un uso exhaustivo del lenguaje SQL para poder dar solución a los mismos. 

Recientemente, motivado por la construcción de varias consultas SQL de un elevado nivel de complejidad, tuve la idea de comenzar este blog en el que intentaré compartir las soluciones a los problemas que me vayan surgiendo, a la vez que será un espacio en el que los lectores podrán proponer nuevas soluciones, y de esta manera establecer un fructífero intercambio.

Con el objetivo de que puedan comprenderse las consultas que publicaré, intentaré utilizar, siempre que sea posible, el mismo modelo de base de datos. Cuando sea necesario ampliaré este modelo, y en última instancia utilizaré uno diferente, debidamente explicado.

El modelo seleccionado responde a uno de los más simples y utilizados didácticamente: La modelación del proceso de ventas. Este es un proceso en el que de alguna manera todos hemos estado involucrados en varias ocasiones y del cual se dominan sus especificidades. Por motivos puramente didácticos utilizaré un modelo bastante reducido, pero que permita encontrar analogías con los problemas reales a los cuales me enfrento diariamente.

A continuación el modelo Entidad Relación con el que trabajaré.