DROP TABLE BANKFULLADDITIONAL
DROP TABLE DECISiON_TREE_MODEL_SETTINGS

ALTER TABLE BANKFULL add (ROW_ID VARCHAR2(30));
UPDATE BANKFULL SET row_id = ROWNUM;

CREATE TABLE bankfull_test
AS SELECT * FROM BANKFULLADDITIONAL
WHERE ROWNUM < 1;


DROP TABLE bankfull_data_valid1
CREATE TABLE bankfull_data_valid1
AS SELECT * FROM BANKFULLADDITIONAL



SELECT * FROM bankfull_data_valid1



DROP TABLE decision_tree_model_settings;

CREATE TABLE decision_tree_model_settings (
setting_name VARCHAR2(300),
setting_value VARCHAR2(300));

BEGIN
   INSERT INTO decision_tree_model_settings (setting_name, setting_value)
   VALUES (dbms_data_mining.algo_name,dbms_data_mining.algo_decision_tree);

   INSERT INTO decision_tree_model_settings (setting_name, setting_value)
   VALUES (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
   COMMIT;
END;




BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
   model_name => 'Decision_Tree',
   mining_function => dbms_data_mining.classification,
   data_table_name => 'bankfull',
   case_id_column_name => 'row_id',
   target_column_name => 'y',
   settings_table_name => 'decision_tree_model_settings');
END;

describe user_mining_model_settings


SELECT model_name,
   mining_function,
   algorithm,
   build_duration,
   model_size
FROM user_MINING_MODELS;


SELECT setting_name,
   setting_value,
   setting_type
FROM user_mining_model_settings;


SELECT attribute_name,
   attribute_type,
   usage_type,
   target
FROM all_mining_model_attributes
WHERE model_name = 'DECISION_TREE';



CREATE OR REPLACE VIEW demo_class_dt_test_results
AS
SELECT row_id,
   prediction(DECISION_TREE USING *) predicted_value,
   prediction_probability(DECISION_TREE USING *) probability
FROM BANKFULL;

SELECT *
FROM demo_class_dt_test_results;

DROP TABLE NEW_DATA_SCORED

BEGIN
   dbms_data_mining.apply(
   model_name => 'Decision_Tree',
   data_table_name => 'BANKFULL',
   case_id_column_name => 'row_id',
   result_table_name => 'NEW_DATA_SCORED');
END;



SELECT *
FROM BANKFULLADDITIONAL

DECLARE
   v_accuracy NUMBER;
BEGIN
DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
   accuracy => v_accuracy,
   apply_result_table_name => 'demo_class_dt_test_results',
   target_table_name => 'BANKFULL',
   case_id_column_name => 'row_id',
   target_column_name => 'y',
   confusion_matrix_table_name => 'demo_class_dt_confusion_matrix_final',
   score_column_name => 'PREDICTED_VALUE',
   score_criterion_column_name => 'PROBABILITY',
   cost_matrix_table_name => NULL,
   apply_result_schema_name => NULL,
   target_schema_name => NULL,
   cost_matrix_schema_name => NULL,
   score_criterion_type => 'PROBABILITY');
   DBMS_OUTPUT.PUT_LINE('**** MODEL ACCURACY ****: ' || ROUND(v_accuracy,4));
END;


SELECT *
FROM demo_class_dt_confusion_matrix;