2.4 Oracle Machine Learning for SQL Scoring Functions
Use OML4SQL functions score data. Functions can apply a machine learning model schema object to data or dynamically mine it with an analytic clause. SQL functions exist for all OML4SQL scoring algorithms.
All OML4SQL functions, as listed in the following table can operate on an R machine learning model with the corresponding OML4SQL function. However, the functions are not limited to the ones listed here.
Table 2-4 OML4SQL Functions
Function | Description |
---|---|
Returns the ID of the predicted cluster |
|
Returns detailed information about the predicted cluster |
|
Returns the distance from the centroid of the predicted cluster |
|
Returns the probability of a case belonging to a given cluster |
|
Returns a list of all possible clusters to which a given case belongs along with the associated probability of inclusion |
|
FEATURE_COMPARE |
Compares two similar and dissimilar set of texts from two different documents or keyword phrases or a combination of both |
Returns the ID of the feature with the highest coefficient value |
|
Returns detailed information about the predicted feature |
|
Returns a list of objects containing all possible features along with the associated coefficients |
|
Returns the value of the predicted feature |
|
ORA_DM_PARTITION_NAME |
Returns the partition names for a partitioned model |
Returns the best prediction for the target |
|
(GLM only) Returns the upper and lower bounds of the interval wherein the predicted values (linear regression) or probabilities (logistic regression) lie. |
|
Returns a measure of the cost of incorrect predictions |
|
Returns detailed information about the prediction |
|
Returns the probability of the prediction |
|
Returns the results of a classification model, including the predictions and associated probabilities for each case |
|
Generates a single vector embedding for different data types |
The following example shows a query that returns the
results of the CLUSTER_ID
function. The query applies the model
em_sh_clus_sample, which finds groups of customers that share certain characteristics. The
query returns the identifiers of the clusters and the number of customers in each cluster.
The em_sh_clus_sample model is created by the
oml4sql-clustering-expectation-maximization.sql
example.
Example 2-9 CLUSTER_ID Function
-- -List the clusters into which the customers in this -- -data set have been grouped. -- SELECT CLUSTER_ID(em_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt FROM mining_data_apply_v GROUP BY CLUSTER_ID(em_sh_clus_sample USING *) ORDER BY cnt DESC; -- List the clusters into which the customers in this -- data set have been grouped. -- SELECT CLUSTER_ID(em_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt FROM mining_data_apply_v GROUP BY CLUSTER_ID(em_sh_clus_sample USING *) ORDER BY cnt DESC;
The output is as follows:
CLUS CNT
---------- ----------
9 311
3 294
7 215
12 201
17 123
16 114
14 86
19 64
15 56
18 36
Parent topic: About the Oracle Machine Learning for SQL API