En este artículo, aprenderemos la función SQL Average que se conoce como función AVG() en T-SQL. La función AVG() es una función agregada que calcula el valor medio de un conjunto de datos numéricos que devuelve la sentencia SELECT.
Introducción
Supongamos que tenemos una colección de números. En primer lugar, sumamos todos los miembros de la colección y luego dividimos el número total de miembros de la colección. Como resultado, el número obtenido será la media. Vamos a explicar esta noción matemática con un ejemplo sencillo.
Juan es un estudiante de la universidad y decide registrar sus gastos cada día. El siguiente gráfico y tabla representan los gastos de la última semana de Juan.
Ahora, calcularemos el gasto medio de la última semana de Juan. En un primer momento, sumaremos todos los gastos de la citada semana.
($20+$60+$20+$42+$10+$15+$8) = $175 es el importe total de los gastos de la semana.
En el segundo paso, dividiremos el importe total de los gastos entre 7 porque esta colección se forma sobre 7 miembros. En otras palabras, una semana está formada por siete días.
175 dólares / 7 = 25 dólares es el gasto medio de la semana.
Después de discutir el concepto matemático de la media, vamos a seguir aprendiendo los fundamentos de la función AVG() en SQL.
Preparación de los datos
Con la ayuda de la siguiente consulta, crearemos la tabla WeekExpense y luego insertaremos los gastos de 3 semanas de Juan. Utilizaremos esta tabla en todos los ejemplos de este artículo.
|
1
2
3
4
5
6
7
8
9
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
CREATE TABLE WeekExpense
( WeekNumber VARCHAR(20),WeekDayName VARCHAR(50), Expense MONEY)
INSERT INTO WeekExpense
VALORES
(‘Week05′,’Monday’, 20 ),
( ‘Week05′,’Tuesday’, 60 ),
( ‘Semana05′,’Miércoles’, 20 ),
( ‘Semana05′,’Jueves’, 42 ),
( ‘Semana05′,’Viernes’, 10 ),
( ‘Semana05′,’Sábado’, 15 ) ,
(‘Semana05′,’Domingo’, 8 ),
(‘Semana04′,’Lunes’, 29 ),
(‘Semana04′,’Martes’, 17 ),
(‘Semana04′,’Miércoles’, 42 ),
(‘Semana04′,’Jueves’, 11 ),
(‘Semana04′,’Viernes’, 43 ),
(‘Semana04′,’Sábado’, 10 ) ,
(‘Semana04′,’Domingo’, 15 ),
(‘Semana03′,’Lunes’, 10 ),
(‘Semana03′,’Martes’, 32 ),
(‘Semana03′,’Miércoles’, 35 ),
(‘Semana03′,’Jueves’, 19 ),
( ‘Semana03′,’Viernes’, 30 ),
( ‘Semana03′,’Sábado’, 10 ) ,
( ‘Semana03′,’Domingo’, 15 )
GO
SELECT *
FROM WeekExpense
|
Sintaxis de la función de Promedio SQL
La función de sintaxis AVG() tendrá el siguiente aspecto en su forma simple:
|
1
2
3
|
SELECT AVG ( columname )
FROM TABLENAME
WHERE CONDITION
|
La palabra clave ALL nos permite calcular una media para todos los valores del conjunto de resultados y se utiliza por defecto. La palabra clave DISTINCT implementa la función AVG() sólo para valores únicos.
Ejemplo de función AVG()
La siguiente consulta calculará el gasto medio de Juan con la ayuda de la función AVG().
|
1
2
3
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber = ‘Week05’
|
La siguiente imagen ilustra la metodología de cálculo de la función AVG() mediante su uso por defecto.
Como podemos ver, AVG() considera todos los días de la semana y los fines de semana en su cálculo. Además, podemos obtener el mismo resultado cuando añadimos la palabra clave ALL a la sintaxis.
|
1
2
3
|
SELECT AVG(ALL Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Si queremos ignorar los valores duplicados durante el cálculo de la función AVG(), podemos utilizar la palabra clave DISTINCT. Después de ejecutar la consulta de abajo, analicemos el resultado:
|
1
2
3
|
SELECT AVG(DISTINCT Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
.
La palabra clave DISTINCT elimina los valores duplicados, por lo tanto, sólo tiene en cuenta uno de los gastos cuyos valores son $20 en el cálculo. La siguiente imagen ilustra básicamente el mecanismo de funcionamiento de la palabra clave DISTINCT.
Función de promedio SQL y valores NULL
La función AVG() no considera los valores NULL durante su cálculo. Ahora, estudiaremos un ejemplo de este problema. Al principio, actualizaremos los gastos del domingo como NULL en la tabla WeekExpense.
|
1
2
3
4
5
|
UPDATE WeekExpense
SET
Expense = NULL
WHERE WeekDayName = ‘Sunday’ AND
WeekNumber = ‘Week05’
|
Ahora, ejecutaremos la siguiente consulta para calcular el valor medio.
|
1
2
3
SELECT AVG(Expense) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
Como podemos ver, el valor NULL no fue tenido en cuenta por la función AVG() en el cálculo. La siguiente imagen ilustra el método de cálculo:
Si queremos incluir los valores NULL en el cálculo, podemos utilizar la función ISNULL. La función ISNULL se utiliza para cambiar los valores NULL en los valores definidos. Así que ejecutaremos lo siguiente para incluir las expresiones NULL en el cálculo.
|
1
2
3
|
SELECT AVG(ISNULL(Expense,0)) AS
FROM WeekExpense
WHERE WeekNumber =’Week05′
|
La siguiente imagen ilustra el método de cálculo de la consulta anterior:
En este punto, debemos remarcar aquí es que incluimos la expresión NULL al cálculo como 0.
Uso de la función Promedio SQL con la sentencia GROUP BY
La sentencia GROUP BY se utiliza para agrupar los datos y se usa principalmente con las funciones de agregación.
Juan decidió calcular el gasto medio de todas las semanas. Para manejar el problema de John, necesitamos usar la sentencia GROUP BY y la función AVG() al mismo tiempo. La siguiente consulta calculará el gasto medio por cada semana individual.
|
1
2
3
4
|
SELECT WeekNumber, AVG(Expense) AS
FROM WeekExpense
GROUP BY WeekNumber
ORDER BY WeekNumber DESC
|
Cuando hemos echado un vistazo al conjunto de resultados de la consulta, podemos ver que los promedios se calcularon para todas las semanas por separado.
Consejo de bonificación: Detalles del plan de ejecución de la función Promedio SQL
El plan de ejecución ayuda a entender los detalles de ejecución de una consulta. Cuando analizamos el plan de ejecución de una consulta, obviamente podemos entender lo que ocurre entre bastidores.
Ahora, analizaremos el siguiente plan de ejecución real de la consulta con ApexSQL Plan para que podamos entender claramente lo que ocurre entre bastidores de la función AVG().
|
1
2
|
SELECT AVG(Expense) AS
FROM WeekExpense
|
El plan de ejecución visual de la consulta se muestra a continuación:
El escaneo de la tabla leyó todas las filas de las tablas porque no creamos ningún índice en esta tabla. En el siguiente paso, el operador Stream aggregate computa la suma y el valor de conteo de las expresiones.
Compute scalar toma estas expresiones de Stream aggregate y calcula el valor promedio a través de la siguiente fórmula.
|
1
2
3
4
5
CASE
WHEN = (0)
THEN NULL
ELSE / CONVERT_IMPLICIT(money, , 0)
END
|
En esta fórmula, debemos remarcar un punto. Cuando el número total de las expresiones devuelva 0, el cálculo del promedio devolverá NULL. Si el número total de expresiones devuelve 0, el valor del promedio será NULL. La intención principal de esto es evitar el error de dividir por cero.
Conclusión
En este artículo, hemos aprendido la función Promedio de SQL y hemos reforzado nuestro aprendizaje con ejemplos e ilustraciones básicas. En el camino, discutimos los detalles del plan de ejecución de la función AVG().
- Autor
- Puestos recientes
La mayor parte de su carrera se ha centrado en la administración y el desarrollo de bases de datos de SQL Server. Sus intereses actuales se centran en la administración de bases de datos y Business Intelligence. Puedes encontrarlo en LinkedIn.
Ver todos los posts de Esat Erkec
- Mitos de la optimización de consultas – 23 de marzo, 2021
- Síntomas de la detección de parámetros en SQL Server – 17 de marzo de 2021
- Utilización de la corrección automática del plan para el ajuste de consultas – 4 de marzo de 2021
.