- Notifications
You must be signed in to change notification settings - Fork 849
/
Copy pathoml4r-vignette-08-extensibility-for-r-models.sql
114 lines (93 loc) · 3.09 KB
/
oml4r-vignette-08-extensibility-for-r-models.sql
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
--##################################################################
--##
--## Oracle Machine Learning for R Vignette
--##
--## Extensibility for R Models
--##
--## Copyright (c) 2020 Oracle Corporation
--##
--## The Universal Permissive License (UPL), Version 1.0
--##
--## https://oss.oracle.com/licenses/upl/
--##
--###################################################################
-- Register a new GLM algorithm and use it to create models
-- Cleanup any prior output tables/scripts/models
BEGIN EXECUTE IMMEDIATE 'DROP TABLE GLM_RDEMO_SETTINGS_CL';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGINDBMS_DATA_MINING.DROP_MODEL('GLM_RDEMO_CLASSIFICATION');
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN
DBMS_DATA_MINING.drop_algorithm(
ALGORITHM_NAME =>'t1',
CASCADE => TRUE);
END;
/
-- Register the Algorithm
BEGIN
DBMS_DATA_MINING.register_algorithm(
ALGORITHM_NAME =>'t1',
algorithm_metadata =>
'{"function_language":"R",
"mining_function" : { "mining_function_name" : "CLASSIFICATION",
"build_function" : {"function_body":
"function(dat, formula, keep.model) { set.seed(1234); mod <- glm(formula = formula(formula), data=dat, family=binomial(logit), model = as.logical(keep.model)); mod}"},
"score_function" : {"function_body":
"function(mod, dat) { res <- predict(mod, newdata = dat, type=''response''); res2=data.frame(1-res, res); names(res2) <- c(''0'', ''1''); res2}"}},
"algo_setting" : [{"name" : "ralg_parameter_keep.model", "data_type" : "integer","value" : "0", "optional" : "TRUE", "min_value" : {"min_value": "0", "inclusive": "TRUE"}, "max_value" : {"max_value": "1", "inclusive": "TRUE"}}]
}',
algorithm_description =>'t1');
END;
/
-- Model Settings
CREATETABLEGLM_RDEMO_SETTINGS_CL (
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000));
BEGIN
INSERT INTO GLM_RDEMO_SETTINGS_CL VALUES
('ALGO_EXTENSIBLE_LANG', 'R');
END;
/
BEGIN
INSERT INTO GLM_RDEMO_SETTINGS_CL
VALUES(dbms_data_mining.algo_name, 't1');
END;
/
BEGIN
INSERT INTO GLM_RDEMO_SETTINGS_CL
VALUES(dbms_data_mining.r_formula, 'AGE + EDUCATION + HOUSEHOLD_SIZE + OCCUPATION');
END;
/
BEGIN
INSERT INTO GLM_RDEMO_SETTINGS_CL
VALUES('ralg_parameter_keep.model', 1);
END;
/
--#-- Build the model using the registered build function
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
model_name =>'GLM_RDEMO_CLASSIFICATION',
mining_function =>dbms_data_mining.classification,
data_table_name =>'mining_data_build_v',
case_id_column_name =>'CUST_ID',
target_column_name =>'AFFINITY_CARD',
settings_table_name =>'GLM_RDEMO_SETTINGS_CL');
END;
/
--#-- Score data using the model's registered score function
SELECT CUST_ID, AFFINITY_CARD as AFFINITY_CARD_act,
PREDICTION(GLM_RDEMO_CLASSIFICATION USING *) AFFINITY_CARD_pred,
round(PREDICTION_PROBABILITY(GLM_RDEMO_CLASSIFICATION USING *), 3)
as AFFINITY_CARD_prob
FROM mining_data_apply_v where CUST_ID <=100010
order by CUST_ID;
--#-- Clean up
BEGIN
DBMS_DATA_MINING.drop_algorithm(
ALGORITHM_NAME =>'t1',
CASCADE => TRUE);
END;
/
--#-- End of Script
