Rem
Rem $Header: km_sh.sql 20-oct-2003.14:30:18 ramkrish Exp $
Rem
Rem km_sh.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      km_sh.sql - Sample program for DBMS_DATA_MINING package.
Rem
Rem    DESCRIPTION
Rem      This script demonstrates the use of DBMS_DATA_MINING
Rem      for Clustering function using the K-Means Algorithm
Rem      against the SH (Sales History) schema in the RDBMS. 
Rem
Rem    NOTES
Rem     
Rem
Rem    MODIFIED   (MM/DD/YY) 
Rem    ramkrish    10/20/03 - ramkrish_txn109085
Rem    pstengar    10/17/03 - added denormalization of model details
Rem    ramkrish    10/16/03 - 
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 km_sh_sample_norm';

  -- Drop the Data Transformation Views for Model BUILD
  EXECUTE IMMEDIATE 'DROP VIEW km_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 K-Means algorithm.
-- In this case, the data for attributes 'age' and 'yrs_residence' requires
-- preparation.
--
BEGIN
  -- Normalize numerical attributes: age, yrs_residence,  
  DBMS_DATA_MINING_TRANSFORM.CREATE_NORM_LIN(
    norm_table_name => 'km_sh_sample_norm');

  DBMS_DATA_MINING_TRANSFORM.INSERT_NORM_LIN_ZSCORE (
    norm_table_name   => 'km_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 => 'km_sh_sample_norm',
    data_table_name => 'mining_data_build_v',
    xform_view_name => 'km_sh_sample_build_prepared');

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

--------------------------------------
-- CLEANUP OLD SETTINGS TABLE (IF ANY)
--
BEGIN
  -- Drop the Settings Table
  EXECUTE IMMEDIATE 'DROP TABLE km_sh_sample_settings';
EXCEPTION WHEN OTHERS THEN
  NULL;
END;
/

--------------------------
-- CREATE A SETTINGS TABLE
--
-- K-Means is the default Clustering algorithm. For this sample,
-- we skip specification of any overrides to defaults
--
-- Uncomment the appropriate sections of the code below if you
-- want to override any defaults.
-- 
-- CREATE TABLE km_sh_sample_settings (
--   setting_name  VARCHAR2(30),
--   setting_value VARCHAR2(30));
 
-- BEGIN       
  -- Populate settings table
  -- INSERT INTO kmns_settings (setting_name, setting_value) VALUES
  -- Examples of possible overrides are:
  -- (dbms_data_mining.kmns_distance,dbms_data_mining.kmns_euclidean);
  -- (dbms_data_mining.kmns_iterations,3);
  -- (dbms_data_mining.kmns_block_growth,2);
  -- (dbms_data_mining.kmns_conv_tolerance,0.01);
  -- (dbms_data_mining.kmns_split_criterion,dbms_data_mining.kmns_variance);
  -- (dbms_data_mining.kmns_min_pct_attr_support,0.1);
  -- (dbms_data_mining.kmns_num_bins,10);
  -- DBMS_OUTPUT.PUT_LINE('Populated settings');
-- END;
-- /

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

---------------------
-- CREATE A NEW MODEL
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('Begin CREATE_MODEL - K-Means - Clustering');
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'KM_SH_sample',
    mining_function     => dbms_data_mining.clustering,
    data_table_name     => 'km_sh_sample_build_prepared',
    case_id_column_name => 'cust_id',
    target_column_name  => NULL,
    settings_table_name => NULL);
  DBMS_OUTPUT.PUT_LINE('End   CREATE_MODEL - K-Means - Clustering');
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('KM_SH_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('KM_SH_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 CLUSTER DESCRIPTION'); END;
/
SELECT T.id cluster_id,
       T.record_count,
       T.parent,
       T.tree_level,
       T.dispersion
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('KM_SH_sample')) T
 WHERE ROWNUM < 11
ORDER BY id;

BEGIN DBMS_OUTPUT.PUT_LINE('DISPLAY TAXONOMY'); END;
/
SELECT T.id cluster_id,
       C.id
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('KM_SH_sample')) T,
       TABLE(T.child) C
 WHERE ROWNUM < 11
ORDER BY T.id;

BEGIN DBMS_OUTPUT.PUT_LINE('DISPLAY CENTROIDS FOR TWO LEAF CLUSTERS'); END;
/
column attribute_name format a20
column mode_value format a25
SELECT T.id cluster_id,
       C.attribute_name,
       DECODE(C.attribute_name, N.col, C.mean*N.scale+N.shift, C.mean) mean,
       C.mode_value,
       C.variance
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('KM_SH_sample')) T,
       TABLE(T.centroid) C,
       km_sh_sample_norm N
 WHERE T.id > 17 -- to restrict output; remove or change as required
 AND   C.attribute_name = N.col (+)
ORDER BY T.id, C.attribute_name;

BEGIN DBMS_OUTPUT.PUT_LINE('DISPLAY HISTOGRAM FOR TWO LEAF CLUSTERS'); END;
/
column attribute_name format a20
column label format a30
SELECT T.id,
       H.attribute_name,
       DECODE(H.attribute_name, N.col, H.lower_bound*N.scale+N.shift, H.lower_bound) lower_bound,
       DECODE(H.attribute_name, N.col, H.upper_bound*N.scale+N.shift, H.upper_bound) upper_bound,
       H.count
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('KM_SH_SAMPLE')) T,
       TABLE(T.histogram) H,
       km_sh_sample_norm N
WHERE T.id > 17 AND ROWNUM < 20
-- to restrict output; remove or change as required
AND   H.attribute_name = N.col (+)
ORDER BY T.id, H.attribute_name, H.label;

BEGIN DBMS_OUTPUT.PUT_LINE('DISPLAY RULES FOR TWO LEAF CLUSTERS'); END;
/
SELECT T.id rule_id,
       T.rule.rule_support support,
       T.rule.rule_confidence confidence
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('KM_SH_SAMPLE')) T
ORDER BY T.id;

BEGIN DBMS_OUTPUT.PUT_LINE('DISPLAY RULE DETAILS FOR TWO LEAF CLUSTERS'); END;
/
column attribute_name format a25
column conditional_operator format a5
column sval format a15
SELECT T.id                   rule_id,
       A.attribute_name,
       A.conditional_operator op,
       DECODE(A.attribute_name, N.col, A.attribute_num_value*N.scale+N.shift, A.attribute_num_value) nval,
       A.attribute_str_value  sval,
       A.attribute_support    support,
       A.attribute_confidence confidence
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('KM_SH_SAMPLE')) T,
       TABLE(T.rule.antecedent) A,
       km_sh_sample_norm N
WHERE T.id > 17 AND ROWNUM < 20
-- to restrict output; remove or change as required
AND   A.attribute_name = N.col (+)
ORDER BY T.id;

------------------------------------------------------------------------------
--                               APPLY THE MODEL
--
-- For a descriptive mining function like Clustering, "Scoring" involves
-- providing the probability values for each cluster.
------------------------------------------------------------------------------

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

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

------------------------
-- DISPLAY APPLY RESULTS
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display APPLY RESULTS'); END;
/
SELECT cust_id, cluster_id, probability
  FROM (SELECT cust_id, cluster_id, ROUND(probability,4) probability
          FROM km_sh_sample_apply_result
        ORDER BY cust_id, probability)
 WHERE ROWNUM < 11
ORDER BY cust_id;
   
--------------------------------------------------------
-- GENERATE RANKED APPLY RESULTS (ranked on probability)
--
-- This is another example (besides the one in svmr_sh.sql)
-- of reuse of the common ranking function. Here the ranking
-- of cluster id's is done based on probability.
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('RANK the Scoring results to Top 2');
  DBMS_DATA_MINING.RANK_APPLY (
    apply_result_table_name     => 'km_sh_sample_apply_result',
    case_id_column_name         => 'cust_id',
    score_column_name           => 'cluster_id',
    score_criterion_column_name => 'probability',
    ranked_apply_table_name     => 'km_sh_sample_apply_ranked',
    top_n                       => 2);
  DBMS_OUTPUT.PUT_LINE(
    'Ranked APPLY results in table: km_sh_sample_apply_ranked');
END;
/

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