/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 6
CLASSIFICATION MODELS
=================================================================
Classification predicts categorical outcomes.
Examples
Customer churn prediction
Loan approval classification
Fraud detection
Email spam detection
Algorithms Supported
Decision Tree
Naive Bayes
Support Vector Machine (SVM)
Random Forest
===============================================================*/
/*===============================================================
SECTION 1
DECISION TREE CLASSIFICATION
===============================================================*/
DROP TABLE dt_class_settings PURGE;
CREATE TABLE dt_class_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO dt_class_settings VALUES
(
'ALGO_NAME',
'ALGO_DECISION_TREE'
);
COMMIT;
/*===============================================================
DROP MODEL IF EXISTS
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('DT_SUBSCRIPTION_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE DECISION TREE MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'DT_SUBSCRIPTION_MODEL',
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => 'CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
target_column_name => 'SUBSCRIBED',
settings_table_name => 'DT_CLASS_SETTINGS'
);
END;
/
/*===============================================================
PREDICTION USING DECISION TREE
===============================================================*/
SELECT
customer_id,
age,
income,
PREDICTION
(
DT_SUBSCRIPTION_MODEL
USING age,income
) AS predicted_subscription
FROM new_customer_data;
/*===============================================================
SECTION 2
NAIVE BAYES CLASSIFICATION
===============================================================*/
DROP TABLE nb_settings PURGE;
CREATE TABLE nb_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO nb_settings VALUES
(
'ALGO_NAME',
'ALGO_NAIVE_BAYES'
);
COMMIT;
/*===============================================================
DROP MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('NB_SUBSCRIPTION_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE NAIVE BAYES MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'NB_SUBSCRIPTION_MODEL',
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => 'CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
target_column_name => 'SUBSCRIBED',
settings_table_name => 'NB_SETTINGS'
);
END;
/
/*===============================================================
NAIVE BAYES PREDICTION
===============================================================*/
SELECT
customer_id,
PREDICTION
(
NB_SUBSCRIPTION_MODEL
USING age,income
) AS predicted_subscription
FROM new_customer_data;
/*===============================================================
SECTION 3
SUPPORT VECTOR MACHINE (SVM)
===============================================================*/
DROP TABLE svm_settings PURGE;
CREATE TABLE svm_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO svm_settings VALUES
(
'ALGO_NAME',
'ALGO_SUPPORT_VECTOR_MACHINES'
);
COMMIT;
/*===============================================================
DROP MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('SVM_SUBSCRIPTION_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE SVM MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'SVM_SUBSCRIPTION_MODEL',
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => 'CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
target_column_name => 'SUBSCRIBED',
settings_table_name => 'SVM_SETTINGS'
);
END;
/
/*===============================================================
SVM PREDICTION
===============================================================*/
SELECT
customer_id,
PREDICTION
(
SVM_SUBSCRIPTION_MODEL
USING age,income
) AS predicted_subscription
FROM new_customer_data;
/*===============================================================
SECTION 4
RANDOM FOREST CLASSIFICATION
===============================================================*/
DROP TABLE rf_settings PURGE;
CREATE TABLE rf_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO rf_settings VALUES
(
'ALGO_NAME',
'ALGO_RANDOM_FOREST'
);
COMMIT;
/*===============================================================
DROP MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('RF_SUBSCRIPTION_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE RANDOM FOREST MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'RF_SUBSCRIPTION_MODEL',
mining_function => DBMS_DATA_MINING.CLASSIFICATION,
data_table_name => 'CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
target_column_name => 'SUBSCRIBED',
settings_table_name => 'RF_SETTINGS'
);
END;
/
/*===============================================================
RANDOM FOREST PREDICTION
===============================================================*/
SELECT
customer_id,
PREDICTION
(
RF_SUBSCRIPTION_MODEL
USING age,income
) AS predicted_subscription
FROM new_customer_data;
/*===============================================================
SECTION 5
PREDICTION PROBABILITY
===============================================================*/
SELECT
customer_id,
PREDICTION_PROBABILITY
(
RF_SUBSCRIPTION_MODEL,
'YES'
USING age,income
) AS probability_of_yes
FROM new_customer_data;
/*===============================================================
END OF PART 6
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/