Precaución al usar la función SUM()

3 comentarios

A veces puede parecer que se obtendrá el mismo resultado escribiendo un SELECT que tiene una función SUM() de una forma o de otra, pero lo que se obtiene no es lo esperado. Veamos un ejemplo:

SUMA1

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

Listado 1.

SELECT
   SUM(MOV_PRECIO) / SUM(MOV_CANTID) AS RESULTADO
FROM
   MOVIMDET

SUMA2

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

Listado 2.

SELECT
   SUM(MOV_PRECIO / MOV_CANTID) AS RESULTADO
FROM
   MOVIMDET

SUMA3

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

¿Por qué no se obtiene el mismo resultado? ¿Acaso ambos resultados no deberían ser idénticos?

Pues no, en el Listado 1. se le pide que sume todos los valores de la columna MOV_PRECIO y los divida por la suma de todos los valores de la columna MOV_CANTID

Suma de MOV_PRECIO = 200

Suma de MOV_CANTID = 2

Al dividir ambas sumas el resultado es 100

Sin embargo, en el Listado 2. para cada fila primero se halla el resultado de la división y luego se realiza la suma.

100 / 1 = 100

100 / 1 = 100

Al sumar ambos resultados se obtiene 200

El motivo por el cual se obtienen resultados diferentes es que en el Listado 1. se suman todas las filas de una sola vez, en cambio en el Listado 2. se suman de una en una.

Cuidado con esto, si no entiendes bien este concepto podrías estar obteniendo resultados incorrectos.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Anuncios

¿Por qué deben escribirse primero los INNER JOIN?

Deja un comentario

En el artículo:

Poniendo los JOIN en el orden correcto

ya había mostrado que los INNER JOIN siempre deben escribirse antes que los OUTER JOIN pero no había explicado claramente el motivo del por qué deba ser así.

Me explayaré más sobre ese tema ahora.

Si tenemos una consulta como la siguiente:

Listado 1

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición1
JOIN
   MiTabla3
      ON MiCondición2

El optimizador del Firebird puede cambiar el orden de las tablas pues como se trata de un INNER JOIN todas las filas deben estar emparejadas y por lo tanto no importa cual se coloque primero. O sea que si después de la cláusula FROM ponemos a MiTabla1 o la ponemos a MiTabla2 o la ponemos a MiTabla3 el resultado no variará, será idéntico. Así mismo, en el primer o en el segundo JOIN podemos poner a MiTabla1, a MiTabla2, o a MiTabla3, y siempre obtendremos lo mismo.

Sin embargo, aunque el resultado final será el mismo, la eficiencia no será la misma. El optimizador del Firebird realiza su tarea mirando (entre otras cosas) la selectividad de los índices y el tiempo que demorarán. Puede quizás descubrir que para obtener la máxima eficiencia después de la cláusula FROM debe colocar a MiTabla2 y que el primer JOIN debe ser con MiTabla3.

Entonces, si lo considera necesario el optimizador puede tranquilamente poner a otra tabla después del FROM o cambiar el orden de los JOIN, no hay problema con eso porque el resultado obtenido será exactamente el mismo.

En síntesis, el optimizador intentará optimizar la consulta.

¿Y qué ocurre cuándo escribimos un LEFT JOIN?

Listado 2

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
LEFT JOIN
   MiTabla2
      ON MiCondición1

Listado 3

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla2
LEFT JOIN
   MiTabla1
      ON MiCondición1

Lo que ocurre es que los resultados devueltos por Listado 2 y por Listado 3 ya no serán idénticos si alguna de las columnas escritas después de la clásula SELECT puede ser NULL o si (este es el caso más común) la cantidad de filas de MiTabla1 no es igual a la cantidad de filas de MiTabla2.

Ejemplo: Si MiTabla1 tiene 5 filas y MiTabla2 tiene 20 filas, el resultado del SELECT puede tener 5 filas (si después de la cláusula FROM escribimos MiTabla1) o puede tener 20 filas (si después de la cláusula FROM escribimos MiTabla2). Evidentemente, son muy diferentes esos resultados.

En el Listado 1 no importaba cual tabla se escribía en el JOIN porque como todas las filas debían estar emparejadas daba lo mismo poner a una tabla o a la otra. Sin embargo, en el Listado 2 y en el Listado 3 eso ya no es verdad, aquí sí importa cual tabla se escribe en el LEFT JOIN y los resultados obtenidos pueden ser muy distintos en cada caso.

Conclusión:

Siempre que ejecutamos un SELECT el optimizador del Firebird intenta que ese SELECT muestre los resultados lo más rápidamente posible, para ello mira entre otras cosas la selectividad de los índices. Cuando usamos INNER JOIN puede cambiar el orden de las tablas porque eso no importa, ya que el resultado final será el mismo y si descubre que cambiando el orden de las tablas los resultados se mostrarán más rápido, entonces cambia el orden de las tablas. Pero cuando usamos LEFT JOIN ya no puede cambiar el orden de las tablas porque en la gran mayoría de los casos los resultados serán distintos. Entonces, deja de optimizar, allí mismo termina su tarea. La primera vez que encuentra un LEFT JOIN, RIGHT JOIN, o FULL JOIN, finaliza la optimización. Y es lógico, porque ya nada puede mejorarse.

Entonces, si nuestro SELECT conteniendo un OUTER JOIN (o sea, LEFT JOIN, RIGHT JOIN, o FULL JOIN) estaba optimizado, todo bien. Si no estaba optimizado, todo mal, ya que el optimizador del Firebird no lo mejorará.

¿Corolario?

Siempre, pero siempre, en todos los casos, debemos escribir primero los INNER JOIN y solamente después de ellos escribir los OUTER JOIN. De esa manera nos aseguraremos de que el optimizador del Firebird haga su trabajo y mejore si es posible la velocidad con la cual se obtienen los resultados.

Artículos relacionados:

Poniendo los JOIN en el orden correcto

El índice del blog Firebird21

El foro del blog Firebird21

El resultado de un SELECT es una tabla

3 comentarios

Un concepto que debes tener bien presente y entenderlo muy bien es que en Firebird siempre, sí o sí, en el 100% de los casos, lo que devuelve un SELECT es una tabla.

Cuando ejecutas un SELECT lo que obtienes se llama conjunto resultado.

Y como recordarás de cuando estudiaste Teoría de Conjuntos en Matemática Moderna, un conjunto puede estar vacío, tener un solo elemento, o tener más de un elemento.

Eso exactamente ocurre con lo que devuelve un SELECT.

En el caso del Firebird ese conjunto resultado siempre es una tabla. Esa tabla no tiene un nombre específico, tú puedes llamarla como quieras, pero en la literatura SQL normalmente se la conoce como SQL_RESULT y por lo tanto será el nombre que usaremos en este artículo.

Una tabla, puede tener cero filas, una fila o más de una fila. Y estar compuesta por cero columnas, una columna o más de una columna.

Por lo tanto, la tabla obtenida puede contener:

  • 0 filas y 0 columnas
  • 1 fila y 1 columna
  • 1 fila y varias columnas
  • varias filas y una columna
  • varias filas y varias columnas

Puedes notar que si no hay filas, no hay columnas. Y si hay filas, la cantidad de columnas puede variar, entre una y muchas.

Algo muy importante a tener en cuenta es que como lo que obtienes al ejecutar un SELECT es una tabla, entonces puedes usar esa tabla obtenida dentro de cualquier comando del Firebird: INSERT, UPDATE, DELETE, SELECT.

Veamos algunos ejemplos:

Ejemplo 1. Un SELECT sencillo

SELECT
   *
FROM
   MiTabla1

En este caso el conjunto resultado (el cual, como recordarás es una tabla de nombre SQL_RESULT) consistirá de todas las filas y de todas las columnas de la tabla MiTabla1. ¿Por qué? porque el asterisco sirve para pedir todas las columnas de una tabla. Y como en este caso no se filtró el resultado (eso se hace con las cláusulas WHERE, JOIN, GROUP BY, HAVING, DISTINCT, FIRST, ROWS), se obtienen también todas las filas.

Por lo tanto la tabla SQL_RESULT (es decir, la tabla obtenida al ejecutar el SELECT) es una copia exacta de la tabla original.

Ejemplo 2. Un SELECT con pocas columnas

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1

Aquí la tabla SQL_RESULT consiste de 3 columnas de MiTabla1 y de todas sus filas. SQL_RESULT tiene todas las filas de MiTabla1 porque no se filtró el resultado.

Ejemplo 3. Un SELECT con una subconsulta

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)
FROM
   MiTabla1

En este caso la tercera columna de la tabla SQL_RESULT se obtuvo de otra tabla, por eso se trata de una subconsulta. Recuerda que MiTabla1 y MiTabla2 pueden tener muchas columnas, pero las columnas de la tabla temporaria SQL_RESULT son las que se piden en el SELECT principal.

Eso significa que la tabla SQL_RESULT tendrá 3 columnas, 2 de ellas provenientes de MiTabla1 y 1 proveniente de MiTabla2.

¿Qué condiciones debe cumplir la subconsulta?

Como puedes ver el Ejemplo 3 es muy similar al Ejemplo 2, la diferencia se da solamente en la tercera columna, que es diferente. Entonces, en la tercera columna del Ejemplo 3 debe colocarse un valor, un solo valor, nunca más de un valor. Ese valor puede ser NULL (si ninguna columna de MiTabla2 cumple con MiCondición) o distinto de NULL (si una y solamente una fila de MiTabla2 cumple con MiCondición).

Por lo tanto, las condiciones a cumplir son:

  1. que en la subconsulta haya solamente una columna (nunca más de una columna)
  2. que una y solamente una fila de MiTabla2 cumpla con MiCondición.

Ejemplo 4. Un SELECT con filtro

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
WHERE
   MiValor = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Aquí, la condición de filtro (es decir, la especificada en la cláusula WHERE) se obtuvo de una subconsulta.

Como puedes ver, se puede usar sin problemas un SELECT en la cláusula WHERE. Pero para ello es requisito ineludible que ese SELECT devuelva cero filas o una sola fila. Y una sola columna. Si el conjunto resultado de la subconsulta está compuesto por más de una fila o por más de una columna, eso causará un error, ya que en la variable MiValor solamente se puede guardar un valor, no muchos.

Ejemplo 5. Un SELECT con filtro múltiple

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
WHERE
   MiValor IN (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

En este caso como no se usó = sino que se usó IN, la subconsulta puede devolver varios valores. O sea que puede devolver varias filas, aunque todas esas filas tendrán una sola columna. Recuerda que IN es una forma simplificada de escribir varios OR

MiValor IN (4, 10, 15) es lo mismo que escribir: MiValor = 4 OR MiValor = 10 OR MiValor = 15

El ahorro se vuelve muy notorio cuando la cantidad de valores distintos aumenta.

Ejemplo 6. Un SELECT con agrupamiento

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)
FROM
   MiTabla1
GROUP BY
   MiColumna1,
   MiColumna2,
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

En este caso la subconsulta fue utilizada en la lista de columnas que tendrá la tabla SQL_RESULT y como queríamos obtener los datos agrupados, esa misma subconsulta tuvimos que usar en la clásula GROUP BY.

Lo importante a notar es que podemos, sin problema, agrupar por una subconsulta.

Ejemplo 7. Un SELECT ordenado

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
ORDER BY
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Aquí ordenamos a la tabla SQL_RESULT según una columna perteneciente a otra tabla.

Ejemplo 8. Un JOIN usando una subconsulta

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiColumna1 = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Aquí la condición del JOIN se obtuvo de una subconsulta.

Ejemplo 9. Una tabla derivada

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   (SELECT MiColumna1, MiColumna2, MiColumna3 FROM MiTabla2 WHERE MiCondición)

Cuando a continuación de la cláusula FROM se pone un SELECT, ya no se le llama subconsulta sino tabla derivada.

El requisito es que si se usa alguna de las columnas de la tabla derivada en el SELECT principal, el nombre sea idéntico. Normalmente se usan todas las columnas de la tabla derivada, pero no es obligatorio.

Ejemplo 10. Un INSERT desde una subconsulta

INSERT INTO
   MiTabla1
SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3,
   MiColumna4
FROM
   MiTabla2
WHERE
   MiCondición

Si MiTabla1 tiene 4 columnas, entonces podemos escribir un INSERT como el anterior para agregarle una o más filas.

En este caso el SELECT puede devolver más de una fila. Si el SELECT devuelve 892 filas entonces 892 filas podrán ser agregadas con este simple comando.

Este ejemplo es útil cuando la tabla SQL_RESULT (o sea, la tabla obtenida al ejecutar el SELECT) tiene la misma cantidad de columnas que la tabla donde se insertarán las filas.

Ejemplo 11. Otro INSERT desde una subconsulta

INSERT INTO
   MiTabla1 (
      MiColumna11,
      MiColumna12,
      MiColumna13
   )
SELECT
   MiColumna21,
   MiColumna22,
   MiColumna23
FROM
   MiTabla2
WHERE
   MiCondición

Este tipo de INSERT usamos cuando la cantidad de columnas que tiene la tabla SQL_RESULT no es la misma cantidad de columnas que tiene la tabla MiTabla1. Nuestra tabla MiTabla1 quizás tiene 8 columnas, pero solamente 3 de ellas estamos usando en este INSERT, por eso debemos especificar cuales son esas columnas, poniéndolas entre paréntesis.

Ejemplo 12. Un UPDATE desde una subconsulta

UPDATE
   MiTabla1
SET
   MiColumna1 = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Así como podemos usar una subconsulta con el comando INSERT, también podemos usar una subconsulta con los comandos UPDATE y DELETE.

El requisito es que la subconsulta devuelva un solo valor, o ninguno. Si en la subconsulta hay más de una columna o si SQL_RESULT tiene más de una fila, eso provocará un error.

Ejemplo 13. Otro UPDATE desde una subconsulta

UPDATE
   MiTabla1
SET
   MiColumna1 = AlgúnValor
WHERE
   MiValor = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Así como podemos poner una subconsulta en la columna que queremos actualizar (como vimos en el Ejemplo 12.), también podemos poner una subconsulta para filtrar con la cláusula WHERE, como vemos en este ejemplo.

Ejemplo 14. Obteniendo el valor de una columna en un stored procedure o en un trigger

Dentro de un stored procedure o dentro de un trigger, podemos escribir algo como:

MiValor = (SELECT MiColumna1 FROM MiTabla1 WHERE MiCondición1);

El contenido de la columna MiColumna1 se guardará en la variable MiValor.

La condición, como es habitual en estos casos, es que SQL_RESULT tenga una sola fila (o ninguna fila, en cuyo caso MiValor será NULL) y una sola columna.

Si SQL_RESULT tiene más de una fila o más de una columna, la asignación anterior no podrá realizarse y provocará un error.

Conclusión:

Lo que obtienes al ejecutar un SELECT es un conjunto de resultados. Ese conjunto de resultados puede tener 0 elementos, 1 elemento, o más de 1 elemento. En Firebird ese conjunto de resultados es tratado como una tabla temporaria. A esa tabla temporaria comúnmente se la llama SQL_RESULT.

O sea que siempre y cada vez que se ejecuta un SELECT se crea una tabla temporaria, cuyo nombre es SQL_RESULT. (Salvo que se ejecuten varios SELECT, en cuyo caso los nombres serán SQL_RESULT, SQL_RESULT2, SQL_RESULT3, etc.)

Como una tabla tiene filas y columnas, y SQL_RESULT es una tabla, entonces puede usarse en cualquier lugar donde un valor es necesitado.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

Optimizando un SELECT al usar una tabla derivada

Optimizando las subconsultas

El índice del blog Firebird21

El foro del blog Firebird21