This is the second of a two part blog posting on using an Oracle Data Mining model to apply it to or score new data. The first part looked at how you can score data the DBMS_DATA_MINING.APPLY procedure for scoring data batch type process.

This second part looks at how you can apply or score the new data, using our ODM model, in a real-time mode, scoring a single record at a time.

__PREDICTION Function__

The PREDICTION SQL function can be used in many different ways. The following examples illustrate the main ways of using it. Again we will be using the same data set with data in our (NEW_DATA_TO_SCORE) table.

The syntax of the function is

PREDICTION (

model_name,USINGattribute_list);

__Example 1 – Real-time Prediction Calculation__

In this example we will select a record and calculate its predicted value. The function will return the predicted value with the highest probability

SELECT cust_id, prediction(clas_decision_tree using *)

FROM NEW_DATA_TO_SCORE

WHERE cust_id = 103001;

CUST_ID PREDICTION(CLAS_DECISION_TREEUSING*)

---------- ------------------------------------

103001 0

So a predicted class value is 0 (zero) and this has a higher probability than a class value of 1.

We can compare and check this results with the result that was produced using the DBMS_DATA_MINING.APPLY function (see previous blog post).

SQL> select * from new_data_scored

2 where cust_id = 103001;

CUST_ID PREDICTION PROBABILITY

---------- ---------- -----------

103001 0 1

103001 1 0

Here we can see that the class value of 0 has a probability of 1 (100%) and the class value of 1 has a probability of 0 (0%).

__Example 2 – Selecting top 10 Customers with Class value of 1__

For this we are selecting from our NEW_DATA_TO_SCORE table. We want to find the records that have a class value of 1 and has the highest probability. We only want to return the first 10 of these

SELECT cust_id

FROM NEW_DATA_TO_SCORE

WHERE PREDICTION(clas_decision_tree using *) = 1

AND rownum <=10;

CUST_ID

----------

103005

103007

103010

103014

103016

103018

103020

103029

103031

103036

__Example 3 – Selecting records based on Prediction value and Probability__

For this example we want to find our from what Countries do the customer come from where the Prediction is 0 (wont take up offer) and the Probability of this occurring being 1 (100%). This example introduces the PREDICTION_PROBABILITY function. This function allows use to use the probability strength of the prediction.

select country_name, count(*)

from new_data_to_score

where prediction(clas_decision_tree using *) = 0

and prediction_probability (clas_decision_tree using *) = 1

group by country_name

order by count(*) asc;

COUNTRY_NAME COUNT(*)

---------------------------------------- ----------

Brazil 1

China 1

Saudi Arabia 1

Australia 1

Turkey 1

New Zealand 1

Italy 5

Argentina 12

United States of America 293

The examples that I have give above are only the basic examples of using the PREDICTION function. There are a number of other uses that include using the PREDICTION_COST, PREDICTION_SET, PREDICTION_DETAILS. Examples of these will be covered in a later blog post