Repository navigation
Expand file tree
/
Copy pathtest_regression_models.sql
More file actions
142 lines (132 loc) · 6.89 KB
/
Copy pathtest_regression_models.sql
File metadata and controls
142 lines (132 loc) · 6.89 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
SET ANSI_NULLS ON
GO
/****** Create the Stored Procedure for PM Template ******/
DROP PROCEDURE IF EXISTS [test_regression_models]
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [test_regression_models] @modelrf varchar(20),
@modelbtree varchar(20),
@modelglm varchar(20),
@modelnn varchar(20),
@connectionString varchar(300)
AS
BEGIN
declare @inquery NVARCHAR(max) = N'SELECT * FROM test_Features_Normalized';
declare @model_rf varbinary(max) = (select model from [PM_Models] where model_name = @modelrf);
declare @model_btree varbinary(max) = (select model from [PM_Models] where model_name = @modelbtree);
declare @model_glm varbinary(max) = (select model from [PM_Models] where model_name = @modelglm);
declare @model_nn varbinary(max) = (select model from [PM_Models] where model_name = @modelnn);
declare @maxrul float = (SELECT MAX(RUL) FROM [train_Features_Normalized]);
EXEC sp_execute_external_script @language = N'R',
@script = N'
####################################################################################################
## Get test dataset from the input
####################################################################################################
prediction_df <- InputDataSet
####################################################################################################
## Regression model evaluation metrics
####################################################################################################
evaluate_model <- function(observed, predicted) {
mean_observed <- mean(observed)
se <- (observed - predicted)^2
ae <- abs(observed - predicted)
sem <- (observed - mean_observed)^2
aem <- abs(observed - mean_observed)
mae <- mean(ae)
rmse <- sqrt(mean(se))
rae <- sum(ae) / sum(aem)
rse <- sum(se) / sum(sem)
rsq <- 1 - rse
metrics <- c("Mean Absolute Error" = mae,
"Root Mean Squared Error" = rmse,
"Relative Absolute Error" = rae,
"Relative Squared Error" = rse,
"Coefficient of Determination" = rsq)
return(metrics)
}
####################################################################################################
## Decision forest prediction
####################################################################################################
forest_model <- unserialize(forest_model)
forest_prediction <- rxPredict(modelObject = forest_model,
data = prediction_df,
predVarNames = "Forest_Prediction",
overwrite = TRUE)
forest_metrics <- evaluate_model(observed = prediction_df$RUL,
predicted = forest_prediction$Forest_Prediction)
####################################################################################################
## Boosted tree prediction
####################################################################################################
boosted_model <- unserialize(boosted_model)
boosted_prediction <- rxPredict(modelObject = boosted_model,
data = prediction_df,
predVarNames = "Boosted_Prediction",
overwrite = TRUE)
boosted_metrics <- evaluate_model(observed = prediction_df$RUL,
predicted = boosted_prediction$Boosted_Prediction)
####################################################################################################
## Poisson regression prediction
####################################################################################################
poisson_model <- unserialize(poisson_model)
poisson_prediction <- rxPredict(modelObject = poisson_model,
data = prediction_df,
predVarNames = "Poisson_Prediction",
overwrite = TRUE)
poisson_metrics <- evaluate_model(observed = prediction_df$RUL,
predicted = poisson_prediction$Poisson_Prediction)
####################################################################################################
## Neural network regression prediction
####################################################################################################
library(nnet)
nnet_model <- unserialize(nnet_model)
nnet_prediction <- predict(object = nnet_model,
newdata = prediction_df)
nnet_prediction <- nnet_prediction * max_train_rul
nnet_prediction <- as.data.frame(nnet_prediction)
names(nnet_prediction) <- "Nnet_Prediction"
nnet_metrics <- evaluate_model(observed = prediction_df$RUL,
predicted = nnet_prediction$Nnet_Prediction)
####################################################################################################
## Write test predictions to SQL
####################################################################################################
predictions <- cbind(prediction_df$id, prediction_df$cycle_orig, forest_prediction,
boosted_prediction, poisson_prediction, nnet_prediction)
colnames(predictions) <- c("id", "cycle", "Forest_Prediction", "Boosted_Prediction",
"Poisson_Prediction", "Nnet_Prediction")
prediction_table <- RxSqlServerData(table = "Regression_prediction",
connectionString = connection_string)
rxDataStep(inData = predictions,
outFile = prediction_table,
overwrite = TRUE)
####################################################################################################
## Combine metrics and write to SQL
####################################################################################################
metrics_df <- rbind(forest_metrics, boosted_metrics, poisson_metrics, nnet_metrics)
metrics_df <- as.data.frame(metrics_df)
rownames(metrics_df) <- NULL
Algorithms <- c("Decision Forest",
"Boosted Decision Tree",
"Poisson Regression",
"Neural Network")
metrics_df <- cbind(Algorithms, metrics_df)
metrics_table <- RxSqlServerData(table = "Regression_metrics",
connectionString = connection_string)
rxDataStep(inData = metrics_df,
outFile = metrics_table,
overwrite = TRUE)'
, @input_data_1 = @inquery
, @params = N'@forest_model varbinary(max), @boosted_model varbinary(max), @poisson_model varbinary(max), @nnet_model varbinary(max), @max_train_rul float, @connection_string varchar(300)'
, @forest_model = @model_rf
, @boosted_model = @model_btree
, @poisson_model = @model_glm
, @nnet_model = @model_nn
, @max_train_rul = @maxrul
, @connection_string = @connectionString
END
GO