Cuando se analizan datos, es importante tener una buena comprensión de los datos con los que se está trabajando. Esto requiere ejecutar funciones y medidas estadísticas para obtener información sobre los datos. Sin embargo, al trabajar con datos en una base de datos de SQL Server, es posible que encuentre valores desconocidos o faltantes, valores atípicos y otros valores no deseados. Estos problemas pueden tener un impacto significativo en su análisis y llevar a interpretaciones falsas.

Una forma de abordar estos problemas es a través de la limpieza de datos, que implica detectar, encontrar y recodificar estos valores. SQL Server proporciona varias funciones estadísticas integradas que se pueden utilizar para explorar los datos. Estas funciones incluyen funciones matemáticas (ABS, COS, SIN, EXP, LOG, PI, SQRT, etc.), funciones de agregado (MIN, MAX, AVG, SUM, COUNT, VAR, VARP, etc.), funciones de clasificación (RANK, NTILE, DENSE, etc.) y funciones de cadena (SOUNDEX, DIFFERENCE, STUFF, REVERSE, etc.). Estas funciones son fáciles de usar y se pueden utilizar para construir funciones estadísticas más complejas.

Además de las funciones integradas, SQL Server también proporciona algoritmos para el perfilado de datos dentro de SSIS y otros enfoques para comprender los datos, como la coincidencia difusa, la deduplicación y la detección de valores atípicos.

Sin embargo, analizar datos utilizando solo SQL Server puede llevar mucho tiempo, especialmente al lidiar con código T-SQL largo y ejecutar pruebas estadísticas. Para simplificar el proceso y obtener capacidades estadísticas más avanzadas, puede utilizar el lenguaje de programación R.

Introducción a R

R es un lenguaje y entorno de software para computación y gráficos estadísticos. Es un programa de código abierto que se puede descargar de forma gratuita desde el sitio web oficial de R. R es fácilmente adaptable y tiene una amplia gama de bibliotecas para realizar diversos análisis estadísticos. También tiene bibliotecas para conectarse a motores de bases de datos, Excel y otros programas.

Usar R para la exploración de datos tiene varias ventajas. En primer lugar, es un programa potente que está ganando popularidad entre los estadísticos. En segundo lugar, es gratuito, lo que puede ser un factor importante de ahorro de costos en comparación con otros programas estadísticos. En tercer lugar, R tiene una comunidad en crecimiento y muchos recursos en línea disponibles para el aprendizaje.

Aunque R no es un motor de bases de datos como SQL Server, se puede utilizar en conjunto con SQL Server para realizar análisis estadísticos en sus datos. Tradicionalmente, los estadísticos exportaban datos de SQL Server a programas de terceros como SAS, SPSS o Excel para calcular estadísticas. Este proceso era lento e ineficiente. Para abordar este problema, se ha desarrollado un procedimiento llamado sp_getStatistics. Este procedimiento le permite alimentar datos desde su tabla de SQL Server, realizar cálculos estadísticos en R y devolver los resultados a SQL Server.

Instalación de R y preparación de su entorno

La instalación de R es un proceso seguro y sencillo. Puede descargar el archivo de instalación ejecutable de R desde el sitio web oficial de R. Una vez instalado, tendrá el motor de R en su computadora.

Para utilizar el procedimiento sp_getStatistics, también deberá ejecutar dos archivos SQL que crean la configuración necesaria de SQL Server, una muestra de datos y el propio procedimiento. Estos archivos se pueden encontrar en la sección de recursos a continuación.

Configuración de SQL Server

Antes de utilizar el procedimiento sp_getStatistics, debe asegurarse de tener los componentes necesarios en su máquina cliente. Estos incluyen una instancia de SQL Server, el programa R para computación estadística y permisos de lectura/escritura en la carpeta de trabajo para los archivos de R.

El procedimiento también requiere habilitar XP_CMDSHELL y Ole Automation Procedures en su instancia de SQL Server. Estas configuraciones se pueden habilitar utilizando el comando SP_CONFIGURE.

Preparación de datos

Antes de ejecutar el procedimiento sp_getStatistics, debe consultar sus datos en una nueva tabla o crear una vista contra una tabla de hechos existente. Por ejemplo, puede crear una nueva tabla utilizando la base de datos AdventureWorks2012 y ejecutar diferentes estadísticas para explorar los datos.

Una vez que los datos estén preparados, puede utilizar una simple instrucción SELECT para verificar que tiene los datos correctos en su tabla.

Parámetros del procedimiento

El procedimiento sp_getStatistics acepta varios parámetros de entrada y devuelve estadísticas sobre las columnas/variables deseadas. Los parámetros de entrada incluyen:

• @TargetTable: El nombre de la tabla que contiene los datos para el análisis.
• @Variables: La(s) columna(s) o variable(s) de la tabla que se analizarán.
• @Statistics: El ID de las estadísticas que se calcularán. Estas estadísticas se definen en la tabla dbo.tableStatistics.
• @ServerName: El nombre del servidor de SQL Server que se ejecuta en la máquina cliente.
• @DatabaseName: El nombre de la base de datos de SQL Server en el servidor.
• @WorkingDirectory: El nombre de la carpeta en la máquina cliente donde SQL Server almacenará los scripts de R y el programa R almacenará las exportaciones.
• @RPath: La ruta en la máquina cliente al archivo R.exe, que es el archivo ejecutable para iniciar R.

El procedimiento incluye comprobaciones de validación para los parámetros de entrada para asegurarse de que estén correctamente definidos y sean válidos.

Generación de script de R

El procedimiento sp_getStatistics genera un script de R que realiza los cálculos estadísticos deseados. El script de R incluye lógica para verificar la existencia de bibliotecas, abrir una conexión ODBC a SQL Server, ejecutar la consulta SQL para extraer datos al entorno de R y ejecutar las estadísticas especificadas en función de los parámetros de entrada.

El script de R se almacena en un archivo de R y se ejecuta utilizando el programa R. Los resultados se almacenan en un archivo de texto e imprimen nuevamente en SQL Server Management Studio para facilitar el acceso y el análisis.

Conclusión

El procedimiento sp_getStatistics proporciona una forma simple y efectiva de realizar análisis estadísticos en sus datos de SQL Server utilizando el lenguaje de programación R. Al combinar el poder de SQL Server y R, puede obtener una comprensión más profunda de sus datos y tomar decisiones más informadas. Ya sea que sea un estadístico o un analista de datos, este procedimiento puede ayudarlo a explorar y comprender sus datos sin abandonar la comodidad de SQL Server Management Studio.

Autor: Tomaz Kastrun

Twitter: @tomaz_tsql

Blog: http://tsqljokes.tumblr.com/