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:

  1. Run a workflow that includes an apply node. In this example, use the workflow in the Data Miner Cue Card set Getting Started with Data Miner. The node PredictHigh is an apply node.

  2. Right-click PredictHigh and select Deploy.

  3. Save the deployment script to a file, for example, identify-PredictHigh.sql.

  4. Open identify-PredictHigh.sql in SQL Developer. The following script is displayed:

    /* SQL Deployed by Oracle SQL Developer 3.1.06.21 from Node "PredictHigh", Workflow "identify", Project "cuecards", Connection "dmConnection" on Oct 25, 2011 */
    --ALTER SESSION FOR OPTIMIZER
    ALTER SESSION set "_optimizer_reuse_cost_annotations"=false;
    ALTER SESSION set NLS_NUMERIC_CHARACTERS=".,";
    WITH 
    /* Start of sql for node: MINING_DATA_APPLY_V */
    "N$10009" as (select /*+ inline */ "MINING_DATA_APPLY_V"."AGE", 
    "MINING_DATA_APPLY_V"."OCCUPATION", 
    "MINING_DATA_APPLY_V"."CUST_INCOME_LEVEL", 
    "MINING_DATA_APPLY_V"."FLAT_PANEL_MONITOR", 
    "MINING_DATA_APPLY_V"."YRS_RESIDENCE", 
    "MINING_DATA_APPLY_V"."HOME_THEATER_PACKAGE", 
    "MINING_DATA_APPLY_V"."HOUSEHOLD_SIZE", 
    "MINING_DATA_APPLY_V"."BULK_PACK_DISKETTES", 
    "MINING_DATA_APPLY_V"."Y_BOX_GAMES", 
    "MINING_DATA_APPLY_V"."AFFINITY_CARD", 
    "MINING_DATA_APPLY_V"."CUST_ID", 
    "MINING_DATA_APPLY_V"."PRINTER_SUPPLIES", 
    "MINING_DATA_APPLY_V"."CUST_MARITAL_STATUS", 
    "MINING_DATA_APPLY_V"."EDUCATION", 
    "MINING_DATA_APPLY_V"."CUST_GENDER", 
    "MINING_DATA_APPLY_V"."COUNTRY_NAME", 
    "MINING_DATA_APPLY_V"."OS_DOC_SET_KANJI", 
    "MINING_DATA_APPLY_V"."BOOKKEEPING_APPLICATION" 
    from "MINER"."MINING_DATA_APPLY_V"  )
    /* End of sql for node: MINING_DATA_APPLY_V */
    ,
    /* Start of sql for node: PredictHigh */
    "N$10008" as (SELECT /*+ inline */
    "CUST_ID"
    , PREDICTION("MINER"."CLAS_NB_1_19" USING *) "CLAS_NB_1_19_PRED"
    , PREDICTION_PROBABILITY("MINER"."CLAS_NB_1_19" USING *) "CLAS_NB_1_19_PROB"
     FROM "N$10009" )
    /* End of sql for node: PredictHigh */
    select * from "N$10008";
    

    The PREDICTION function returns the best prediction for the model CLAS_NB_1_19. The PREDICTION_PROBABILITY function returns the probability for a given prediction.