Home > Data Miner 3.2 > Oracle Data Mining Applicat... > Data Mining SQL Functions
The built-in SQL functions for Data Mining implement scoring operations for models that have already been created in the database.
The SQL functions include functions for predictions, clustering, and feature extraction.
For detailed information about these functions, see "SQL Scoring Functions" in the Data Mining API Reference at http://www.oracle.com/pls/db112/vbook_subject?subject=dma
.
Data Miner generates code for deployment that uses these functions.
The Deploy selection for an apply node generates code that uses the PREDICTION function. The PREDICTION function returns the best prediction for the model.
To see an example using prediction functions for deployment, follow these steps:
Run a workflow that includes an apply node. In this example, use the workflow in the Oracle By Example Using Oracle Data Miner for Oracle Database 11g Release 2. (See Oracle By Example for Oracle Data Miner 11g R2 for details.) The node Apply Model
is an apply node.
Right-click Apply Model
and select Deploy.
Save the deployment script to a file, for example, Apply_Model.sql
.
Open Apply_Model.sql
in SQL Developer. The following script is displayed:
/* SQL Deployed by Oracle SQL Developer 3.2.08.44 from Node "Apply Model", Workflow "Targeting_Best_Customers", Project "obe", Connection "dmConnection" on Jun 7, 2012 */ --ALTER SESSION FOR OPTIMIZER ALTER SESSION set "_optimizer_reuse_cost_annotations"=false; ALTER SESSION set NLS_NUMERIC_CHARACTERS=".,"; WITH /* Start of sql for node: INSUR_CUST_LTV_APPLY */ "N$10011" as (select /*+ inline */ "INSUR_CUST_LTV_SAMPLE"."MARITAL_STATUS", "INSUR_CUST_LTV_SAMPLE"."STATE", "INSUR_CUST_LTV_SAMPLE"."CREDIT_BALANCE", "INSUR_CUST_LTV_SAMPLE"."TIME_AS_CUSTOMER", "INSUR_CUST_LTV_SAMPLE"."CUSTOMER_ID", "INSUR_CUST_LTV_SAMPLE"."MORTGAGE_AMOUNT", "INSUR_CUST_LTV_SAMPLE"."BANK_FUNDS", "INSUR_CUST_LTV_SAMPLE"."N_OF_DEPENDENTS", "INSUR_CUST_LTV_SAMPLE"."HAS_CHILDREN", "INSUR_CUST_LTV_SAMPLE"."SALARY", "INSUR_CUST_LTV_SAMPLE"."SEX", "INSUR_CUST_LTV_SAMPLE"."PROFESSION", "INSUR_CUST_LTV_SAMPLE"."CREDIT_CARD_LIMITS", "INSUR_CUST_LTV_SAMPLE"."REGION", "INSUR_CUST_LTV_SAMPLE"."HOUSE_OWNERSHIP", "INSUR_CUST_LTV_SAMPLE"."N_TRANS_WEB_BANK", "INSUR_CUST_LTV_SAMPLE"."BUY_INSURANCE", "INSUR_CUST_LTV_SAMPLE"."MONTHLY_CHECKS_WRITTEN", "INSUR_CUST_LTV_SAMPLE"."N_TRANS_KIOSK", "INSUR_CUST_LTV_SAMPLE"."AGE", "INSUR_CUST_LTV_SAMPLE"."MONEY_MONTLY_OVERDRAWN", "INSUR_CUST_LTV_SAMPLE"."LTV", "INSUR_CUST_LTV_SAMPLE"."T_AMOUNT_AUTOM_PAYMENTS", "INSUR_CUST_LTV_SAMPLE"."N_TRANS_TELLER", "INSUR_CUST_LTV_SAMPLE"."CHECKING_AMOUNT", "INSUR_CUST_LTV_SAMPLE"."N_TRANS_ATM", "INSUR_CUST_LTV_SAMPLE"."LTV_BIN", "INSUR_CUST_LTV_SAMPLE"."LAST", "INSUR_CUST_LTV_SAMPLE"."N_MORTGAGES", "INSUR_CUST_LTV_SAMPLE"."CAR_OWNERSHIP", "INSUR_CUST_LTV_SAMPLE"."FIRST" from "MINER"."INSUR_CUST_LTV_SAMPLE" ) /* End of sql for node: INSUR_CUST_LTV_APPLY */ , /* Start of sql for node: Apply Model */ "N$10010" as (SELECT /*+ inline */ "CUSTOMER_ID" , PREDICTION("MINER"."CLAS_DT_1_3" COST MODEL USING *) "CLAS_DT_1_3_PRED" , PREDICTION_PROBABILITY("MINER"."CLAS_DT_1_3" USING *) "CLAS_DT_1_3_PROB" , PREDICTION_COST("MINER"."CLAS_DT_1_3" COST MODEL USING *) "CLAS_DT_1_3_PCST" FROM "N$10011" ) /* End of sql for node: Apply Model */ select * from "N$10010";
The PREDICTION
function returns the best prediction for the model CLAS_DT_1_3
. The PREDICTION_PROBABILITY
function returns the probability for a given prediction.