Análisis estadístico SQL Parte 3: Medición de la dispersión de la distribución

Además de conocer los centros de una distribución en tus datos, necesitas saber cuán variadas son las observaciones. En este artículo, explicaremos cómo encontrar la dispersión de una distribución en SQL.

¿Se trata de una población muy uniforme o muy dispersa? Para entender realmente lo que dicen los números, debes conocer la respuesta a esta pregunta.

En la segunda parte de esta serie, hablamos de cómo calcular los centros de distribución. Al igual que en el caso del centro, hay varias formas de medir la dispersión de la distribución en SQL. Además, hay muchas definiciones diferentes para la dispersión de la distribución. Discutiremos las más populares: el rango, el rango intercuartil, la media absoluta, la desviación media al cuadrado, la varianza, la desviación estándar y el coeficiente de variación. También explicaré el término grados de libertad. Por último, consideraremos la diferencia entre la varianza y la desviación típica para muestras y para poblaciones. Después de repasar esta entrada, serás capaz de encontrar la dispersión de una distribución en SQL por tu cuenta.

Rango

El rango es la distancia llana entre el valor máximo y el valor mínimo que toma la variable. (Una variable es un atributo de una observación, representado como una columna en una tabla). Es la medida más sencilla de la dispersión. La fórmula de la dispersión es

R = vmax - vmin

Las funciones de agregación T_SQL MAX y MIN calculan el rango de una variable, como se muestra a continuación:

USE AdventureWorksDW2014;
SELECT MAX(Age) - MIN(Age) AS Range
FROM dbo.vTargetMail;

El código genera la siguiente salida:

Range
-----
   70

Rango intercuartil

Vamos a sumergirnos en el cálculo de cuartiles en SQL. La mediana es el valor que divide la distribución en dos mitades. Se puede dividir más la distribución, por ejemplo, se puede dividir cada mitad en dos mitades. Esto crea los cuartiles: tres valores que dividen la distribución en cuartos.

Examinemos este proceso de división, la base para calcular los cuartiles en SQL. Se empieza por ordenar las filas (casos, observaciones) en una columna seleccionada (atributo, variable). Se define el rango como la posición absoluta de una fila en la secuencia de filas ordenadas. El rango percentil de un valor es una medida relativa que le indica qué porcentaje de todas (n) las observaciones tienen un valor inferior al valor seleccionado.

Al dividir las observaciones en cuartos, se obtienen tres percentiles (al 25%, 50% y 75% de todas las filas). Puede leer los valores en los cuartiles. El primer cuartil, en el punto del 25%, se denomina cuartil inferior. El segundo cuartil es la mediana (50%). El tercero, en el 75%, es el cuartil superior. Si se resta el cuartil inferior (Q1) del cuartil superior (Q3), se obtiene la fórmula del rango intercuartil (IQR):

IQR = Q3 - Q1

Calcular los cuartiles en SQL y obtener el IQR es tan sencillo como utilizar la función analítica PERCENTILE_CONT:

SELECT DISTINCT 
 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY 1.0*Age) OVER () -
 PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY 1.0*Age) OVER () AS IQR
FROM dbo.vTargetMail;

Esta consulta devuelve el siguiente resultado:

IQR
---
 17

Al igual que la mediana, el IQR es resistente al cambio. Esto significa que no es sensible a una oscilación salvaje en una sola observación. La resistencia es lógica, porque sólo utiliza dos observaciones clave. Cuando se observa una gran diferencia entre el rango y el rango intercuartil de la misma variable, esto significa que algunos valores de la distribución están bastante alejados del valor medio.

Desviación media absoluta

Para la IQR, sólo se utilizan dos observaciones clave: el cuartil inferior y el superior. ¿Existe una medida que tenga en cuenta todas las observaciones? Sí.

Puede medir la distancia entre cada valor y el valor medio y llamarla desviación. La suma de todas las distancias te da una medida de la dispersión de tu población. Pero hay que tener en cuenta que algunas de las distancias son positivas mientras que otras son negativas; en realidad, se anulan mutuamente, por lo que el total da exactamente cero.

Lo mismo ocurre si se hace una media de las desviaciones, por lo que sería una medida inútil de la dispersión. Este problema se resuelve ignorando los signos positivos/negativos y utilizando los valores absolutos de las distancias entre los valores y la media.

Calculando la media de las desviaciones absolutas, se obtiene la fórmula de la desviación media absoluta (DMA):

A partir de la fórmula de la DAM, se puede ver que primero hay que calcular la media. Al principio, es tentador intentar esto utilizando la función de agregación AVG y utilizando el resultado como entrada en la función SUM. Sin embargo, SQL Server no puede realizar una función de agregación en una expresión que contenga un agregado o una subconsulta; por lo tanto, tenemos que almacenar el valor medio (de AVG) en una variable:

DECLARE @mean AS NUMERIC(10,2);
SET @mean = (SELECT AVG(1.0*YearlyIncome) FROM dbo.vTargetMail);
SELECT SUM(ABS(YearlyIncome - @mean))/COUNT(*) AS MAD
FROM dbo.vTargetMail;

Se obtiene la siguiente salida:

MAD
------------
25474.966405

Desviación media al cuadrado

Otra forma de evitar los problemas de los signos de desviación es elevar al cuadrado cada desviación. Con una ligera modificación de la fórmula MAD - concretamente, calculando la media de las desviaciones al cuadrado en lugar de las desviaciones absolutas - se obtiene la fórmula de la desviación media al cuadrado (MSD):

Quizás te hayas preguntado por qué no he utilizado ninguna función de agregación de ventanas para el cálculo de la DAM. Por supuesto, esto es posible. Lo haré para el cálculo de la DMS. Probemos con la siguiente consulta.

SELECT 
 SUM( 
  SQUARE(YearlyIncome - 
   (AVG(1.0*YearlyIncome) OVER())
  )
 ) / COUNT(*) AS MSD
FROM dbo.vTargetMail;

Por desgracia, este enfoque es bastante ingenuo. La consulta devuelve error 4109 - windowed functions cannot be used in the context of another windowed function or aggregate.

Es necesario utilizar la función de agregación de la ventana dentro de una expresión de tabla común, y luego hacer la agregación final en una consulta externa. El siguiente fragmento de código muestra cómo se hace:

WITH MSDCTE AS
(
SELECT 
  YearlyIncome, 
  AVG(1.0*YearlyIncome) OVER() AS Deviation
FROM dbo.vTargetMail
)
SELECT SUM(SQUARE(YearlyIncome - Deviation)) / COUNT(*) AS MSD
FROM MSDCTE;

Esto devuelve el resultado deseado:

MSD
----------------
1042319181.07085

Grados de Libertad y Varianza

Supongamos que sólo tiene una observación (n=1). Esta observación es también su media muestral, pero no hay ninguna dispersión. Sólo puede calcular la dispersión si n es superior a 1. Sólo los datos (n-1) le ayudan a calcular la dispersión, teniendo en cuenta que la primera observación es la media. Estos datos se denominan grados de libertad.

Piense en los grados de libertad como el número de piezas de información que pueden variar. Por ejemplo, imagine una variable que puede tomar cinco estados discretos diferentes. Sólo es necesario calcular las frecuencias de cuatro estados para conocer la distribución de la variable; la frecuencia del último estado viene determinada por las frecuencias de los cuatro primeros estados calculados. No pueden variar porque el porcentaje acumulado de todos los estados debe ser igual a 100.

La suma de todas las desviaciones, sin ignorar los signos positivos/negativos, es siempre cero. Por ello, la fórmula de la varianza utiliza las desviaciones al cuadrado. Sólo hay (n-1) desviaciones libres; la última está estrictamente determinada por las demás. La definición de la varianza (Var) es similar a la definición de la DMS; basta con sustituir el número de casos n por los grados de libertad (n-1):

Esta es la fórmula de la varianza de una muestra, que puede utilizarse como estimador de la varianza de la población. Ahora imagine que sus datos representan la población completa. En ese caso, todas las observaciones contribuyen al cálculo de la varianza por igual, y los grados de libertad no tienen sentido. La varianza de una población (VarP) se define, entonces, con la misma fórmula que la DMS:

Por supuesto, cuando se tiene una muestra grande, la diferencia entre Var y VarP es mínima.

Transact-SQL incluye una función agregada (la función VAR) que calcula la varianza de una muestra como estimador. La función VARP calcula la varianza de la población. Utilizar cualquiera de las dos en una consulta es muy sencillo. El siguiente ejemplo calcula ambas varianzas para la columna "YearlyIncome". También las compara de dos maneras: dividiéndolas y dividiendo el número de casos menos uno entre el número de casos. Esto último muestra que la diferencia es sólo el resultado de los grados de libertad utilizados en el cálculo de la varianza de la muestra como estimador de la varianza de la población:

SELECT VAR(1.0*YearlyIncome) AS SampleVariance,
  VARP(1.0*YearlyIncome) AS PopulationVariance,
  VARP(1.0*YearlyIncome) / VAR(1.0*YearlyIncome) AS SampleVsPopulation1,
  (1.0 * COUNT(*) - 1) / COUNT(*) AS SampleVsPopulation2
FROM dbo.vTargetMail;

La consulta devuelve el siguiente resultado:

SampleVariance   PopulationVariance SampleVsPopulation1 SampleVsPopulation2
---------------- ------------------ ------------------- -------------------
1042375574.46912 1042319181.07081   0.999945899156027   0.999945899156

Desviación estándar y coeficiente de variación

Para compensar el hecho de tener las desviaciones al cuadrado en la fórmula de la varianza, se puede tomar la raíz cuadrada de la varianza. Esta es la definición de la desviación estándar (σ):

Puede utilizar esta fórmula para calcular la desviación estándar en SQL, tanto de la población como de una muestra: sólo tiene que utilizar la varianza adecuada en la fórmula.

Supongamos que derivamos las medidas absolutas de una dispersión. La interpretación es bastante evidente para una sola variable: cuanto mayores sean los valores de las medidas, más dispersa estará la variable en las observaciones. Pero las medidas absolutas no pueden utilizarse para comparar la dispersión entre dos o más variables. Por lo tanto, tenemos que derivar medidas relativas.

Podemos derivar las medidas relativas de la dispersión para cualquiera de las medidas absolutas mencionadas. Sin embargo, sólo haremos la más popular: la desviación estándar. La definición de la desviación típica relativa (también conocida como coeficiente de variación, o CV) es una simple división de la desviación estándar por el valor medio:

T-SQL incluye una función agregada para calcular la desviación estándar en SQL para la población (STDEVP) y otra para calcular la desviación estándar de una muestra como estimador (STDEV). Por lo tanto, calcular la desviación estándar en SQL, así como el coeficiente de la variación, es sencillo. La siguiente consulta calcula las desviaciones estándar para la "edad" y "Ingresos anuales" y el coeficiente de variación de las mismas:

SELECT STDEV(1.0*Age) AS StDevAge,
 STDEV(1.0*YearlyIncome) AS StDevIncome,
 STDEV(1.0*Age) / AVG(1.0*Age) AS CVAge,
 STDEV(1.0*YearlyIncome) / AVG(1.0*YearlyIncome) AS CVIncome
FROM dbo.vTargetMail;

Este es el resultado:

StDevAge          StDevIncome       CVAge              CVIncome
----------------  ----------------  -----------------  -----------------
11.5178146121881  32285.8417029682  0.241654328044298	  0.563395923529214

Después de calcular la desviación estándar en SQL se puede ver que la desviación estándar para "Ingresos anuales" es mucho mayor que la de "Edad"sin embargo, la dispersión relativa, el coeficiente de variación, no es tan diferente.

Conclusión

Los centros de distribución, especialmente el valor medio, son probablemente las medidas de las que más se abusa en estadística. La media no significa mucho sin mencionar la dispersión. Hay varias medidas de dispersión: la desviación estándar, la varianza y el coeficiente de variación son las más importantes. Como ahora sabes más sobre la medición de la dispersión de una distribución en SQL y temas como el cálculo de cuartiles en SQL o la escritura de consultas para obtener la desviación estándar en SQL no te son extraños, ¡has llevado tu análisis estadístico al siguiente nivel!