¿Estás interesado en usar funciones R en SQL Server? En este artículo, exploraremos cómo crear funciones R personalizadas en SQL Server y proporcionaremos ejemplos de código para ayudarte a comenzar.
Funciones R en SQL Server
Una función R típica tiene un nombre, parámetros de entrada y un cuerpo. Veamos un ejemplo de una función que convierte la temperatura entre Fahrenheit y Celsius:
fcTemperatureConvert <- function(ConvertTo, mytemperature) {
if(ConvertTo == "F")
Newtemperature <- (mytemperature - 32) * 5 / 9
else
Newtemperature <- mytemperature * 9 / 5 + 32
return(Newtemperature)
}
print("Convertir Fahrenheit a Celsius")
print(fcTemperatureConvert("F", 68))
print("Convertir Celsius a Fahrenheit")
print(fcTemperatureConvert("C", 20))
En SQL Server Management Studio, puedes ejecutar el script R utilizando el procedimiento almacenado sp_execute_external_script:
DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
fcTemperatureConvert <- function(ConvertTo, mytemperature) {
if(ConvertTo == "F")
Newtemperature <- (mytemperature - 32) * 5 / 9
else
Newtemperature <- mytemperature * 9 / 5 + 32
return(Newtemperature)
}
print("Convertir Fahrenheit a Celsius")
print(fcTemperatureConvert("F", 68))
print("Convertir Celsius a Fahrenheit")
print(fcTemperatureConvert("C", 20))
';
EXEC sp_execute_external_script @language = N'R', @script = @rscript;
El lenguaje R permite que las funciones tengan parámetros de entrada. Puedes pasar los parámetros de entrada por nombre completo, por posición o por nombre parcial. Veamos un ejemplo:
myADRS <- function(FamilyName, Address, zip, state) {
FullAddress <- c(FamilyName, Address, zip, state)
return(FullAddress)
}
print('Llamada a la función usando argumentos por posición')
print(myADRS('Test Person', '51 Test dr Atlanta', '30031', 'GA'))
print('Llamada a la función usando argumentos por nombre completo y posición')
print(myADRS(FamilyName = 'Test Person', zip = '30031', '51 Test dr Atlanta', 'GA'))
print('Llamada a la función usando argumentos por nombre parcial')
print(myADRS(sta = 'GA', Fam = 'Test Person', z = '30031', '51 Test dr Atlanta'))
En SQL Server Management Studio, puedes ejecutar el script R utilizando el procedimiento almacenado sp_execute_external_script:
DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
myADRS <- function(FamilyName, Address, zip, state) {
FullAddress <- c(FamilyName, Address, zip, state)
return(FullAddress)
}
print(''Llamada a la función usando argumentos por posición'')
print(myADRS(''Test Person'', ''51 Test dr Atlanta'', ''30031'', ''GA''))
print(''Llamada a la función usando argumentos por nombre completo y posición'')
print(myADRS(FamilyName = ''Test Person'', zip = ''30031'', ''51 Test dr Atlanta'', ''GA''))
print(''Llamada a la función usando argumentos por nombre parcial'')
print(myADRS(sta = ''GA'', Fam = ''Test Person'', z = ''30031'', ''51 Test dr Atlanta''))
';
EXEC sp_execute_external_script @language = N'R', @script = @rscript;
Ejemplo: Funciones R en SQL Server – Contar, Media, Mediana, Desviación Estándar y Resumir
En este ejemplo, utilizaremos una combinación de funciones R (Contar, Media, Mediana, Desviación Estándar y Resumir) para comparar las ventas mensuales anuales de 2012 y 2013 para el agente de ventas 275. Nuestros datos de entrada provienen de la base de datos AdventureWorks2014.
Aquí está la consulta T-SQL para resumir las ventas de 2012 y 2013 del vendedor 275:
SELECT YEAR(OrderDate) AS SaleYear, MONTH(OrderDate) AS SaleMonth, SUM(TotalDue) AS SaleAmount
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 275 AND YEAR(OrderDate) IN (2012, 2013)
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)
En el script R, dividimos los datos de entrada en dos conjuntos de datos, uno para las ventas de 2012 y otro para las ventas de 2013:
# Datos de consulta de entrada
SalesAnalysis <- InputDataSet
# Dividir las ventas por año
SalesSplit <- split(SalesAnalysis, SalesAnalysis$SaleYear)
SalesBefore <- SalesSplit$"2012"$SaleAmount
SalesAfter <- SalesSplit$"2013"$SaleAmount
Luego, agrupamos los datos y mostramos el recuento de registros, la media, la mediana y la desviación estándar:
# Agrupar los datos
dataAnalysis <- data.frame(
group = rep(c("SalesBefore", "SalesAfter"), each = 12),
weight = c(SalesBefore, SalesAfter)
)
# Imprimir nuestros resultados estadísticos
print(
group_by(dataAnalysis, group) %>
summarise(
count = n(),
mean = mean(weight, na.rm = TRUE),
median = median(weight, na.rm = TRUE),
sd = sd(weight, na.rm = TRUE)
)
)
En SQL Server Management Studio, puedes ejecutar el script R utilizando el procedimiento almacenado sp_execute_external_script:
DECLARE @rscript NVARCHAR(MAX);
SET @rscript = N'
library("dplyr")
# Datos de consulta de entrada
SalesAnalysis <- InputDataSet
# Dividir las ventas por año
SalesSplit <- split(SalesAnalysis, SalesAnalysis$SaleYear)
SalesBefore <- SalesSplit$"2012"$SaleAmount
SalesAfter <- SalesSplit$"2013"$SaleAmount
# Agrupar los datos
dataAnalysis <- data.frame(
group = rep(c("SalesBefore", "SalesAfter"), each = 12),
weight = c(SalesBefore, SalesAfter)
)
# Imprimir nuestros resultados estadísticos
print(
group_by(dataAnalysis, group) %>
summarise(
count = n(),
mean = mean(weight, na.rm = TRUE),
median = median(weight, na.rm = TRUE),
sd = sd(weight, na.rm = TRUE)
)
)
';
DECLARE @sqlscript NVARCHAR(MAX);
SET @sqlscript = N'
SELECT YEAR(OrderDate) AS SaleYear, MONTH(OrderDate) AS SaleMonth, SUM(TotalDue) AS SaleAmount
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 275 AND YEAR(OrderDate) IN (2012, 2013)
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate)
';
EXEC sp_execute_external_script @language = N'R', @script = @rscript, @input_data_1 = @sqlscript;
Conclusión
En este artículo, hemos aprendido cómo crear y usar funciones R simples en SQL Server. Al aprovechar el poder de R, puedes realizar cálculos y análisis complejos directamente dentro de tu entorno de SQL Server. Estén atentos para futuros artículos donde exploraremos temas más avanzados, como la creación de procedimientos almacenados R.