/*===============================================================
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 7
REGRESSION MODELS
=================================================================
Regression predicts continuous numeric values.
Examples
House price prediction
Sales forecasting
Revenue prediction
Demand estimation
Algorithms Covered
Linear Regression
Generalized Linear Models (GLM)
Support Vector Machine Regression
===============================================================*/
/*===============================================================
SECTION 1
VIEW TRAINING DATASET
===============================================================*/
SELECT * FROM house_data;
/*===============================================================
SECTION 2
LINEAR REGRESSION SETTINGS
===============================================================*/
DROP TABLE lr_settings PURGE;
CREATE TABLE lr_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO lr_settings VALUES
(
'ALGO_NAME',
'ALGO_GENERALIZED_LINEAR_MODEL'
);
COMMIT;
/*===============================================================
DROP MODEL IF EXISTS
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('HOUSE_PRICE_LR_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE LINEAR REGRESSION MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'HOUSE_PRICE_LR_MODEL',
mining_function => DBMS_DATA_MINING.REGRESSION,
data_table_name => 'HOUSE_DATA',
case_id_column_name => 'HOUSE_ID',
target_column_name => 'PRICE',
settings_table_name => 'LR_SETTINGS'
);
END;
/
/*===============================================================
VERIFY MODEL
===============================================================*/
SELECT model_name, mining_function, algorithm
FROM user_mining_models
WHERE model_name='HOUSE_PRICE_LR_MODEL';
/*===============================================================
SECTION 3
PREDICT HOUSE PRICE
===============================================================*/
SELECT
house_id,
size_sqft,
location,
PREDICTION
(
HOUSE_PRICE_LR_MODEL
USING size_sqft,location
) AS predicted_price
FROM new_house_data;
/*===============================================================
SECTION 4
PREDICTION DETAILS
===============================================================*/
SELECT
house_id,
PREDICTION_DETAILS
(
HOUSE_PRICE_LR_MODEL
USING size_sqft,location
) AS prediction_info
FROM new_house_data;
/*===============================================================
SECTION 5
SUPPORT VECTOR MACHINE REGRESSION
===============================================================*/
DROP TABLE svm_reg_settings PURGE;
CREATE TABLE svm_reg_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
INSERT INTO svm_reg_settings VALUES
(
'ALGO_NAME',
'ALGO_SUPPORT_VECTOR_MACHINES'
);
COMMIT;
/*===============================================================
DROP MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('HOUSE_PRICE_SVM_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE SVM REGRESSION MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'HOUSE_PRICE_SVM_MODEL',
mining_function => DBMS_DATA_MINING.REGRESSION,
data_table_name => 'HOUSE_DATA',
case_id_column_name => 'HOUSE_ID',
target_column_name => 'PRICE',
settings_table_name => 'SVM_REG_SETTINGS'
);
END;
/
/*===============================================================
SVM REGRESSION PREDICTION
===============================================================*/
SELECT
house_id,
size_sqft,
PREDICTION
(
HOUSE_PRICE_SVM_MODEL
USING size_sqft,location
) AS predicted_price
FROM new_house_data;
/*===============================================================
SECTION 6
MODEL ATTRIBUTE IMPORTANCE
===============================================================*/
SELECT *
FROM USER_MINING_MODEL_ATTRIBUTES
WHERE MODEL_NAME='HOUSE_PRICE_LR_MODEL';
/*===============================================================
SECTION 7
VIEW MODEL PARAMETERS
===============================================================*/
SELECT *
FROM USER_MINING_MODEL_SETTINGS
WHERE MODEL_NAME='HOUSE_PRICE_LR_MODEL';
/*===============================================================
SECTION 8
MODEL COEFFICIENTS
===============================================================*/
SELECT *
FROM TABLE
(
DBMS_DATA_MINING.GET_MODEL_DETAILS_GLM
('HOUSE_PRICE_LR_MODEL')
);
/*===============================================================
END OF PART 7
===============================================================
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.
===============================================================*/