Rem
Rem $Header: ar_sh.sql 11-dec-2003.15:31:07 mmcracke Exp $
Rem
Rem ar_sh.sql
Rem
Rem Copyright (c) 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      ar_sh.sql - Sample program for DBMS_DATA_MINING package.
Rem
Rem    DESCRIPTION
Rem      This script demonstrates the use of DBMS_DATA_MINING
Rem      for Association function using the Apriori Algorithm
Rem      against the SH (Sales History) schema in the RDBMS. 
Rem
Rem    NOTES
Rem     
Rem
Rem    MODIFIED   (MM/DD/YY) 
Rem    mmcracke    12/11/03 - Remove RuleID from results display 
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
-----------------------------------------------------------------------

------------------------------------
-- DATA PREPARATION - NOT APPLICABLE
--
-- The data used for demonstrating Association is market_basket_v.
-- It is different from those used for other mining functions, in
-- that it is synthetisized to emulate market basket data. See
-- dm/demo/data/README.txt for details.


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

--------------------------
-- CREATE A SETTINGS TABLE
--
-- Override the default max rules
-- 
CREATE TABLE ar_sh_sample_settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));
 
BEGIN       
  -- Populate settings table
  INSERT INTO ar_sh_sample_settings VALUES
  (dbms_data_mining.asso_max_rule_length,3);
  COMMIT;

  -- Examples of other possible overrides are:
  --(dbms_data_mining.asso_min_confidence,0.1);
  --(dbms_data_mining.asso_min_support,0.1);
  dbms_output.put_line('Populated settings');
END;
/

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

---------------------
-- CREATE A NEW MODEL
--
BEGIN
  DBMS_OUTPUT.PUT_LINE('Begin CREATE_MODEL - Association');
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'AR_SH_sample',
    mining_function     => dbms_data_mining.association,
    data_table_name     => 'market_basket_v',
    case_id_column_name => 'cust_id',
    settings_table_name => 'ar_sh_sample_settings');
  DBMS_OUTPUT.PUT_LINE('End   CREATE_MODEL - Association');
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('AR_SH_sample'))
ORDER BY setting_name;

-------------------------------------------
-- DISPLAY MODEL SIGNATURE - NOT APPLICABLE
--
-- The model signature is applicable only for predictive models.
-- It provides the information about the predictor and target attributes.
-- Association models are descriptive models that are summaries of data.
--

-----------------------------------------------------------------------
--                            DISPLAY MODEL CONTENTS
-----------------------------------------------------------------------

----------------------------
-- DISPLAY FREQUENT ITEMSETS
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display (formatted) FREQUENT ITEMSETS'); END;
/
SET heading on;
BREAK ON itemset_id SKIP 1;
column item format a30
SELECT F.itemset_id,
       I.column_value AS item,
       F.support,
       F.number_of_items
  FROM TABLE(DBMS_DATA_MINING.GET_FREQUENT_ITEMSETS('AR_SH_sample')) F,
       TABLE(F.items) I
ORDER BY itemset_id,column_value,support,number_of_items;

----------------------------------------
-- DISPLAY ASSOCIATION RULES (formatted)
--
BEGIN DBMS_OUTPUT.PUT_LINE('Display (formatted) ASSOCIATION RULES'); END;
/
SET HEADING OFF;
BREAK ON rule_id,rule_support,rule_confidence;
BREAK ON antecedent,consequent;
BREAK ON antecedent_support,consequent_support;

SELECT 'Rule Support: ' || R.rule_support rule_support,
--       ' Rule Id: '     || R.rule_id rule_id ,
       ' Rule Confidence: ' || R.rule_confidence rule_confidence,
       ' Antecedent attribute: ' ||
       ante.attribute_name  ||
       ante.conditional_operator ||
       NVL(ante.attribute_str_value,ante.attribute_num_value) antecedent,
       ' Consequent attribute:' ||
       cons.attribute_name ||
       cons.conditional_operator ||
       NVL(cons.attribute_str_value,cons.attribute_num_value) consequent,
       ' Antecedent support: ' || ante.attribute_support antecedent_support,
       ' Consequent support: ' || cons.attribute_support consequent_support
 FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('AR_SH_sample')) R,
      TABLE(R.antecedent) ante,
      TABLE(R.consequent) cons
ORDER BY 1 DESC,2,3,4,5,6;
