Otro ejemplo de CTE: ventas semanales

Deja un comentario

En este ejemplo veremos como obtener el total vendido en cada día de una semana usando la construcción CTE. O sea que podremos responder a la pregunta, ¿cuánto se vendió el domingo, cuánto se vendió el lunes, cuánto se vendió el martes…., etc.?

CREATE PROCEDURE VENTAS_SEMANALES(
   tdFechax DATE)
RETURNS(
   ftnVentasDom NUMERIC(10, 2),
   ftnVentasLun NUMERIC(10, 2),
   ftnVentasMar NUMERIC(10, 2),
   ftnVentasMie NUMERIC(10, 2),
   ftnVentasJue NUMERIC(10, 2),
   ftnVentasVie NUMERIC(10, 2),
   ftnVentasSab NUMERIC(10, 2)
AS
   DECLARE VARIABLE ldFecIni DATE;
BEGIN

   ldFecIni = (SELECT :tdFechax - EXTRACT(WEEKDAY FROM :tdFechax) FROM RDB$DATABASE);

   WITH VentasDia AS (
      SELECT
         M.MVC_FECHAX,
         SUM(M.MVC_TOTALX) AS VENTAS
      FROM
         MOVIMCAB M
      WHERE
         MVC_TIPMOV = 'SVT'
      GROUP BY
         1
   )

   SELECT
      COALESCE(D0.VENTAS, 0),
      COALESCE(D1.VENTAS, 0),
      COALESCE(D2.VENTAS, 0),
      COALESCE(D3.VENTAS, 0),
      COALESCE(D4.VENTAS, 0),
      COALESCE(D5.VENTAS, 0),
      COALESCE(D6.VENTAS, 0)
   FROM
      RDB$DATABASE
   LEFT JOIN
      VENTASDIA D0
         ON D0.MVC_FECHAX = :ldFecIni + 0
   LEFT JOIN
      VENTASDIA D1
         ON D1.MVC_FECHAX = :ldFecIni + 1
   LEFT JOIN
      VENTASDIA D2
         ON D2.MVC_FECHAX = :ldFecIni + 2
   LEFT JOIN
      VENTASDIA D3
         ON D3.MVC_FECHAX = :ldFecIni + 3
   LEFT JOIN
      VENTASDIA D4
         ON D4.MVC_FECHAX = :ldFecIni + 4
   LEFT JOIN
      VENTASDIA D5
         ON D5.MVC_FECHAX = :ldFecIni + 5
   LEFT JOIN
      VENTASDIA D6
         ON D6.MVC_FECHAX = :ldFecIni + 6
   INTO
      :ftnVentasDom,
      :ftnVentasLun,
      :ftnVentasMar,
      :ftnVentasMie,
      :ftnVentasJue,
      :ftnVentasVie,
      :ftnVentasSab;

END;

Como ya sabes, una tabla CTE es una tabla virtual o sea que no existe en la realidad sino que es creada antes de usarla y es eliminada después de ser usada. Es muy útil cuando debes hacer más de un JOIN con una tabla que tiene columnas agrupadas o funciones agregadas.

¿Qué hace este stored procedure?

Primero, halla el domingo que corresponde a la semana de la fecha que recibió como parámetro de entrada. Por ejemplo, el día 18 de noviembre de 2013 es lunes, eso significa que el domingo de esa semana es el 17 de noviembre de 2013. Ese día domingo se guarda en la variable ldFecIni. ¿Por qué se hace eso? porque los días de la semana tienen número en Firebird. Domingo = 0, Lunes = 1, Martes, = 2, etc.

A continuación se crea la tabla virtual, la tabla CTE, la cual será usada más adelante en un SELECT. A esa tabla virtual (o tabla CTE) se la llamó VentasDia.

Después, se hace un SELECT a la tabla RDB$DATABASE. ¿Por qué a esa tabla? porque está garantizada de tener siempre una fila, solamente una fila, y nunca más de una fila. Aquí podrías usar cualquier otra tabla de la cual estás seguro de que siempre tendrá exactamente una fila, no es obligatorio usar RDB$DATABASE, puedes usar cualquier otra tabla que siempre tenga una fila.

Se selecciona la columna VENTAS de la tabla virtual VentasDia para cada día de la semana. ¿Cómo sabemos cuál es el día de la semana? Pues en ldFecIni tenemos el domingo. Si le sumamos 1 tendremos el lunes. Si le sumamos 2 tendremos el martes, y así sucesivamente.

Los alias a la tabla virtual los nombramos como D0, D1, D2, D3, etc. donde la letra “D” significa “día”.

Finalmente, los valores de todas esas columnas los guardamos en los parámetros de salida ftnVentasDomftnVentasLun, etc.

Al ejecutar este stored procedure obtendremos algo como:

VENTASSEMANALES1

Captura 1. Si haces clic en la imagen la verás más grande

Las columnas que tienen cero implican que ese día no hubo ventas. Para ello se usó la función COALESCE() para que si el resultado de ejecutar la función SUM() era NULL lo convierta a cero.

En síntesis, este stored procedure recibe un parámetro de entrada (la fecha de un día de la semana que nos interesa) y devuelve siete parámetros de salida (el total de las ventas del domingo, el total de las ventas del lunes, el total de las ventas del martes, etc.)

Artículo relacionado:

El índice del blog Firebird21

Usando CTE (Common Table Expression)

Deja un comentario

Una de las excelentes construcciones que tiene Firebird se llama CTE (Common Table Expression) porque nos facilita grandemente escribir consultas complejas.

¿Qué es CTE?

Es una tabla virtual que se escribe antes del SELECT principal y que es eliminada cuando finaliza el SELECT principal. El SELECT principal puede referenciar a cualquier CTE como si fuera una tabla o una vista normal. Las CTE también pueden ser recursivas (o sea que pueden llamarse a sí mismas), pero no pueden ser anidadas (o sea que no puede existir una CTE dentro de otra CTE).

¿Cómo se escribe una CTE?

WITH NombreCTE AS (
   SELECT .....
)

Fíjate que se usan paréntesis para indicar donde empieza y donde termina el SELECT, no se usa BEGIN … END

¿Cuándo es conveniente usar CTE?

Cuando para escribir nuestro SELECT principal necesitamos columnas de otra tabla que cumplen una condición o que necesitamos tenerlas agrupadas o necesitamos unir a dos o más tablas o vistas o stored procedures seleccionables.

Eso implica que escribir una CTE como la siguiente es un error porque no te otorga algún beneficio:

WITH NombresClientes AS (
   SELECT CLI_NOMBRE FROM CLIENTES
)

¿Por qué es un error escribir una CTE como la de arriba? porque en el SELECT no se usó la cláusula WHERE ni la cláusula GROUP BY ni una función agregada ni el comando UNION, por lo tanto nada se ha ganado al escribirla, fue un desperdicio de tiempo y se escribió más de lo necesario.

Ejemplo:

Queremos mostrar, para cada sucursal de la Empresa, el total de las ventas del año 2011 y el total de las ventas del año 2012.

Para ello escribimos una vista que hace uso de una CTE. Nuestra tabla MOVIMCAB tiene estos datos:

CTE1

Captura 1. Si haces clic en la imagen la verás más grande

CREATE VIEW V_TOTAL_VENTAS(
   SUC_CODIGO,
   VENTAS2011,
   VENTAS2012)
AS

   WITH TotalVentasAnuales AS (
      SELECT
         MVC_CODSUC,
         EXTRACT(YEAR FROM MVC_FECHAX) AS Ano,
         SUM(MVC_TOTALX * MVC_COTIZA) AS VentasAnuales
      FROM
         MOVIMCAB
      GROUP BY
         1, 2
   )

   SELECT
      S.SUC_CODIGO,
      V2011.VENTASANUALES AS VENTAS2011,
      V2012.VENTASANUALES AS VENTAS2012
   FROM
      SUCURSALES S
   LEFT JOIN
      TotalVentasAnuales V2011
         ON S.SUC_CODIGO = V2011.MVC_CODSUC AND
            V2011.Ano = 2011
   LEFT JOIN
      TotalVentasAnuales V2012
         ON S.SUC_CODIGO = V2012.MVC_CODSUC AND
            V2012.Ano = 2012;

Y este es el resultado que obtenemos:

CTE2

Captura 2. Si haces clic en la imagen la verás más grande

En el SELECT principal se usó a la CTE llamada TotalVentasAnuales como si fuera una tabla normal. Se usó LEFT JOIN y no simplemente JOIN porque alguna Sucursal puede no tener ventas de un año (porque se cerró la Sucursal antes de ese año o porque se la abrió después de ese año).

Por supuesto que no es la única forma de llegar a ese resultado pero la ventaja de usar CTE es que nuestro código es mucho más entendible y por lo tanto si hay algún error será mucho más fácil encontrarlo y corregirlo.

Conclusión:

Sería muy conveniente que te acostumbres a usar CTE cuando en tu consulta necesitas datos de otra tabla (para hacer un JOIN) y los datos de esa otra tabla deben encontrarse en columnas agrupadas o requieren de funciones agregadas o del comando UNION porque más rápidamente escribirás tu consulta.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21