/*===============================================================
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 8
CLUSTERING MODELS
=================================================================
Clustering groups similar records together.
Unlike classification or regression,
clustering does not require a target column.
Example
Customer segmentation based on:
age
income
spending behavior
===============================================================*/
/*===============================================================
SECTION 1
VIEW CUSTOMER DATA
===============================================================*/
SELECT * FROM customer_data;
/*===============================================================
SECTION 2
K-MEANS CLUSTERING SETTINGS
===============================================================*/
DROP TABLE kmeans_settings PURGE;
CREATE TABLE kmeans_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
/* Algorithm selection */
INSERT INTO kmeans_settings VALUES
(
'ALGO_NAME',
'ALGO_KMEANS'
);
/* Number of clusters */
INSERT INTO kmeans_settings VALUES
(
'KMNS_NUM_CLUSTERS',
'3'
);
COMMIT;
/*===============================================================
DROP MODEL IF EXISTS
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('CUSTOMER_KMEANS_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE K-MEANS MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'CUSTOMER_KMEANS_MODEL',
mining_function => DBMS_DATA_MINING.CLUSTERING,
data_table_name => 'CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
settings_table_name => 'KMEANS_SETTINGS'
);
END;
/
/*===============================================================
VERIFY MODEL CREATION
===============================================================*/
SELECT model_name, mining_function, algorithm
FROM user_mining_models
WHERE model_name='CUSTOMER_KMEANS_MODEL';
/*===============================================================
SECTION 3
ASSIGN CUSTOMERS TO CLUSTERS
===============================================================*/
SELECT
customer_id,
age,
income,
CLUSTER_ID
(
CUSTOMER_KMEANS_MODEL
USING age,income
) AS cluster_number
FROM customer_data;
/*===============================================================
SECTION 4
CLUSTER PROBABILITY
===============================================================*/
SELECT
customer_id,
CLUSTER_PROBABILITY
(
CUSTOMER_KMEANS_MODEL
USING age,income
) AS cluster_probability
FROM customer_data;
/*===============================================================
SECTION 5
VIEW CLUSTER DETAILS
===============================================================*/
SELECT *
FROM TABLE
(
DBMS_DATA_MINING.GET_MODEL_DETAILS_KM
('CUSTOMER_KMEANS_MODEL')
);
/*===============================================================
SECTION 6
EXPECTATION MAXIMIZATION CLUSTERING
===============================================================*/
DROP TABLE em_settings PURGE;
CREATE TABLE em_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
/* Select EM algorithm */
INSERT INTO em_settings VALUES
(
'ALGO_NAME',
'ALGO_EXPECTATION_MAXIMIZATION'
);
COMMIT;
/*===============================================================
DROP MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('CUSTOMER_EM_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
CREATE EM CLUSTERING MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'CUSTOMER_EM_MODEL',
mining_function => DBMS_DATA_MINING.CLUSTERING,
data_table_name => 'CUSTOMER_DATA',
case_id_column_name => 'CUSTOMER_ID',
settings_table_name => 'EM_SETTINGS'
);
END;
/
/*===============================================================
ASSIGN CLUSTERS USING EM MODEL
===============================================================*/
SELECT
customer_id,
age,
income,
CLUSTER_ID
(
CUSTOMER_EM_MODEL
USING age,income
) AS cluster_number
FROM customer_data;
/*===============================================================
SECTION 7
VIEW CLUSTER STATISTICS
===============================================================*/
SELECT *
FROM USER_MINING_MODEL_ATTRIBUTES
WHERE MODEL_NAME='CUSTOMER_KMEANS_MODEL';
/*===============================================================
SECTION 8
CUSTOMER SEGMENTATION EXAMPLE
===============================================================
Example interpretation
Cluster 1 β Young customers with low income
Cluster 2 β Mid-age customers with moderate income
Cluster 3 β High income customers
Businesses can use clustering for:
Targeted marketing
Product recommendations
Customer segmentation
===============================================================*/
/*===============================================================
END OF PART 8
===============================================================
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.
===============================================================*/