Skip to content

Instantly share code, notes, and snippets.

@cseidman

cseidman/modelbuildsp.SQL Secret

Created Jan 24, 2018
Embed
What would you like to do?
modelbuildsp
ALTER PROCEDURE [dbo].[buildCustomerTree]
@parallel_outer bit = 0,
@input_data_1_outer nvarchar(max) = N'select
[Age]
,[MaritalStatus]
,[Gender]
,[YearlyIncome]
,[TotalChildren]
,[NumberChildrenAtHome]
,[Education]
,[Occupation]
,[IsHomeOwner]
,[NumberCarsOwned]
,[YearsSinceFirstPurchase]
,[CommuteDistance]
,[CountryRegionCode]
,[StateProvinceCode]
,[IsCardUser]
from dbo.vwDimCustomer'
AS
BEGIN TRY
exec sp_execute_external_script
@language = N'R',
@script = N'
buildCustomerTree <- function (trainingDs)
{
trainingDs$TotalChildren <- as.factor(trainingDs$TotalChildren)
trainingDs$NumberChildrenAtHome <- as.factor(trainingDs$NumberChildrenAtHome)
trainingDs$NumberCarsOwned <- as.factor(trainingDs$NumberCarsOwned)
trainingDs$YearsSinceFirstPurchase <- as.factor(trainingDs$YearsSinceFirstPurchase)
trainingDs$AgeRanges <- cut(trainingDs$Age, breaks = c(0, 30, 50, 70, 90), labels = c("11-30", "31-50", "51-70","71+"))
trainingDs$IncomeCategories <- cut(x = trainingDs$YearlyIncome,
breaks = c(0, 20000, 50000, 70000, 100000, 250000), labels = c("Low","Lower", "Middle", "Upper", "Wealthy"))
tr <- rxDTree(IsCardUser ~ Gender + Occupation + MaritalStatus +
IsHomeOwner + TotalChildren + NumberCarsOwned + AgeRanges +
CommuteDistance + IncomeCategories + CountryRegionCode +
Education, data = trainingDs, method = "class", overwrite = TRUE,
reportProgress = 0, cp = 0.03)
data.frame(trained_model = as.raw(serialize(tr, connection = NULL)))
}
result <- buildCustomerTree(trainingDs = trainingDs)
if (is.data.frame(result)) {
OutputDataSet <- result
} else if (is.list(result) && length(result) == 1
&& is.data.frame(result[[1]])) {
OutputDataSet <- result[[1]]
} else if (!is.null(result)) {
stop(paste0("the R function must return either NULL,",
" a data frame, or a list that ",
"constains a single data frame"))
}
',
@parallel = @parallel_outer,
@input_data_1 = @input_data_1_outer,
@input_data_1_name = N'trainingDs'
END TRY
BEGIN CATCH
THROW;
END CATCH;
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment