Rem
Rem $Header: svmr_sh.sql 11-dec-2003.08:50:07 pstengar Exp $
Rem
Rem svmr_sh.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      svmr_sh.sql - Sample program for DBMS_DATA_MINING package.
Rem
Rem    DESCRIPTION
Rem      This script demonstrates the use of DBMS_DATA_MINING
Rem      for Regression function using the SVM Algorithm
Rem      against the SH (Sales History) schema in the RDBMS. 
Rem
Rem    NOTES
Rem     
Rem
Rem    MODIFIED   (MM/DD/YY) 
Rem    pstengar    12/11/03 - modified apply selection query
Rem    ramkrish    10/20/03 - ramkrish_txn109085
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 svmr_sh_sample_norm';

  -- Drop the Data Transformation Views for Model BUILD
  EXECUTE IMMEDIATE 'DROP VIEW svmr_sh_sample_build_prepared';
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 SVM algorithm.
--
-- NOTE: Unlike the Classification examples, note that the target
--       attribute 'age' is normalized (i.e. transformed) here. This
--       implies that the scoring results have to be suitably de-normalized
--       based on the data preparation tables generated in this step.
--
BEGIN
  -- Normalize numerical attributes: age
  DBMS_DATA_MINING_TRANSFORM.CREATE_NORM_LIN (
    norm_table_name => 'svmr_sh_sample_norm');        

  DBMS_DATA_MINING_TRANSFORM.INSERT_NORM_LIN_ZSCORE (
    norm_table_name => 'svmr_sh_sample_norm',
    data_table_name => 'mining_data_build_v',
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (
                       'affinity_card',
                       'bulk_pack_diskettes',
                       'flat_panel_monitor',
                       'home_theater_package',
                       'bookkeeping_application',
                       'printer_supplies',
                       'y_box_games',
                       'os_doc_set_kanji',
                       'cust_id')
  );

  -- Create the transformed view
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN (
    norm_table_name => 'svmr_sh_sample_norm',
    data_table_name => 'mining_data_build_v',
    xform_view_name => 'svmr_sh_sample_build_prepared');

  DBMS_OUTPUT.PUT_LINE('BUILD DATA PREPARATION OBJECTS:');
  DBMS_OUTPUT.PUT_LINE('1. Normalization Table: svmr_sh_sample_norm');
  DBMS_OUTPUT.PUT_LINE(
    '2. Input (view) to CREATE_MODEL: svmr_sh_sample_build_prepared');
END;
/

--------------------------------------
-- CLEANUP OLD SETTINGS TABLE (IF ANY)
--
BEGIN
  -- Drop the Settings Table
  EXECUTE IMMEDIATE 'DROP TABLE svmr_sh_sample_settings';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/
 
--------------------------
-- CREATE A SETTINGS TABLE
--
-- The default regression algorithm is SVM. The default kernel is the
-- Linear Kernel. In order to override this, create and populate a
-- settings table to be used as input for CREATE_MODEL.
-- 
CREATE TABLE svmr_sh_sample_settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));
 
BEGIN       
  -- Populate settings table
  INSERT INTO svmr_sh_sample_settings (setting_name, setting_value) VALUES
  (dbms_data_mining.svms_kernel_function, dbms_data_mining.svms_gaussian);
  -- Examples of other possible overrides are:
  --(dbms_data_mining.svms_conv_tolerance,0.01);
  --(dbms_data_mining.svms_epsilon,0.1);
  --(dbms_data_mining.svms_kernel_cache_size,50000000);
  --(dbms_data_mining.svms_kernel_function,dbms_data_mining.svms_linear);
  COMMIT;
  dbms_output.put_line('Populated settings');
END;
/

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

---------------------
-- CREATE A NEW MODEL
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('Begin CREATE_MODEL - SVM - Regression');
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'SVMR_SH_Regr_sample',
    mining_function     => dbms_data_mining.regression,
    data_table_name     => 'svmr_sh_sample_build_prepared',
    case_id_column_name => 'cust_id',
    target_column_name  => 'age',
    settings_table_name => 'svmr_sh_sample_settings');
  DBMS_OUTPUT.PUT_LINE('End   CREATE_MODEL - SVM - Regression');
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('SVMR_SH_Regr_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('SVMR_SH_Regr_sample'))
ORDER BY attribute_name;

------------------------
-- DISPLAY MODEL DETAILS
--
-- Skip. GET_MODEL_DETAILS_SVM is supported only for Linear Kernels.
-- The current model is built using a Gaussian Kernel. Uncomment
-- the following code if you build a regression model with a linear kernel.
--
-- 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 T.class,
--        S.attribute_name,
--        S.attribute_value,
--        S.coefficient
--   FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM('SVMR_SH_Regr_sample')) T,
--        TABLE(T.attribute_set) S
--  WHERE ROWNUM < 11
-- ORDER BY T.class;

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

--------------------------------------------------------
-- CLEANUP TEST DATA PREPARATION OBJECTS (IF APPLICABLE)
--
BEGIN
  -- Drop the Data Transformation Views for Model TEST
  EXECUTE IMMEDIATE 'DROP VIEW svmr_sh_sample_test_targets';
  EXECUTE IMMEDIATE 'DROP VIEW svmr_sh_sample_test_prepared';
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
   -- Create the transformed view
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN (
    norm_table_name => 'svmr_sh_sample_norm',
    data_table_name => 'mining_data_test_v',
    xform_view_name => 'svmr_sh_sample_test_prepared');
END;
/

---------------------------------------
-- CLEANUP TEST RESULT OBJECTS (IF ANY)
--
-- The supported metrics for Regression are:
-- 1. Root Mean Square Error
-- 2. Mean Absolute Error
-- which are unary results computed using simple SQL queries.
-- Only the intermediate apply results table needs to be dropped.
--
BEGIN
  -- Drop the intermediate and result tables for Model TEST
  EXECUTE IMMEDIATE 'DROP TABLE svmc_sh_sample_test_apply';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

------------------------------------
-- 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 svmr_sh_sample_test_targets AS
SELECT cust_id, age
  FROM svmr_sh_sample_test_prepared;

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

-- COMPUTE TEST METRICS
--
-- The supported metrics for Regression are:
-- 1. Root Mean Square Error
-- 2. Mean Absolute Error
-- which are computed using simple queries shown below.
--
-- ROOT MEAN SQUARED ERROR
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display ROOT MEAN SQUARED ERROR'); END;
/
SELECT SQRT(AVG((a.prediction - b.age) * (a.prediction - b.age))) rmse
  FROM svmr_sh_sample_test_apply a,
       svmr_sh_sample_test_targets b
 WHERE a.cust_id = b.cust_id;

-- MEAN ABSOLUTE ERROR
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display MEAN ABSOLUTE ERROR'); END;
/
SELECT AVG(ABS(a.prediction - b.age)) mae
  FROM svmr_sh_sample_test_apply a,
       svmr_sh_sample_test_targets b
  WHERE a.cust_id = b.cust_id;

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

-----------------------------------------------------------
-- CLEANUP SCORING DATA PREPARATION OBJECTS (IF APPLICABLE)
--
BEGIN
  -- Drop the Data Transformation Views for Model APPLY
  EXECUTE IMMEDIATE 'DROP VIEW svmr_sh_sample_apply_prepared';
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
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN (
    norm_table_name => 'svmr_sh_sample_norm',
    data_table_name => 'mining_data_apply_v',
    xform_view_name => 'svmr_sh_sample_apply_prepared');
END;
/

------------------------------------------
-- CLEANUP SCORING RESULT OBJECTS (IF ANY)
--
BEGIN
  -- Drop the result tables for Model APPLY
  EXECUTE IMMEDIATE 'DROP TABLE svmr_sh_sample_apply_result';
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          => 'SVMR_SH_Regr_sample',
    data_table_name     => 'svmr_sh_sample_apply_prepared',
    case_id_column_name => 'cust_id',
    result_table_name   => 'svmr_sh_sample_apply_result');
  DBMS_OUTPUT.PUT_LINE('Begin APPLY on scoring data');
  DBMS_OUTPUT.PUT_LINE('APPLY results in table: svmr_sh_sample_apply_result');
END;
/

------------------------
-- DISPLAY APPLY RESULTS
--
-- Note that APPLY results have to be reverse transformed here,
-- because the target attribute 'AGE' was normalized in the data
-- preparation phase of APPLY data (as a consequence of similar
-- data preparation for model build data)
--
-- This is done using a simple query which joins the Apply results
-- with the Normalization table (built during data preparation step
-- for Build data) as shown below.
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display (Remapped) APPLY RESULTS'); END;
/
SELECT cust_id, prediction
  FROM (SELECT A.cust_id, ((A.prediction * N.scale) + N.shift) prediction 
          FROM svmr_sh_sample_apply_result A,
               svmr_sh_sample_norm N
         WHERE N.col = 'AGE'
	 ORDER BY A.cust_id)
 WHERE ROWNUM < 11;
   
----------------------------------------
-- RANKED APPLY RESULTS - NOT APPLICABLE
--
-- The results of scoring a regression model return just the case identifier
-- and the target class. There is no probability based on which one can rank
-- the results.
--
