Análisis Estadístico SQL Parte 1: Cálculo de frecuencias e histogramas

Los desarrolladores de bases de datos y de Business Intelligence (BI) crean un gran número de informes a diario, y los análisis de datos son una parte integral de los mismos. Si te preguntas si puedes realizar análisis estadísticos en SQL, la respuesta es "sí". Lea mi artículo para aprender a hacerlo.

Las estadísticas son muy útiles como etapa inicial de un análisis más profundo, es decir, para la visión general de los datos y la evaluación de su calidad. Sin embargo, las posibilidades de análisis estadístico en SQL son algo limitadas, ya que no hay muchas funciones estadísticas en SQL Server. . Además, un buen conocimiento de la estadística no es muy común entre los profesionales de T-SQL. En SQL Server 2016, se puede utilizar R para calcular todo tipo de medidas estadísticas, pero muchos desarrolladores de SQL Server y administradores de bases de datos no programan en R. Y no todos los sitios se han actualizado a SQL Server 2016.

Esta serie explicará los fundamentos del análisis estadístico de SQL. El código utilizado se basa en mis experiencias de la vida real. Me ocupo de proyectos de BI, especialmente de minería de datos, y a menudo necesito crear muchas consultas estadísticas en las etapas iniciales de un proyecto. Durante estos proyectos, muchas veces el único software en el que puedo confiar es un RDBMS.

Optimización de las consultas estadísticas SQL

La optimización de las consultas estadísticas es diferente a la optimización de las consultas transaccionales. Para calcular las estadísticas, la consulta suele escanear todos los datos. Si la consulta es demasiado lenta, se puede preparar una muestra aleatoria de los datos y escanearla. Sin embargo, si las consultas siguen ciegamente las fórmulas, suelen realizar múltiples escaneos de los datos. Optimizar estas consultas significa minimizar el número de exploraciones. Para conseguirlo, hay que desarrollar un algoritmo que utilice matemáticas adicionales para convertir las fórmulas en equivalentes que puedan optimizarse mejor en SQL Server o en cualquier otro RDBMS. También hay que entender SQL en profundidad. Por ejemplo, necesitas entender muy bien las funciones de ventana y los cálculos de SQL.

Además de explicar la estadística y las consultas estadísticas, esta serie también te dará algunas ideas para optimizar las consultas estadísticas y no estadísticas.

Cómo preparar tus datos para el análisis estadístico SQL

Antes de comenzar el análisis, necesitas entender qué estás analizando. En estadística, se analizan los casos utilizando sus variables. En la terminología del RDBMS, puede pensar en un caso como una fila de la tabla y en una variable como una columna de la misma tabla. Para la mayoría de los análisis estadísticos, se prepara una única tabla o vista. A veces no es tan fácil definir exactamente su caso. Por ejemplo, si está realizando un análisis de riesgo crediticio, podría definir una familia como un caso en lugar de un solo cliente.

Cuando prepare los datos para el análisis estadístico SQL, tendrá que transformar los datos de origen en consecuencia. Para cada caso, debe encapsular toda la información disponible en las columnas de la tabla que va a analizar.

Variables continuas y discretas

Antes de iniciar un análisis serio de los datos, debe comprender cómo se miden los valores de los datos en su conjunto. Es posible que tenga que comprobarlo con un experto en la materia y analizar el sistema empresarial que es la fuente de sus datos. Hay varias formas de medir los valores de los datos y diferentes tipos de columnas:

• Variables discretas puede tomar un valor sólo de un dominio limitado de valores posibles. Los valores discretos incluyen variables categóricas o nominales que no tienen un orden natural. Algunos ejemplos son los estados, los códigos de estado y los colores.
  • Losrangos también pueden tomar un valor sólo de un conjunto discreto de valores. Tienen un orden pero no permiten ninguna aritmética. Algunos ejemplos son los rangos de opinión y los valores numéricos reales agrupados (discretizados).
  • También hay algunos tipos específicos de variables categóricas. Las variables de un solo valor o constantes no son muy interesantes para el análisis porque no aportan ninguna información. Las variables de dos valores o dicotómicas tienen dos valores que son mínimamente necesarios para cualquier análisis. Las variables binarias son variables dicotómicas específicas que sólo toman los valores 0 y 1.
• Variables continuas puede tomar cualquiera de un número ilimitado de valores posibles; sin embargo, el propio dominio puede tener un límite inferior y/o superior.
  • Losintervalos tienen uno o dos límites, tienen un orden y permiten algunas sustracciones de tipo aritmético (pero no siempre permiten la suma). Algunos ejemplos son las fechas, las horas y las temperaturas.
  • Las variablesnuméricas verdaderas admiten toda la aritmética. Algunos ejemplos son las cantidades y los valores.
  • Lasvariables monótonas son un tipo específico de variables continuas que aumentan monótonamente sin límite. Si son simplemente IDs, pueden no ser interesantes. Sin embargo, pueden transformarse (clasificarse en categorías) si el ID que crece constantemente contiene información de orden temporal (los ID más bajos son más antiguos que los más altos).

Datos utilizados para los análisis estadísticos de SQL

Para este y todos los próximos artículos, estoy utilizando la base de datos de demostración AdventureWorksDW2014. Puede descargar una copia de seguridad completa de esta base de datos desde el sitio de ejemplo de SQL Server de Microsoft. Estoy ejecutando todo el código en SQL Server 2016 Developer Edition.

Prefiero utilizar la base de datos de ejemplo AdventureWorks para SQL Server 2014 en lugar de la base de datos de ejemplo WideWorldImportersDW de SQL Server 2016. La base de datos WideWorldImporters es muy útil para demostrar las nuevas características de SQL Server 2016, pero sus datos carecen de las correlaciones y asociaciones necesarias para el análisis estadístico.

Uso de la distribución de frecuencias en SQL para entender las variables discretas

En SQL, la distribución de frecuencias (normalmente presentada en forma de tabla) se utiliza para obtener una visión general rápida de las variables discretas. Puede mostrar los valores reales así como sus:

• Frecuencia absoluta
• Porcentaje absoluto
• Frecuencia acumulada
• Porcentaje acumulado

Además, la distribución de frecuencias de SQL muestra un histograma del porcentaje absoluto de los valores.

A continuación, te mostraré varias formas de calcular la distribución de frecuencias en SQL, empezando por una que es bastante ineficiente.

Distribución de frecuencias en SQL sin usar Funciones de ventana

Calcular la frecuencia absoluta y el porcentaje absoluto de los valores es una agregación directa. Sin embargo, calcular la frecuencia acumulada y el porcentaje acumulado significa calcular totales corridos. Antes de que SQL Server 2012 añadiera compatibilidad con las funciones de agregación de ventana, había que utilizar subconsultas correlacionadas o uniones propias no equitativas para esta tarea. Ninguno de los dos métodos es muy eficiente.

Ejecute el siguiente código, que utiliza subconsultas correlacionadas, para analizar en SQL la distribución de frecuencia de la variable NumberCarsOwned de la vista dbo.vTargetMailen la base de datos de demostración AdventureWorksDW2014.

USE AdventureWorksDW2014;
GO
WITH freqCTE AS
(
SELECT v.NumberCarsOwned,
 COUNT(v.NumberCarsOwned) AS AbsFreq,
 CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) /
       (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc
FROM dbo.vTargetMail AS v
GROUP BY v.NumberCarsOwned
)
SELECT c1.NumberCarsOwned AS NCars,
 c1.AbsFreq,
 (SELECT SUM(c2.AbsFreq)
  FROM freqCTE AS c2
  WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumFreq,
 c1.AbsPerc,
 (SELECT SUM(c2.AbsPerc)
  FROM freqCTE AS c2
  WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumPerc,
 CAST(REPLICATE('*',c1.AbsPerc) AS varchar(100)) AS Histogram
  FROM freqCTE AS c1
ORDER BY c1.NumberCarsOwned;

Esto genera la siguiente salida:

NCars  AbsFreq  CumFreq  AbsPerc  CumPerc  Histogram
-----  -------  -------  -------  -------  -----------------------------------
0         4238     4238       23       23  ***********************
1         4883     9121       26       49  **************************
2         6457    15578       35       84  ***********************************
3         1645    17223        9       93  *********
4         1261    18484        7      100  *******

Distribución de frecuencias en SQL utilizando Funciones de ventana - Solución 1

Cuando se realiza un análisis estadístico en SQL, las funciones de agregación de ventanas resultan útiles. Proporcionan una solución mucho mejor. Como ya se ha señalado, estas funciones están disponibles en las versiones 2012 y posteriores de SQL Server.

Si observa la primera parte de la consulta, se dará cuenta de que la consulta de Expresión de tabla común que calcula los números absolutos es la misma que en la consulta anterior. Sin embargo, los valores acumulativos - los totales corridos - se calculan con la ayuda de las funciones de agregación de la ventana.

WITH freqCTE AS
(
SELECT v.NumberCarsOwned,
 COUNT(v.NumberCarsOwned) AS AbsFreq,
 CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) /
       (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc
FROM dbo.vTargetMail AS v
GROUP BY v.NumberCarsOwned
)
SELECT NumberCarsOwned,
 AbsFreq,
 SUM(AbsFreq) 
  OVER(ORDER BY NumberCarsOwned 
       ROWS BETWEEN UNBOUNDED PRECEDING
	    AND CURRENT ROW) AS CumFreq,
 AbsPerc,
 SUM(AbsPerc)
  OVER(ORDER BY NumberCarsOwned
       ROWS BETWEEN UNBOUNDED PRECEDING
	    AND CURRENT ROW) AS CumPerc,
 CAST(REPLICATE('*',AbsPerc) AS VARCHAR(50)) AS Histogram
FROM freqCTE
ORDER BY NumberCarsOwned;

El resultado de esta consulta es el mismo que el de la consulta anterior.

Distribución de frecuencias en SQL usando Funciones de ventana - Solución 2

He encontrado otra solución interesante utilizando las funciones analíticas de ventana de SQL. La función CUME_DIST calcula la distribución acumulativa, o posición relativa, de un valor en un grupo de valores. Para una fila r, asumiendo un ordenamiento ascendente, el CUME_DIST de r es el número de filas con valores menores o iguales al valor de r, dividido por el número de filas evaluadas en la partición o conjunto de resultados de la consulta. La función PERCENT_RANK calcula el rango relativo de una fila dentro de un grupo de filas. Podemos utilizar PERCENT_RANK para evaluar el rango relativo de un valor dentro de un conjunto de resultados de consulta o partición.

La siguiente consulta de análisis estadístico SQL calcula el número de fila una vez particionado sobre la columna NúmeroDeCochesPropiedad y el número de fila una vez sobre todo el conjunto de entrada. También calcula el rango porcentual y la distribución acumulativa sobre el conjunto de entrada completo.

SELECT NumberCarsOwned AS NCars,
 ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq,
 ROW_NUMBER() OVER(
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq,
 PERCENT_RANK()
  OVER(ORDER BY NumberCarsOwned) AS Pr_AbsPerc, 
 CUME_DIST()
  OVER(ORDER BY NumberCarsOwned, CustomerKey) AS Cd_CumPerc
FROM dbo.vTargetMail;

La salida parcial, que muestra sólo las filas pertinentes para la explicación del algoritmo de cálculo de frecuencias, es:

NCars  Rn_AbsFreq  Rn_CumFre  Pr_AbsPerc         Cd_CumPerc
-----  ----------  ---------  -----------------  --------------------
0               1          1                 0   5.4100843973166E-05
0               2          2                 0   0.000108201687946332
…               …          …                 …   …
0            4238       4238                 0   0.229279376758277
1               1       4239  0.22929178163718   0.229333477602251
…               …          …                 …   …
1            4883       9121  0.22929178163718   0.493453797879247
2               1       9122  0.493480495590543  0.49350789872322
…               …          …                 …   …

Como puede ver, el último número de fila particionado por NumberCarsOwned en una categoría representa en realidad la frecuencia absoluta de los valores en esa categoría. El último número de fila no particionado en una categoría representa la frecuencia acumulada hasta la categoría actual inclusive. Por ejemplo, la frecuencia absoluta para NumberCarsOwned = "0 " es 4.238 y la frecuencia acumulada es 4.238; para NumberCarsOwned = "1", la frecuencia absoluta es 4.883 y la frecuencia acumulada es 9.121.

A continuación, consideremos la función CUME_DIST (la columna Cd_CumPerc en la salida). CUME_DIST, en la última fila de una categoría, devuelve el porcentaje acumulado hasta la categoría, inclusive. Si se resta el PERCENT_RANK (la columna Pr_AbsPerc en la salida) para la última fila en la categoría de la CUME_DIST de la última fila en la misma categoría, se obtiene el porcentaje absoluto para la categoría. Por ejemplo, el porcentaje absoluto para la categoría donde NumberCarsOwned = "1 " es más del 26 por ciento (0,493453797879247 - 0,22929178163718 = 0,264162016242067).

La siguiente consulta calcula la distribución de frecuencias utilizando las observaciones de los resultados de la consulta anterior.

WITH freqCTE AS
(
SELECT NumberCarsOwned,
 ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq,
 ROW_NUMBER() OVER(
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq,
 ROUND(100 * PERCENT_RANK()
  OVER(ORDER BY NumberCarsOwned), 0) AS Pr_AbsPerc, 
 ROUND(100 * CUME_DIST()
  OVER(ORDER BY NumberCarsOwned, CustomerKey), 0) AS Cd_CumPerc
FROM dbo.vTargetMail
)
SELECT NumberCarsOwned AS NCars,
 MAX(Rn_AbsFreq) AS AbsFreq,
 MAX(Rn_CumFreq) AS CumFreq,
 MAX(Cd_CumPerc) - MAX(Pr_Absperc) AS AbsPerc,
 MAX(Cd_CumPerc) AS CumPerc,
 CAST(REPLICATE('*',MAX(Cd_CumPerc) - MAX(Pr_Absperc)) AS varchar(100)) AS Histogram
FROM freqCTE
GROUP BY NumberCarsOwned
ORDER BY NumberCarsOwned;

Aunque la idea de esta última consulta es muy interesante, esta consulta no es tan eficiente como la segunda (que utiliza la función de agregación de ventanas). Por lo tanto, la segunda solución es la recomendada.

Conclusión

En este artículo has aprendido a calcular la distribución de frecuencias de SQL para variables discretas. También has visto una solución que utiliza algo de creatividad. En los siguientes artículos, conocerás otros métodos de análisis estadístico SQL. El siguiente estará dedicado al cálculo de medidas estadísticas básicas para variables continuas. También verás cómo escribir consultas eficientes que requieren conocimientos matemáticos más que creatividad.