Un poco más sobre la función AVG()

La función AVG() nos devuelve el promedio de una columna numérica. O sea que halla la suma de esa columna y luego la divide por la cantidad de filas que no sean NULL, es decir que si en una fila hay NULL no la utiliza en el cálculo.

Para entender mejor a la función AVG() consideremos una tabla que tiene 7 filas, las cuales en una columna numérica tienen estos valores:

20

40

10

20

NULL

NULL

NULL

Ejemplo 1:

El promedio según la función AVG() es:

SELECT
   AVG(MiColumna)
FROM
   MiTabla

90 / 4 = 22,5

Se dividió por 4 y no por 7, porque las filas con NULL para la función AVG() son inexistentes.

Ejemplo 2:

En la misma tabla anterior queremos hallar el promedio de esa columna pero esta vez considerando a las filas que tienen NULL, en ese caso deberíamos escribir:

SELECT
   SUM(MiColumna) / COUNT(*)
FROM
   MiTabla

90 / 7 = 12,8571

Ejemplo 3:

Si lo que nos interesa es hallar el promedio considerando solamente los valores distintos entonces escribiríamos:

SELECT
   AVG(DISTINCT MiColumna)
FROM
   MiTabla

70 / 3 = 23,3333

Hay 3 valores distintos (20, 40, 10) porque uno de ellos (20) está repetido y por lo tanto no se lo usa ni en la suma ni en la cantidad de filas. La suma de los valores distintos (20 + 40 + 10) es 70 y la cantidad de valores distintos es 3, por lo tanto el promedio es 70 / 3 = 23,3333

Artículos relacionados:

La función AVG()

El índice del blog Firebird21

 

Usando un índice descendente

Cuando creamos un nuevo índice y no especificamos si lo queremos ascendente (es decir: 1, 2, 3, 4, 5, etc.) o descendente (es decir: 10, 9, 8, 7, 6, etc.) el Firebird por defecto lo crea ascendente.

¿Por qué?

Porque para la mayoría de los casos es la mejor opción, porque es la que más frecuentemente se usa.

Cuando definimos una restricción Primary Key, Foreign Key o Unique Key el Firebird automáticamente crea un índice para esas restricciones. Y ese índice es siempre ascendente.

¿Y qué sucede si se le envía como argumento a la función MAX() la columna de la Primary Key?

SELECT
   MAX(PRD_IDENTI)     /* PRD_IDENTI es la Primary Key de la tabla PRODUCTOS */
FROM
   PRODUCTOS

Si miramos el PLAN de esta consulta veremos que no usa un índice:

(haciendo clic en la imagen la verás más grande)

¿Por qué no se usó un PLAN?

Porque el índice de la Primary Key, el índice que el Firebird automáticamente crea para esa restricción es siempre ascendente. Si queremos que la función MAX() use un índice en esta consulta entonces tenemos que crear ese índice de forma descendente.

CREATE DESC INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_IDENTI);

Si después de crear un índice descendente para la Primary Key nos fijamos cual es el PLAN que se usará en la consulta de arriba esto será lo que veremos:

(haciendo clic en la imagen la verás más grande)

 O sea que ahora sí el Firebird está usando un índice.

Conclusión:

Verifica siempre cual es el PLAN de tus consultas, a veces agregándoles un índice se puede obtener una ganancia en velocidad impresionantemente grande.

Artículos relacionados:

https://firebird21.wordpress.com/2013/04/30/usando-un-plan/

https://firebird21.wordpress.com/2013/05/03/algo-mas-sobre-plan/

Usando un SELECT dentro de una función agregada

Normalmente dentro de las funciones agregadas: MAX(), MIN(), AVG(), etc., no se puede escribir un SELECT ya que algo como esto:

SELECT
   MAX(SELECT MAX(PRD_PREVTA) FROM PRODUCTOS)
FROM
   CLIENTES

mostrará el error: “Invalid token” o el error “Token unknown”

Sin embargo, hay un truco para conseguirlo y es agregando un nuevo par de paréntesis, así:

SELECT
   MAX((SELECT MAX(PRD_PREVTA) FROM PRODUCTOS))
FROM
   CLIENTES

Solamente una acotación más: la tabla principal (en el ejemplo CLIENTES) debe tener al menos una fila o el resultado obtenido será NULL.

.

La función LIST()

Esta función agregada devuelve una cadena de caracteres consistiendo de todos los valores que no sean NULL en ese grupo. Esos valores pueden estar separados por una coma (por defecto) o por cualquier otro carácter que el programador especifique. Si todos los valores del grupo son NULL o el grupo está vacío, la función LIST() devuelve NULL.

SELECT
   PRD_PROCED,
   LIST(PRD_NOMBRE)
FROM
   PRODUCTOS
GROUP BY
   PRD_PROCED

Este SELECT nos mostrará todos los productos provenientes de cada uno de los países de procedencia. La columna LIST(PRD_NOMBRE) puede ser muy larga ya que en ella se encontrarán todos los productos que provienen de un país.

Como todas las funciones agregadas, LIST() se usa casi siempre con la cláusula GROUP BY.

.

La función MIN()

Esta función agregada nos mostrará:

• Si la columna es numérica, el menor número de todos
• Si la columna es carácter, la palabra que en orden alfabético esté primera
• Si la columna es fecha, la que tiene la fecha más antigua

No tiene en cuenta a los NULL, simplemente los ignora y halla el valor mínimo de las demás columnas.

SELECT
   CLI_CODIGO,
   MIN(CLI_MONTOX) AS MENOR_MONTO_COBRADO
FROM
   COBRANZAS
GROUP BY
   CLI_CODIGO

El SELECT anterior nos mostrará, para cada uno de nuestros clientes, el menor monto de todos los que le hemos cobrado hasta ahora. El alias “MENOR_MONTO_COBRADO” es opcional, no es necesario escribir un alias pero sí es conveniente para que sea más entendible.

Como todas las funciones agregadas, MIN() se usa casi siempre con la cláusula GROUP BY.

La función MAX()

Esta función agregada nos mostrará:

• Si la columna es numérica, el número mayor de todos
• Si la columna es carácter, la palabra que en orden alfabético esté última
• Si la columna es fecha, la que tiene la fecha más reciente

No tiene en cuenta a los NULL, simplemente los ignora y halla el valor máximo de las demás columnas.

SELECT
   CLI_CODIGO,
   MAX(CLI_MONTOX) AS MAYOR_MONTO_COBRADO
FROM
   COBRANZAS
GROUP BY
   CLI_CODIGO

El SELECT anterior nos mostrará, para cada uno de nuestros clientes, el mayor monto de todos los que le hemos cobrado hasta ahora. El alias “MAYOR_MONTO_COBRADO” es opcional, no es necesario escribir un alias pero sí es conveniente para que sea más entendible.

Como todas las funciones agregadas, MAX() se usa casi siempre con la cláusula GROUP BY.

La función SUM()

Esta función agregada nos mostrará la suma de una columna numérica. No tiene en cuenta a los NULL, simplemente los ignora y halla la suma de todos los demás números.

SELECT
   CLI_CODIGO,
   SUM(CLI_MONTOX) AS TOTAL_COBRADO
FROM
   COBRANZAS
GROUP BY
   CLI_CODIGO

El SELECT anterior nos mostrará, para cada uno de nuestros clientes, la suma de todo lo que le hemos cobrado hasta ahora. El alias “TOTAL_COBRADO” es opcional, no es necesario escribir un alias pero sí es conveniente para que sea más entendible.

Como todas las funciones agregadas, SUM() se usa casi siempre con la cláusula GROUP BY.

¿Por qué “casi siempre”?

Porque la cláusula GROUP BY se debe escribir si en la lista de columnas del SELECT hay alguna columna que no es una función agregada. Si en la lista del SELECT solamente hay funciones agregadas entonces no se debe escribir la cláusula GROUP BY.