5.2 Use the Oracle Machine Learning for SQL Functions
Some of the benefits of using SQL functions for Oracle Machine Learning for SQL are listed.
The OML4SQL functions provide the following benefits:
-
Models can be easily deployed within the context of existing SQL applications.
-
Scoring operations take advantage of existing query execution functionality. This provides performance benefits.
-
Scoring results are pipelined, enabling the rows to be processed without requiring materialization.
The machine learning functions produce a score for each row in the selection. The functions can apply a machine learning model schema object to compute the score, or they can score dynamically without a pre-defined model, as described in "Dynamic Scoring".
5.2.1 Choose the Predictors
You can select different attributes as predictors in a PREDICTION
function through a USING
clause.
The OML4SQL functions support a USING
clause that specifies which attributes to use for scoring. You can specify some or all of the attributes in the selection and you can specify expressions. The following examples all use the PREDICTION
function to find the customers who are likely to use an affinity card, but each example uses a different set of predictors.
When predictor values are not in the training data, the models score categorical values that were not in the training data without error. A score is produced using the remaining predictors. This enables batch scoring that does not fail because of a single record with an invalid value. Also, in some algorithms, like k-Means or Gaussian SVM, a new value can change the prediction in a meaningful way, such as resulting in larger distances with the unknown value. Furthermore, additional columns that were not present for building may be present in the table or view provided for scoring, and only the columns matching the model signature are used. Also, scoring may be performed with fewer predictors than are listed in the model signature.
In the case of partitioned models, a NULL
score is produced if the
partition value is invalid. If the partition column value is omitted, an error message is
returned.
The query in Example 5-1 uses all the predictors.
The query in Example 5-2 uses only gender, marital status, occupation, and income as predictors.
The query in Example 5-3 uses three attributes and an expression as predictors. The prediction is based on gender, marital status, occupation, and the assumption that all customers are in the highest income bracket.
Example 5-1 Using All Predictors
The dt_sh_clas_sample model is created by the oml4sql-classification-decision-tree.sql
example.
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age FROM mining_data_apply_v WHERE PREDICTION(dt_sh_clas_sample USING *) = 1 GROUP BY cust_gender ORDER BY cust_gender; C CNT AVG_AGE - ---------- ---------- F 25 38 M 213 43
Example 5-2 Using Some Predictors
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age FROM mining_data_apply_v WHERE PREDICTION(dt_sh_clas_sample USING cust_gender,cust_marital_status, occupation, cust_income_level) = 1 GROUP BY cust_gender ORDER BY cust_gender; C CNT AVG_AGE - ---------- ---------- F 30 38 M 186 43
Example 5-3 Using Some Predictors and an Expression
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age FROM mining_data_apply_v WHERE PREDICTION(dt_sh_clas_sample USING cust_gender, cust_marital_status, occupation, 'L: 300,000 and above' AS cust_income_level) = 1 GROUP BY cust_gender ORDER BY cust_gender; C CNT AVG_AGE - ---------- ---------- F 30 38 M 186 43
5.2.2 Single-Record Scoring
You can score a single record which produces 0 and 1 to predict customers who are unlikely or likely to use an affinity card.
The Oracle Machine Learning for SQL functions can produce a score for a single record, as shown in Example 5-4 and Example 5-5.
Example 5-4 returns a prediction for customer 102001 by applying the classification model NB_SH_Clas_sample. The resulting score is 0, meaning that this customer is unlikely to use an affinity card. The NB_SH_Clas_Sample model is created by the oml4sql-classification-naive-bayes.sql
example.
Example 5-5 returns a prediction for 'Affinity card is great' as the comments attribute by applying the text machine learning model T_SVM_Clas_sample. The resulting score is 1, meaning that this customer is likely to use an affinity card. The T_SVM_Clas_sample model is created by the oml4sql-classification-text-analysis-svm.sql
example.
Example 5-4 Scoring a Single Customer or a Single Text Expression
SELECT PREDICTION (NB_SH_Clas_Sample USING *) FROM sh.customers where cust_id = 102001;
The output is as follows:
PREDICTION(NB_SH_CLAS_SAMPLEUSING*)
-----------------------------------
0
Example 5-5 Scoring a Single Text Expression
SELECT PREDICTION(T_SVM_Clas_sample USING 'Affinity card is great' AS comments) FROM DUAL;
The output is as follows:
PREDICTION(T_SVM_CLAS_SAMPLEUSING'AFFINITYCARDISGREAT'ASCOMMENTS)
-----------------------------------------------------------------
1