/*===============================================================
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 12
MODEL EVALUATION AND MODEL PERFORMANCE ANALYSIS
=================================================================
After building machine learning models, it is critical
to evaluate how well they perform.
Key evaluation metrics include:
Confusion Matrix
Accuracy
Precision
Recall
Prediction probability analysis
These metrics help determine if a model is reliable.
===============================================================*/
/*===============================================================
SECTION 1
GENERATE PREDICTIONS FOR TRAINING DATA
===============================================================*/
DROP TABLE customer_predictions PURGE;
CREATE TABLE customer_predictions AS
SELECT
customer_id,
subscribed AS actual_value,
PREDICTION
(
DT_SUBSCRIPTION_MODEL
USING age,income
) AS predicted_value
FROM customer_data;
/*===============================================================
VIEW PREDICTIONS
===============================================================*/
SELECT * FROM customer_predictions;
/*===============================================================
SECTION 2
CONFUSION MATRIX
===============================================================
Confusion Matrix compares actual values with predicted values.
Example structure
Predicted
YES NO
Actual YES TP FN
Actual NO FP TN
TP = True Positive
TN = True Negative
FP = False Positive
FN = False Negative
===============================================================*/
SELECT
actual_value,
predicted_value,
COUNT(*) AS count_records
FROM customer_predictions
GROUP BY actual_value, predicted_value
ORDER BY actual_value;
/*===============================================================
SECTION 3
CALCULATE MODEL ACCURACY
===============================================================
Accuracy = Correct Predictions / Total Predictions
===============================================================*/
SELECT
SUM
(
CASE
WHEN actual_value = predicted_value
THEN 1
ELSE 0
END
)
/ COUNT(*) AS accuracy
FROM customer_predictions;
/*===============================================================
SECTION 4
PRECISION
===============================================================
Precision measures how many predicted positives
were actually correct.
Precision = TP / (TP + FP)
===============================================================*/
SELECT
SUM(CASE WHEN actual_value='YES' AND predicted_value='YES' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN predicted_value='YES' THEN 1 ELSE 0 END)
AS precision
FROM customer_predictions;
/*===============================================================
SECTION 5
RECALL
===============================================================
Recall measures how many actual positives
were correctly predicted.
Recall = TP / (TP + FN)
===============================================================*/
SELECT
SUM(CASE WHEN actual_value='YES' AND predicted_value='YES' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN actual_value='YES' THEN 1 ELSE 0 END)
AS recall
FROM customer_predictions;
/*===============================================================
SECTION 6
MODEL COMPARISON
===============================================================
You can compare multiple models such as
Decision Tree
Naive Bayes
SVM
Random Forest
by generating prediction tables for each model.
Example
customer_predictions_dt
customer_predictions_nb
customer_predictions_svm
Then compare accuracy metrics.
===============================================================*/
/*===============================================================
SECTION 7
PREDICTION PROBABILITY ANALYSIS
===============================================================*/
SELECT
customer_id,
subscribed,
PREDICTION_PROBABILITY
(
DT_SUBSCRIPTION_MODEL,
'YES'
USING age,income
) AS probability_yes
FROM customer_data;
/*===============================================================
SECTION 8
MODEL METADATA
===============================================================*/
SELECT
model_name,
mining_function,
algorithm
FROM user_mining_models;
/*===============================================================
SECTION 9
MODEL PARAMETERS
===============================================================*/
SELECT *
FROM USER_MINING_MODEL_SETTINGS;
/*===============================================================
SECTION 10
MODEL ATTRIBUTE IMPORTANCE
===============================================================*/
SELECT *
FROM USER_MINING_MODEL_ATTRIBUTES;
/*===============================================================
SECTION 11
BEST PRACTICES FOR ML MODELS
===============================================================
Use larger datasets for training
Perform data cleaning
Use cross validation
Compare multiple models
Monitor prediction accuracy
===============================================================*/
/*===============================================================
END OF PART 12
===============================================================
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.
===============================================================*/