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.