Enviando una consulta a EXCEL

2 comentarios

Hay muchas formas de enviar el resultado de una consulta a una planilla Excel, aquí veremos una de esas formas.

NOTA: el código fuente está basado en Visual FoxPro pero puede ser fácilmente adaptado a otros lenguajes de programación.

La ventaja de este método es que nos resultará muy fácil enviar la consulta que queremos, al libro Excel que queremos, a la hoja Excel que queremos, en el rango que queremos mostraremos la consulta, y esa consulta puede estar basada en cualquier tabla o en cualquier vista de nuestra Base de Datos del Firebird.

Primero, creamos la función que se encargará de realizar la tarea.

Listado 1.

FUNCTION ENVIAR_EXCEL_CON_DSN
LParameters tcDSN, tcUsuario, tcPassword, tcConsulta, tcRango
Local lcMensajeError, lcConsulta, loExcel, loLibro, loHoja, loTabla, lcConexion, lcBaseDatos

  lcMensajeError = ""

  tcRango = iif(VarType(tcRango) <> "C", "A1", tcRango)    && Si no se definió donde colocar la tabla, se la coloca en la celda A1

  *-- Tratamos de crear un objeto Excel
  if Empty(lcMensajeError)
    loExcel = CreateObject("Excel.Application")
    lcMensajeError = iif(VarType(loExcel) <> "O", "No se pudo crear el objeto EXCEL, verifica si está instaldo EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear un libro Excel
  if Empty(lcMensajeError)
    loLibro = loExcel.Workbooks.Add
    lcMensajeError = iif(VarType(loLibro) <> "O", "No se pudo crear el libro de EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, tratamos de crear una hoja en el libro Excel
  if Empty(lcMensajeError)
    loHoja = loLibro.Worksheets(1)
    lcMensajeError = iif(VarType(loHoja) <> "O", "No se pudo crear la hoja en el libro EXCEL", lcMensajeError)
  endif

  *-- Si todo OK, creamos el string de conexión a la Base de Datos del Firebird, usando el DSN de esa Base de Datos
  if Empty(lcMensajeError)
    lcConexion = "ODBC;DSN=" + tcDSN + ";USER=" + tcUsuario + ";PASSWORD=" + tcPassword
  endif

  *-- Si todo OK, tratamos de conectarnos a la Base de Datos del Firebird
  if Empty(lcMensajeError)
    loTabla = loHoja.QueryTables.Add(lcConexion, loHoja.Range(tcRango), tcConsulta)
    lcMensajeError = iif(VarType(loTabla) <> "O", "No se pudo crear la tabla de consulta en la planilla Excel", lcMensajeError)
  endif

  *-- Si todo OK, insertamos la tabla en la planilal Excel
  if Empty(lcMensajeError)
    #DEFINE xlInsertEntireRows 2
    loTabla.RefreshStyle = xlInsertEntireRows
    loTabla.Refresh
  endif

  *-- Si todoOK, mostramos la planilla Excel
  if Empty(lcMensajeError)
    loExcel.Visible = .T.
  endif

  *-- Liberamos de la memoria los objetos que habíamos creado
  loTabla = .NULL.
  loHoja  = .NULL.
  loLibro = .NULL.
  loExcel = .NULL.

Return(lcMensajeError)
*
*

Y después llamamos a esa función, con los parámetros que deseemos usar.

Listado 2.

Local lcDSN, lcUsuario, lcPassword, lcConsulta, lcMensajeError, lcRango

  lcDSN = "AUTOMUNDO-FIREBIRD"

  lcUsuario = "SYSDBA"
  lcPassword = "masterkey"

   lcConsulta = "SELECT " ;
              + "   ASC_ANOEJE, " ;
              + "   ASC_CODSUC, " ;
              + "   ASC_FECHAX, " ;
              + "   ASC_NUMERO " ;
              + "FROM " ;
              + "   V_CONTA_ABM_ASIENTOS " ;
              + "WHERE " ;
              + "   ASC_NUMERO >= 100 AND " ;
              + "   ASC_NUMERO <= 120"

  lcRango = "B5"

  lcMensajeError = ENVIAR_EXCEL_CON_DSN(lcDSN, lcUsuario, lcPassword, lcConsulta, lcRango)

  *-- Si ocurrió algún error, mostramos un mensaje al usuario
  if !Empty(lcMensajeError)
    #DEFINE MSG_ICONO_ERROR 16
    #DEFINE MSG_BOTON_ACEPTAR 0
    =MessageBox(lcMensajeError, MSG_ICONO_ERROR + MSG_BOTON_ACEPTAR, "Hay un problema...")
  endif

Return
*
*

Y así obtendremos una planilla similar a la siguiente:

EXCEL01

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

Por lo tanto, nos resultará muy sencillo crear una planilla Excel con los datos que el usuario pueda necesitar. Inclusive podríamos tener en nuestra aplicación una opción que le muestre todas las vistas disponibles y que le permita crear consultas personalizadas, a su gusto y paladar. Ya depende de la creatividad de cada uno hacer algo así.

Artículos relacionados:

Usando EXCEL para leer bases de datos de Firebird

Planillas EXCEL dinámicas con Firebird (1)

Planillas EXCEL dinámicas con Firebird (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

Restando fechas que se encuentran en distintas filas

Deja un comentario

Un lector del foro de este blog, preguntó si era posible restar fechas que se encuentran en distintas filas.

Restar dos fechas de distintos registros

FECHAS01

Captura 1.

La respuesta es afirmativa, pero necesitamos conocer sin equivocación posible a la fila donde se encuentra la segunda fecha.

Por eso, en este caso supondremos que se encuentra en la siguiente fila. O sea, la Fecha Inicial estará en una fila y la Fecha Final siempre estará en la siguiente fila.

¿Y cómo podemos conocer cual es la siguiente fila?

Pues numerando a las filas, de tal manera que la primera tenga el número 1, la segunda tenga el número 2, la tercera tenga el número 3, etc.

Este artículo nos muestra algunas técnicas para conseguir lo que deseamos:

Numerando las filas de un SELECT

Lo que aquí haremos (no es el único método, pero es un método que se entiende bien) es agregarle una columna a nuestra tabla. Nos quedará así:

FECHAS02

Captura 2.

PRU_NUMREG es el Número de Registro

PRU_FECINI es la Fecha Inicial

PRU_FECFIN es la Fecha Final

Luego creamos un generador (también llamado secuencia) y con un comando UPDATE numeraremos a esas filas:

Listado 1.

CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

Como el generador ya no nos sirve, lo eliminamos con el comando DROP.

El contenido de nuestra tabla ahora quedará así:

FECHAS03

Captura 3.

Como tenemos a todas las filas numeradas el siguiente paso ya es muy fácil:

Listado 2:

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1),
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1) - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1

Podemos tener a todo junto:

Listado 3.

CREATE GENERATOR
   GEN_NUMERAR;

UPDATE
   PRUEBA1
SET
   PRU_NUMREG = GEN_ID(GEN_NUMERAR, 1);

DROP GENERATOR
   GEN_NUMERAR;

SELECT
   T1.PRU_NUMREG,
   T1.PRU_FECINI,
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1),
   (SELECT PRU_FECFIN FROM PRUEBA1 T2 WHERE T2.PRU_NUMREG = T1.PRU_NUMREG + 1 ROWS 1) - T1.PRU_FECINI AS DIFERENCIA
FROM
   PRUEBA1 T1;

Y este será el resultado que obtendremos:

FECHAS04

Captura 4.

Que es justamente lo que queríamos conseguir: restarle a la Fecha Inicial que se encuentra en una fila la Fecha Final que se encuentra en la siguiente fila.

Desde luego que este no es el único método posible para obtener este resultado, pero es un método fácil y sencillo de entender.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Ejemplo de recursión (6). Repitiendo las filas

3 comentarios

Un usuario del foro hizo esta pregunta: “Tengo una tabla llamada ETIQUETAS con dos columnas: NOMBRES Y NUMERO_REPETICIONES y deseo que cada nombre se repita esa cantidad de veces”.

La idea es imprimir etiquetas con esos nombres.

Solución:

Primero, creamos la tabla ETIQUETAS

ETIQUETAS1

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

Segundo, le insertamos algunas filas a la tabla de ETIQUETAS

ETIQUETAS2

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

Tercero, usando recursión obtenemos cada nombre, repetido la cantidad de veces indicada.

Listado 1.

WITH RECURSIVE MISETIQUETAS AS (
 
   SELECT
      ETI_NOMBRE,
      ETI_REPITE
   FROM
      ETIQUETAS
   WHERE
      ETI_REPITE > 0
 
   UNION ALL
 
   SELECT
      ETI_NOMBRE,
      ETI_REPITE - 1
   FROM
      MISETIQUETAS
   WHERE
      ETI_REPITE - 1 > 0
 
)

SELECT
   ETI_NOMBRE
FROM
   MISETIQUETAS

ETIQUETAS3

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

Y como podemos ver, cada nombre se ha repetido la cantidad de veces que habíamos escrito en la columna ETI_REPITE.

Explicación:

Primero, obtenemos todas las filas de la tabla ETIQUETAS cuya cantidad de etiquetas a imprimir es mayor que cero. Eso hace el primer SELECT, o sea nuestro “ancla” como habíamos visto en artículos anteriores.

Segundo, repetimos cada una de esas filas ETI_REPITE – 1 veces. ¿Por qué – 1? Porque la primera fila ya fue insertada por el “ancla” así que debemos insertar solamente las demás filas.

Tercero, mostramos cada nombre repetido la cantidad de veces que habíamos especificado en su columna ETI_REPITE. De eso se encarga el último SELECT.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

Ejemplo de recursión (4). Actualizando filas recursivamente

Ejemplo de recursión (5). Saldos acumulados

El índice del blog Firebird21

El foro del blog Firebird21

Ejemplo de GROUP BY (1). Respondiendo a “todos” o “en cada uno”

Deja un comentario

A veces necesitamos responder a preguntas que incluyen a las palabras “todos” o “en cada uno”. Por ejemplo:

  • Los productos que se vendieron todos los días
  • Los alumnos que rindieron todos los exámenes
  • Los empleados que trabajaron todos los días

¿Cómo podemos solucionarlo?

Una alternativa es mediante el uso de las cláusulas GROUP BY y HAVING.

La idea es: primero, hallar la cantidad total de alternativas distintas, y luego hallar quienes tienen esa cantidad

Ejemplo:

Tenemos una tabla llamada MOVIMCAB (cabecera de movimientos) donde guardamos, entre otros datos, la fecha de cada venta y el identificador del vendedor que realizó la venta. Y nos interesa responder a la pregunta: ¿cuáles vendedores vendieron todos los días?

TODOS1

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

En casos así lo mejor es dividir el problema en 3 partes:

  1. Hallar la cantidad de fechas distintas
  2. Hallar la cantidad de fechas distintas en que cada vendedor ha vendido
  3. Comparar ambas cantidades

Paso 1. Hallando la cantidad de fechas distintas

Listado 1.

SELECT
   COUNT(DISTINCT MVC_FECHAX)
FROM
   MOVIMCAB

TODOS2

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

Al ejecutar el Listado 1. vemos que hay 3 fechas distintas en la tabla MOVIMCAB.

Paso 2. Hallando la cantidad de fechas distintas que cada vendedor ha vendido

Listado 2.

SELECT
   MVC_IDEVEN,
   COUNT(DISTINCT MVC_FECHAX)
FROM
   MOVIMCAB
GROUP BY
   MVC_IDEVEN

TODOS3

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

Al ejecutar el Listado 2. vemos la cantidad de fechas distintas en que cada vendedor ha vendido.

Paso 3. Comparando ambas cantidades

Listado 3.

SELECT
   MVC_IDEVEN,
   COUNT(DISTINCT MVC_FECHAX)
FROM
   MOVIMCAB
GROUP BY
   MVC_IDEVEN
HAVING
   COUNT(DISTINCT MVC_FECHAX) = (SELECT COUNT(DISTINCT MVC_FECHAX) FROM MOVIMCAB)

TODOS4

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

Al ejecutar el Listado 3. obtenemos la Captura 4. que nos dice que los vendedores cuyos identificadores son 1 y 7 han vendido todos los días, y que hay 3 días distintos.

Explicación:

En la cláusula HAVING del Listado 3. comparamos la cantidad de fechas distintas que ha vendido cada vendedor con la cantidad de fechas distintas que tenemos en la tabla MOVIMCAB. Si ambas cantidades son iguales entonces evidentemente ese vendedor ha vendido en todas las fechas.

Artículos relacionados:

Entendiendo la cláusula GROUP BY: agrupando datos

La claúsula GROUP BY requiere estar ordenada

La claúsula HAVING: filtrando las filas agrupadas

El índice del blog Firebird21

El foro del blog Firebird21

La cláusula HAVING: filtrando las filas agrupadas

Deja un comentario

En artículos anteriores ya hemos visto a la cláusula GROUP BY, la forma de usarla, y su utilidad práctica. Así que ahora nos preguntamos ¿y si solamente nos interesan algunas de esas filas agrupadas?

Pues en nuestro socorro viene la cláusula HAVING.

Después de haber agrupado a las filas usando la cláusula GROUP BY, podemos establecer las condiciones que esas filas agrupadas deben cumplir.

Esto es algo opcional, no siempre se requiere filtrar a las filas agrupadas.

Veamos un ejemplo:

GROUP1

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

En la tabla MOVIMCAB (cabecera de movimientos) tenemos los datos de cada venta realizada. Queremos agrupar a esas filas por identificador del cliente, para ver el total vendido a cada cliente.

Listado 1.

SELECT
   MVC_IDECLI,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_IDECLI

GROUP3

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

En el Listado 1. tenemos la solución, después de ejecutarlo veremos la Captura 2. Todo bien hasta ahí. Pero supongamos que ahora solamente nos interesa ver los clientes a quienes les hemos vendido por 1.000.000 ó más.

Listado 2.

SELECT
   MVC_IDECLI,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_IDECLI
HAVING
   SUM(MVC_TOTALX) >= 1000000

GROUP7

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

En el Listado 2. hemos usado a la cláusula HAVING y el resultado obtenido lo vemos en la Captura 3.

¿Qué hace la cláusula HAVING?

Filtra (o sea, le pone condiciones) a las filas que estarán en el conjunto resultado de nuestra consulta. La cláusula HAVING solamente puede usarse después que la cláusula GROUP BY y solamente allí, en ningún otro lugar es admitida.

En la Captura 2. hemos visto el resultado de agrupar a las ventas por clientes, en la Captura 3. vemos el resultado de poner una condición a las filas de la Captura 2.

Es decir que tenemos a las filas que vemos en la Captura 2. pero solamente algunas de esas filas nos interesan. Allí es donde usamos a la cláusula HAVING, para que solamente las filas de la Captura 2. que cumplen nuestra condición sean mostradas.

La cláusula HAVING es muy similar a la cláusula WHERE, pero mientras que WHERE filtra a todas las filas, HAVING solamente filtra a las filas que obtuvimos después de un agrupamiento.

A veces, podemos usar WHERE o podemos usar HAVING, y el resultado obtenido es el mismo. A veces solamente podemos usar a HAVING, no está permitido usar WHERE. Veamos ejemplos:

Con WHERE y con HAVING se obtiene el mismo resultado

Listado 3.

SELECT
   MVC_IDECLI,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
WHERE
   MVC_IDECLI >= 5
GROUP BY
   MVC_IDECLI

Listado 4.

SELECT
   MVC_IDECLI,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_IDECLI
HAVING
   MVC_IDECLI >= 5

GROUP8

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

Tanto si ejecutamos el Listado 3. o el Listado 4. el resultado que obtendremos será el mismo: el mostrado en la Captura 4.

¿Por qué?

Porque la condición de filtro fue puesta en la columna MVC_IDECLI, o sea en una columna que no es una función agregada: COUNT(), SUM(), MAX(), MIN(), AVG().

En cambio, si la condición la ponemos en una columna que proviene de una función agregada, solamente podremos usar la cláusula HAVING, no podemos usar una función agregada en la cláusula WHERE.

Listado 5.

SELECT
   MVC_IDECLI,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
WHERE
   SUM(MVC_TOTALX) >= 1000000
GROUP BY
   MVC_IDECLI

El Listado 5. es incorrecto, el Firebird no lo aceptará y nos mostrará el mensaje: “Cannot use an aggregate function in a WHERE clause, use HAVING instead.

Por lo tanto, si queremos filtrar a las funciones agregadas no podremos escribirlas en la cláusula WHERE, solamente podremos escribirlas en la cláusula HAVING.

En síntesis, si tu condición de filtro no involucra a una función agregada puedes usar la cláusula WHERE o la cláusula HAVING para filtrar a esas filas, como prefieras, es tu elección, al Firebird le da lo mismo. Pero si tu condición de filtro involucra a una función agregada solamente puedes usar a la cláusula HAVING, no tienes otra posibilidad.

No se pueden usar alias en la cláusula HAVING

Listado 6.

SELECT
   MVC_IDECLI,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_IDECLI
HAVING
   TOTAL_VENTA >= 1000000

En el Listado 6. hay un error y el Firebird nos avisará con el mensaje: “Column unknown. TOTAL_VENTA.

¿Cuál es el error? Que quisimos usar al alias de una columna (en este caso el alias es TOTAL_VENTA) en la cláusula HAVING y eso no está permitido. La forma correcta es la mostrada en el Listado 2., que sí funcionará perfectamente.

Conclusión:

Si después de agrupar a las filas nos interesa ver solamente a algunas de ellas, entonces podemos establecer la condición (o condiciones) que deben cumplir esas filas en la cláusula HAVING.

La cláusula HAVING solamente puede escribirse inmediatamente después que la cláusula GROUP BY, en ningún otro lugar es admitida.

A veces podemos filtrar a las filas usando la cláusula WHERE o usando la cláusula HAVING, indistintamente. Eso ocurre cuando nuestro filtro no involucra a una función agregada. En tales casos ya es preferencia de cada uno usar WHERE o usar HAVING.

Sin embargo, si la condición de filtro involucra a una (o más de una) función/es agregada/s entonces solamente podemos usar la cláusula HAVING, usar la cláusula WHERE no está permitido en esos casos.

Otro punto importante a recordar es que los alias de las columnas no pueden usarse con la cláusula HAVING.

Artículos relacionados:

Entendiendo la cláusula GROUP BY: agrupando datos

La cláusula GROUP BY requiere estar ordenada

El índice del blog Firebird21

El foro del blog Firebird21

Entendiendo la cláusula GROUP BY: agrupando datos

4 comentarios

El lenguaje SQL nos permite agrupar filas. ¿Qué significa agrupar filas? Que todas aquellas filas que tienen en una (o más de una) columna/s valores en común aparecerán una sola vez en nuestra consulta.

Ejemplos:

  • Para cada fecha, queremos ver el total vendido en esa fecha.
  • Para cada cliente, queremos ver el total vendido a ese cliente.
  • Para cada producto, queremos ver la cantidad total vendida de ese producto.
  • Para cada vendedor, queremos ver el promedio mensual de ventas de ese vendedor

Hay dos formas de resolver algo así:

  1. Sin agrupar las filas
  2. Agrupando las filas

Ventas a un cliente sin agrupar las filas

Tenemos una tabla llamada MOVIMCAB (cabecera de movimientos) donde guardamos los datos de las ventas que realizamos, y si consultamos las ventas realizadas entre el 1 de agosto y el 3 de agosto del año 2015, veremos algo como:

GROUP1

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

Donde MVC_FECHAX es la fecha de la venta, MVC_IDECLI es el identificador del cliente, MVC_TIPDOC es el tipo de Factura (1=contado, 2=crédito), MVC_NRODOC es el número de la Factura y MVC_TOTALX es el total de esa venta.

Y ahora queremos saber: ¿cuánto le hemos vendido en total al cliente cuyo identificador es 5?. Podemos fácilmente responder a esa pregunta así:

Listado 1.

SELECT
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
WHERE
   MVC_IDECLI = 5

GROUP2

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

Y como vemos en la Captura 2., funcionó perfectamente. Respondimos muy bien a la pregunta.

Sin embargo, ¿qué pasará si necesitamos conocer el total vendido no a un solo cliente sino a muchos clientes? Que tendremos que repetir el Listado 1. muchas veces, una vez por cada cliente que nos interese. Si tenemos 2000 clientes tendremos que repetir esa consulta 2000 veces, algo totalmente impráctico y mortalmente aburrido.

Hay una solución muchísimo más eficiente: usar la cláusula GROUP BY.

Ventas a los clientes, agrupando las filas

Listado 2.

SELECT
   MVC_IDECLI,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_IDECLI

GROUP3

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

Aquí, para cada cliente, hemos obtenido el monto total vendido, como puedes comprobar mirando la Captura 1. y comparándola con la Captura 3.

Evidentemente, escribir el Listado 2. una sola vez es tremendamente mejor que escribir el Listado 1. unas 2000 veces.

¿Cómo funciona la cláusula GROUP BY?

  1. Normalmente se la utiliza con las funciones agregadas: COUNT(), SUM(), MAX(), MIN(), AVG()
  2. Agrupa todas las filas que en una columna (o más de una) columna/s tienen valores en común
  3. Todas las columnas que no sean funciones agregadas, deben especificarse en la cláusula GROUP BY

Total de ventas, por fechas

En el Listado 2. usamos GROUP BY para obtener el total vendido a cada cliente. Ahora usaremos esa misma cláusula pero para obtener el total vendido en cada fecha.

Listado 3.

SELECT
   MVC_FECHAX,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_FECHAX

GROUP4

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

Y aquí hemos obtenido el total vendido en cada fecha. La diferencia entre el Listado 2. y el Listado 3. es la columna que hemos elegido para agrupar. Al agrupar por identificador del cliente hemos obtenido el total vendido a cada cliente; al agrupar por fecha hemos obtenido el total vendido en cada fecha.

Podemos agrupar por cualquier columna que nos interese. Desde luego que para que tenga sentido en 2 ó más filas esa columna debe tener al menos un valor en común. Por ejemplo, sería una tontería agrupar por la columna MVC_NRODOC ¿por qué? porque no hay valores repetidos allí. Agrupando o no agrupando obtendremos los mismos resultados así que hemos escrito más (porque escribimos la cláusula GROUP BY) y obtuvimos lo mismo que si no la hubiéramos escrito. Pérdida de tiempo.

Por lo tanto, debemos agrupar por cualquier columna que tenga o pueda tener valores repetidos.

Agrupar por varias columnas

En los ejemplos anteriores siempre hemos agrupado usando una sola columna, pero eso no tiene siempre que ser así, podemos agrupar usando varias columnas también.

Por ejemplo, para cada tipo de Factura queremos obtener el total vendido en cada fecha.

Listado 4.

SELECT
   MVC_TIPDOC,
   MVC_FECHAX,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_TIPDOC,
   MVC_FECHAX

GROUP5

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

Aquí hemos agrupado primero por MVC_TIPDOC (1=Factura Contado, 2=Factura Crédito) y luego por fecha de la venta.

Así sabemos que el 1 de agosto hubo ventas con Facturas Contado por un total de 835.000 y ese mismo día las ventas con Facturas Crédito ascendieron a 740.000

El Listado 4. nos permitirá responder a: “Muéstrame el total de Facturas Contado vendidas en cada fecha y luego el total de Facturas Crédito vendidas en cada fecha”.

Podríamos cambiar el orden, para que primero aparezcan las fechas

Listado 5.

SELECT
   MVC_FECHAX,
   MVC_TIPDOC,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_FECHAX,
   MVC_TIPDOC

GROUP6

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

Aunque el orden de las columnas y el orden de las filas está cambiado, vemos que el TOTAL_VENTA sigue siendo correcto. La consulta del Listado 5. nos permitiría responder lo siguiente: “Para cada fecha muéstrame el total vendido con Factura Contado y el total vendido con Factura Crédito”.

Un error común de principiantes

Un error muy común entre los principiantes es querer que la consulta muestre también una columna que no fue agrupada. Eso no está permitido, no es posible.

Listado 6.

SELECT
   MVC_FECHAX,
   MVC_NRODOC,
   SUM(MVC_TOTALX) AS TOTAL_VENTA
FROM
   MOVIMCAB
GROUP BY
   MVC_FECHAX

Si intentamos ejecutar el Listado 6. veremos el mensaje: “Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

¿Cuál es el error? Que la columna MVC_NRODOC no se encuentra en la cláusula GROUP BY. Ese SELECT es por lo tanto incorrecto.

Cuando usamos la cláusula GROUP BY al Firebird le estamos pidiendo que agrupe a todas las filas que en una columna (o más de una columna) tenga/n valores repetidos. Para eso sirve la cláusula GROUP BY.

Si en la lista de columnas del SELECT escribimos una columna y a esa columna no la escribimos también en el GROUP BY entonces evidentemente no estaremos agrupando según esa columna. Y no tiene sentido agrupar por algunas columnas sí y por algunas columnas no. O se agrupa por todas las columnas o por ninguna. Agrupar solamente algunas columnas es imposible de realizar.

Si algo así estuviera permitido entonces el Listado 6. nos mostraría una fecha, el número de una Factura (¿de cuál Factura? podemos tener muchas en una fecha), y el total vendido en esa fecha. No nos mostraría la venta de esa Factura sino la venta acumulada de toda la fecha. Eso es un sinsentido. Ninguna pregunta práctica podríamos responder con el Listado 6.

Por ese motivo el Firebird rechazará una consulta como la del Listado 6.

¿Y por qué no agrega a la cláusula GROUP BY todas las columnas que se encuentran en el SELECT?

Podríamos pensar que algo así sería muy práctico: si escribimos una columna en el SELECT y no la escribimos en el GROUP BY, que el Firebird se encargue de agregarla allí.

Bien, eso tiene sus problemas: 1) que el orden de las columnas del SELECT puede ser distinto al orden de las columnas del GROUP BY, no hay obligación de mantener el orden y ya vimos que cambiando el orden de las columnas del GROUP BY se obtienen resultados distintos, por lo tanto el Firebird podría colocar a la columna faltante en el lugar equivocado y no obtendríamos los resultados deseados. 2) Que el Firebird no puede adivinar lo que tú quieres hacer y por lo tanto no puede saber si esa columna sobrante la escribiste por equivocación o si tu equivocación fue no incluirla en el GROUP BY.

En resumen: como con la cláusula GROUP BY se agrupan filas que en una columna (o más de una columna) tiene/n valores repetidos todas las columnas que se escriban en el SELECT deben también, sí o sí, escribirse en el GROUP BY. Eso es lo lógico y lo único que tiene sentido.

Conclusión:

La cláusula GROUP BY es muy útil para agrupar filas que en una columna (o en más de una columna) tienen valores repetidos. Podríamos dejar de usarla y obtendríamos los mismos resultados pero si no la usamos generalmente escribiremos muchísimo pero muchísimo más.

Todas las columnas que escribimos en el SELECT debemos también escribir en el GROUP BY porque de lo contrario obtendremos un error. No tiene sentido tener una columna en el SELECT y no tenerla también en el GROUP BY.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Ejemplo de recursión (4). Actualizando filas recursivamente

3 comentarios

Así como podemos consultar filas de forma recursiva, también podemos actualizar esas filas recursivamente si es lo que necesitamos.

Veamos un caso:

Tenemos una tabla llamada CUENTAS que tiene estos datos:

UPDATE1

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

En la columna CUE_IDENTI guardamos el identificador de cada fila; en la columna CUE_IDEPAD guardamos el identificador de su fila padre; en la columna CUE_TIMEST guardamos la fecha y la hora de la última actualización de una cuenta. Ahora supongamos que queremos que la cuenta CRÉDITOS y todas las cuentas que sean sus descendientes tengan la fecha y la hora actuales.

Listado 1.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (
      WITH RECURSIVE MiCTE AS (
         
         SELECT
            CUE_IDENTI
         FROM
            CUENTAS
         WHERE
            CUE_IDENTI = 6
         
         UNION ALL
         
         SELECT
            T1.CUE_IDENTI
         FROM
            CUENTAS T1
         JOIN
            MiCTE 
               ON T1.CUE_IDEPAD = MiCTE.CUE_IDENTI
         
      )
      
      SELECT CUE_IDENTI FROM MiCTE

   );

UPDATE2

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

Tal y como podemos ver en la Captura 2. se actualizó la columna CUE_TIMEST de la cuenta que tiene CUE_IDENTI =6 y también de todos sus descendientes. En el Listado 1. podemos ver que la primera cuenta a ser actualizada es la que tiene CUE_IDENTI = 6, a continuación se actualizan las demás filas.

Desde luego que no solamente podemos cambiar la fecha y la hora al actualizar, podemos cambiar cualquier dato que necesitemos.

Explicación:

Como en la cláusula WHERE del UPDATE estamos usando IN eso significa que queremos actualizar varias filas. ¿Qué filas actualizaremos? Aquellas cuya columna CUE_IDENTI se encuentre en la tabla virtual que crearemos. En nuestra tabla virtual la primera fila es la que tiene CUE_IDENTI = 6, así que esa fila sí o sí será actualizada. A continuación hallamos el CUE_IDENTI de las demás filas que queremos actualizar. Como la tabla virtual es recursiva entonces se le irán agregando filas hasta que la condición del JOIN deje de cumplirse. El último SELECT es el que devuelve todas las filas de la tabla virtual, y todas esas filas tienen una sola columna, llamada CUE_IDENTI.

Entonces, nuestro UPDATE recursivo sería el equivalente a este UPDATE no recursivo.

Listado 2.

UPDATE
   CUENTAS
SET
   CUE_TIMEST = CURRENT_TIMESTAMP
WHERE
   CUE_IDENTI IN (6, 7, 8, 9, 10)

¿Cuál es la diferencia? Que en el Listado 2. debemos conocer  todos los identificadores que deseamos actualizar, en cambio en el Listado 1., no. Allí solamente necesitamos conocer el identificador de la primera cuenta, nada más. Todas las cuentas que sean sus descendientes serán actualizadas, sean 4 como en este caso o sean 500 o sean 1000. La única limitación es que pueden ser como máximo 1024 porque el Firebird permite hasta 1024 recursiones.

El Listado 2. es más sencillo, pero requiere conocer los identificadores de todas las cuentas que queremos actualizar y si los usuarios están constantemente agregando cuentas tendremos que estar cambiando nuestro UPDATE a cada rato. Por el contrario con el Listado 1. lo único que debemos conocer es el identificador de la primera cuenta que deseamos actualizar y todas las cuentas que sean sus descendientes se actualizarán también.

Artículos relacionados:

Stored procedures recursivos

Entendiendo a las tablas autoreferenciadas

Usando CTE (Common Table Expression)

Otro ejemplo de CTE: ventas semanales

Usando varias CTE en una vista o en un stored procedure

FOR SELECT y tablas CTE

Usando recursividad con CTE

Ejemplo de recursión (1). Filas salteadas

Ejemplo de recursión (2). Numerar filas

Ejemplo de recursión (3). Fechas consecutivas

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries