Rem
Rem $Header: nb_sh.sql 17-dec-2003.18:55:47 ramkrish Exp $
Rem
Rem nb_sh.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      nb_sh.sql - Sample program for DBMS_DATA_MINING package.
Rem
Rem    DESCRIPTION
Rem      This script demonstrates the use of DBMS_DATA_MINING
Rem      for Classification function using the NB Algorithm
Rem      against the SH (Sales History) schema in the RDBMS. 
Rem
Rem    NOTES
Rem     
Rem
Rem    MODIFIED   (MM/DD/YY) 
Rem    ramkrish    10/02/03 - Creation
  
SET serveroutput ON
SET trimspool ON  
SET pages 10000

-----------------------------------------------------------------------
--                            BUILD THE MODEL
-----------------------------------------------------------------------

---------------------------------------------------------
-- CLEANUP BUILD DATA PREPARATION OBJECTS (IF APPLICABLE)
--
BEGIN
  -- Drop the Data Transformation Tables
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_num';
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_cat';

  -- Drop the Data Transformation Views for Model BUILD
  EXECUTE IMMEDIATE 'DROP VIEW nb_sh_sample_build_prepared';
  EXECUTE IMMEDIATE 'DROP VIEW nb_sh_sample_build_cat';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

--------------------------------
-- PREPARE BUILD (TRAINING) DATA
--
-- This step is required only if the data is not prepared/transformed
-- to enable creation of an efficient model using the NB algorithm.
-- In this case, the data for attributes 'occupation' and 'age'
-- requires preparation.
--
BEGIN
  -- Bin categorical attributes: occupation
  DBMS_DATA_MINING_TRANSFORM.CREATE_BIN_CAT (
    bin_table_name => 'nb_sh_sample_cat');        

  DBMS_DATA_MINING_TRANSFORM.INSERT_BIN_CAT_FREQ (
    bin_table_name  => 'nb_sh_sample_cat',
    data_table_name => 'mining_data_build_v',
    bin_num         => 7,
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (
                       'cust_gender',
                       'cust_marital_status',
                       'country_name',
                       'cust_income_level',
                       'education',
                       'household_size')
  );

  -- Bin numerical attributes: age
  DBMS_DATA_MINING_TRANSFORM.CREATE_BIN_NUM (
    bin_table_name => 'nb_sh_sample_num');
                         
  DBMS_DATA_MINING_TRANSFORM.INSERT_BIN_NUM_EQWIDTH (
    bin_table_name  => 'nb_sh_sample_num',
    data_table_name => 'mining_data_build_v',
    bin_num         => 5,
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (
                       'cust_id',
                       'yrs_residence',
                       'affinity_card',
                       'bulk_pack_diskettes',
                       'flat_panel_monitor',
                       'home_theater_package',
                       'bookkeeping_application',
                       'printer_supplies',
                       'y_box_games',
                       'os_doc_set_kanji')
  );

  -- Create the transformed view
  -- Execute the first transformation (categorical binning)
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_CAT (
    bin_table_name  => 'nb_sh_sample_cat',
    data_table_name => 'mining_data_build_v',
    xform_view_name => 'nb_sh_sample_build_cat');    

  -- Provide the result (nb_sh_sample_build_cat)
  -- to the next transformation (numerical binning)
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_NUM (
    bin_table_name  => 'nb_sh_sample_num',
    data_table_name => 'nb_sh_sample_build_cat',
    xform_view_name => 'nb_sh_sample_build_prepared');

  DBMS_OUTPUT.PUT_LINE('BUILD DATA PREPARATION OBJECTS:');
  DBMS_OUTPUT.PUT_LINE('1. Categorical Bin Table: nb_sh_sample_cat');
  DBMS_OUTPUT.PUT_LINE('2. Numerical Bin Table: nb_sh_sample_num');
  DBMS_OUTPUT.PUT_LINE(
    '3. Input (view) to CREATE_MODEL: nb_sh_sample_build_prepared');
END;
/

--------------------------------------
-- CLEANUP OLD SETTINGS TABLE (IF ANY)
--
BEGIN
  -- Drop the Settings Table
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_settings';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
-- DROP OLD PRIORS TABLE (IF ANY)
--
BEGIN
  -- Drop any existing Priors Table
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_priors';  
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
 
--------------------------
-- CREATE A SETTINGS TABLE
--
-- NB is the default classifier. But by default, CREATE_MODEL does not
-- attach any weights to the target classes through a priors table.
-- Override the default by providing a priors table as a setting for
-- model creation.
-- 
CREATE TABLE nb_sh_sample_settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));
 
-- CREATE AND POPULATE A PRIORS TABLE
--
-- A priors table is used to influence the weighting of target classes
-- during model creation. See Concepts Guide for more details.
--
CREATE TABLE nb_sh_sample_priors (
  target_value      NUMBER,
  prior_probability NUMBER);
INSERT INTO nb_sh_sample_priors VALUES (0,0.65);
INSERT INTO nb_sh_sample_priors VALUES (1,0.35);
COMMIT;

BEGIN       
  -- Populate settings table
  INSERT INTO nb_sh_sample_settings VALUES
    (dbms_data_mining.clas_priors_table_name, 'nb_sh_sample_priors');
  COMMIT;

  -- Examples of other possible overrides are:
  --(dbms_data_mining.nabs_pairwise_threshold,'.01')
  --(dbms_data_mining.nabs_singleton_threshold,'.01')
  dbms_output.put_line('Populated settings');
END;
/

--------------------------------------------
-- CLEANUP OLD MODEL WITH SAME NAME (IF ANY)
--
BEGIN
  DBMS_DATA_MINING.DROP_MODEL('NB_SH_Clas_sample');
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

---------------------
-- CREATE A NEW MODEL
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('Begin CREATE_MODEL - NB - Classification');
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'NB_SH_Clas_sample',
    mining_function     => dbms_data_mining.classification,
    data_table_name     => 'nb_sh_sample_build_prepared',
    case_id_column_name => 'cust_id',
    target_column_name  => 'affinity_card',
    settings_table_name => 'nb_sh_sample_settings');
  DBMS_OUTPUT.PUT_LINE('End   CREATE_MODEL - NB - Classification');
END;
/

-------------------------
-- DISPLAY MODEL SETTINGS
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display MODEL SETTINGS'); END;
/
column setting_name format a30
column setting_value format a30
SELECT setting_name, setting_value
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_SETTINGS('NB_SH_Clas_sample'))
ORDER BY setting_name;

--------------------------
-- DISPLAY MODEL SIGNATURE
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display MODEL SIGNATURE'); END;
/
column attribute_type format a20
SELECT attribute_name, attribute_type
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_SIGNATURE('NB_SH_Clas_sample'))
ORDER BY attribute_name;

------------------------
-- DISPLAY MODEL DETAILS
--
-- If the build data is prepared (as in this example), then the model
-- contains values of prepared data. The query shown below
-- reverse-transforms the model contents to be as close to the original
-- values as possible, based on the build data preparation objects.
-- Reporting the exact original value is impossible since the input
-- build data has already undergone some transformations (binning/
-- normalization etc.)
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display Remapped MODEL DETAILS'); END;
/
SELECT *
  FROM (SELECT T.target_attribute_name,
               T.target_attribute_num_value target_nval,
               T.target_attribute_str_value target_sval,
               T.prior_probability          prior_prob,
               C.attribute_name             predictor_attr,
               C.attribute_num_value        predictor_nval,
               C.attribute_str_value        predictor_sval,
               C.conditional_probability
          FROM TABLE(
               DBMS_DATA_MINING.GET_MODEL_DETAILS_NB('NB_SH_Clas_sample')) T,
               TABLE(T.conditionals) C
        ORDER BY 1,2,3,4,5,6)
 WHERE ROWNUM < 11;

-----------------------------------------------------------------------
--                               TEST THE MODEL
-----------------------------------------------------------------------

--------------------------------------------------------
-- CLEANUP TEST DATA PREPARATION OBJECTS (IF APPLICABLE)
--
BEGIN
  -- Drop the Data Transformation Views for Model TEST
  EXECUTE IMMEDIATE 'DROP VIEW nb_sh_sample_test_targets';
  EXECUTE IMMEDIATE 'DROP VIEW nb_sh_sample_test_prepared';
  EXECUTE IMMEDIATE 'DROP VIEW nb_sh_sample_test_cat';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

--------------------
-- PREPARE TEST DATA
--
-- If the data for model creation has been prepared, then the data used
-- for testing the model must be prepared to the same scale in order to
-- obtain meaningful results. You accomplish this by providing the transform
-- views generated during the training data preparation, along with 
-- mining_test_v, as input to the DBMS_DATA_MINING_TRANSFORM interfaces.
--
-- If this model is tested in a different schema or instance, the 
-- data preparation objects generated in the CREATE step must also
-- be made available in the target schema/instance. So you must export
-- those objects (i.e. the data preparation tables and views) along with 
-- the model to the target user schema.
--
BEGIN
  -- Execute the first transformation effected on training data
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_CAT (
    bin_table_name  => 'nb_sh_sample_cat',
    data_table_name => 'mining_data_test_v',
    xform_view_name => 'nb_sh_sample_test_cat');

  -- Provide the result to the next transformation effected on training data
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_NUM (
    bin_table_name  => 'nb_sh_sample_num',
    data_table_name => 'nb_sh_sample_test_cat',
    xform_view_name => 'nb_sh_sample_test_prepared');
END;
/

---------------------------------------
-- CLEANUP TEST RESULT OBJECTS (IF ANY)
--
BEGIN
  -- Drop the intermediate and result tables for Model TEST
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_test_apply';
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_confusion_matrix';
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_lift';
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_roc';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

---------------------------------
-- SPECIFY COST MATRIX (OPTIONAL)
--
-- (Skip cost matrix input - see svmc_sh.sql for example)
--
-- You can evaluate the cost of predictions from classification
-- models in an iterative manner during the model testing phase.
-- The data input to each COMPUTE procedure in the package is the
-- result generated from applying the model on test data. In addition,
-- if you also provide a cost matrix as an input, the COMPUTE
-- procedure generates test results taking the cost matrix into
-- account.
--
-- Once you arrive at the optimal cost matrix based on various
-- COMPUTE results, provide this cost matrix table name as input
-- to the RANK_APPLY procedure to get your costed scoring results.
--

-- CLEANUP OLD COST MATRIX TABLE (IF ANY)
--
-- BEGIN
  -- Drop the Cost Matrix Table
  -- EXECUTE IMMEDIATE 'DROP TABLE svmc_cost';
-- EXCEPTION WHEN OTHERS THEN
  -- NULL;
-- END;
-- /

-- CREATE A COST MATRIX TABLE
--
-- CREATE TABLE svmc_sh_cost (
--   actual_target_value    NUMBER,
--   predicted_target_value NUMBER,
--   cost                   NUMBER);

-- POPULATE THE COST MATRIX
--
-- INSERT INTO svmc_sh_cost VALUES (0,0,0);
-- INSERT INTO svmc_sh_cost VALUES (0,1,0.65);
-- INSERT INTO svmc_sh_cost VALUES (1,0,0.35);
-- INSERT INTO svmc_sh_cost VALUES (1,1,0);
-- COMMIT;

------------------------------------
-- COMPUTE METRICS TO TEST THE MODEL
--
-- The COMPUTE interfaces that provide the test results require two
-- data inputs:
-- 1. A table or view of targets - i.e. one that provides only the
--    case identifier and target columns of your test data.
-- 2. The table with the results of an APPLY operation on test data.
--

-- CREATE TEST TARGETS VIEW
--
CREATE VIEW nb_sh_sample_test_targets AS
SELECT cust_id, affinity_card
  FROM nb_sh_sample_test_prepared;

-- APPLY MODEL ON TEST DATA
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('Apply on test data');
  DBMS_DATA_MINING.APPLY(
    model_name          => 'NB_SH_Clas_sample',
    data_table_name     => 'nb_sh_sample_test_prepared',
    case_id_column_name => 'cust_id',
    result_table_name   => 'nb_sh_sample_test_apply');
  DBMS_OUTPUT.PUT_LINE('Completed apply on test data');
END;
/

-- COMPUTE TEST METRICS (WITH COST MATRIX INPUT)
--
DECLARE
  v_accuracy         NUMBER;
  v_area_under_curve NUMBER;
 BEGIN
   DBMS_OUTPUT.PUT_LINE('Execute COMPUTE_CONFUSION_MATRIX');
   DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
     accuracy                    => v_accuracy,
     apply_result_table_name     => 'nb_sh_sample_test_apply',
     target_table_name           => 'nb_sh_sample_test_targets',
     case_id_column_name         => 'cust_id',
     target_column_name          => 'affinity_card',
     confusion_matrix_table_name => 'nb_sh_sample_confusion_matrix',
     score_column_name           => 'PREDICTION',   -- default
     score_criterion_column_name => 'PROBABILITY',  -- default
     cost_matrix_table_name      => NULL);
   DBMS_OUTPUT.PUT_LINE(
     'Confusion matrix in table: nb_sh_sample_confusion_matrix');
   DBMS_OUTPUT.PUT_LINE('Model Accuracy: ' || v_accuracy);
 
   DBMS_OUTPUT.PUT_LINE('Execute COMPUTE_LIFT');     
   DBMS_DATA_MINING.COMPUTE_LIFT (
     apply_result_table_name => 'nb_sh_sample_test_apply',
     target_table_name       => 'nb_sh_sample_test_targets',
     case_id_column_name     => 'cust_id',
     target_column_name      => 'affinity_card',
     lift_table_name         => 'nb_sh_sample_lift',
     positive_target_value   => '1',
     num_quantiles           => '10',
     cost_matrix_table_name  => NULL);
   DBMS_OUTPUT.PUT_LINE('Lift results in table: nb_sh_sample_lift');

   DBMS_OUTPUT.PUT_LINE('Execute COMPUTE_ROC');
   DBMS_DATA_MINING.COMPUTE_ROC (
     roc_area_under_curve        => v_area_under_curve,
     apply_result_table_name     => 'nb_sh_sample_test_apply',
     target_table_name           => 'nb_sh_sample_test_targets',
     case_id_column_name         => 'cust_id',
     target_column_name          => 'affinity_card',
     roc_table_name              => 'nb_sh_sample_roc',
     positive_target_value       => '1',
     score_column_name           => 'PREDICTION',
     score_criterion_column_name => 'PROBABILITY');
   DBMS_OUTPUT.PUT_LINE('ROC in table: nb_sh_sample_roc');
   DBMS_OUTPUT.PUT_LINE('Area under the curve: ' || v_area_under_curve );
 END;
 /

-- DISPLAY CONFUSION MATRIX
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display CONFUSION MATRIX'); END;
/
SELECT actual_target_value, predicted_target_value, value
  FROM nb_sh_sample_confusion_matrix
ORDER BY actual_target_value,predicted_target_value;

-- DISPLAY LIFT RESULTS
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display LIFT RESULTS'); END;
/
SELECT quantile_number,
       quantile_total_count,
       percentage_records_cumulative,
       lift_cumulative,
       target_density_cumulative,
       targets_cumulative,
       non_targets_cumulative,
       lift_quantile,
       target_density
  FROM nb_sh_sample_lift
ORDER BY quantile_number;

-- DISPLAY ROC
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display RECEIVER OPERATING CHARACTERISTIC'); END;
/
SELECT probability,
       true_positives,
       false_negatives,
       false_positives,
       true_negatives,
       true_positive_fraction,
       false_positive_fraction
  FROM nb_sh_sample_roc
 WHERE ROWNUM < 11
ORDER BY true_negatives;

-----------------------------------------------------------------------
--                               APPLY THE MODEL
-----------------------------------------------------------------------

-----------------------------------------------------------
-- CLEANUP SCORING DATA PREPARATION OBJECTS (IF APPLICABLE)
--
BEGIN
  -- Drop the Data Transformation Views for Model APPLY
  EXECUTE IMMEDIATE 'DROP VIEW nb_sh_sample_apply_prepared';
  EXECUTE IMMEDIATE 'DROP VIEW nb_sh_sample_apply_cat';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

-----------------------
-- PREPARE SCORING DATA
--
-- If the data for model creation has been prepared, then the data
-- upon which the model is to be applied for scoring must be prepared
-- to the same scale in order to obtain meaningful results. You 
-- accomplish this by providing the transform views generated during
-- the training data preparation, along with mining_test_v, as input
-- to the DBMS_DATA_MINING_TRANSFORM interfaces.
--
-- Note that if this model is applied on scoring data located in a different
-- schema or instance, the data preparation tables generated in the
-- CREATE step must also be available in the target schema/instance for
-- the data preparation. So you must export the transformation tables and
-- views along with the model to the target user schema.
--
BEGIN
  -- Execute the first transformation effected on training data
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_CAT (
    bin_table_name  => 'nb_sh_sample_cat',
    data_table_name => 'mining_data_apply_v',
    xform_view_name => 'nb_sh_sample_apply_cat');   

  -- Provide the result to the next transformation effected on training data
  DBMS_DATA_MINING_TRANSFORM.XFORM_BIN_NUM (
    bin_table_name  => 'nb_sh_sample_num',
    data_table_name => 'nb_sh_sample_apply_cat',
    xform_view_name => 'nb_sh_sample_apply_prepared');
END;
/

------------------------------------------
-- CLEANUP SCORING RESULT OBJECTS (IF ANY)
--
BEGIN
  -- Drop the result tables for Model APPLY
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_apply_result';
  EXECUTE IMMEDIATE 'DROP TABLE nb_sh_sample_apply_ranked';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

------------------
-- APPLY THE MODEL
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('APPLY model to scoring data');
  DBMS_DATA_MINING.APPLY(
    model_name          => 'NB_SH_Clas_sample',
    data_table_name     => 'nb_sh_sample_apply_prepared',
    case_id_column_name => 'cust_id',
    result_table_name   => 'nb_sh_sample_apply_result');
  DBMS_OUTPUT.PUT_LINE('APPLY results in table: nb_sh_sample_apply_result');
END;
/

------------------------
-- DISPLAY APPLY RESULTS
--
-- Note that APPLY results do not need to be reverse transformed,
-- as in the case of model details. This is because class values of
-- a classification target are not (required to be) binned or normalized.
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display APPLY RESULTS'); END;
/
SELECT cust_id, prediction, probability
  FROM (SELECT cust_id, prediction, ROUND(probability,4) probability
          FROM nb_sh_sample_apply_result
        ORDER BY cust_id, prediction, probability)
 WHERE ROWNUM < 11
ORDER BY cust_id;
   
-----------------------------------------------------------
-- GENERATE RANKED APPLY RESULTS (OPTIONALLY BASED ON COST)
--

-- Skip cost matrix input - see svmc_sh.sql for example
--

-- RANK THE APPLY RESULTS
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('RANK the Scoring results to Top 2');
  DBMS_DATA_MINING.RANK_APPLY (
    apply_result_table_name     => 'nb_sh_sample_apply_result',
    case_id_column_name         => 'cust_id',
    score_column_name           => 'PREDICTION',
    score_criterion_column_name => 'PROBABILITY',
    ranked_apply_table_name     => 'nb_sh_sample_apply_ranked',
    top_n                       => 2,
    cost_matrix_table_name      => NULL);
  DBMS_OUTPUT.PUT_LINE(
    'Ranked APPLY results in table: nb_sh_sample_apply_ranked');
END;
/

-------------------------------
-- DISPLAY RANKED APPLY RESULTS
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display RANKED APPLY RESULTS'); END;
/
SELECT cust_id, prediction, probability, rank
  FROM (SELECT cust_id, prediction, ROUND(probability,4) probability, rank
          FROM nb_sh_sample_apply_ranked
        ORDER BY cust_id, rank)
 WHERE ROWNUM < 11
ORDER BY cust_id;
