/*===============================================================
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 9
TIME SERIES FORECASTING
=================================================================
Time series forecasting predicts future values based on
historical time-ordered data.
Examples
Sales forecasting
Stock price prediction
Inventory demand forecasting
Weather trend prediction
Oracle Machine Learning supports time series models
such as ARIMA.
In this example we will forecast monthly sales.
Training Table
sales_data
Columns
month_id
sales
===============================================================*/
/*===============================================================
SECTION 1
VIEW SALES DATA
===============================================================*/
SELECT * FROM sales_data
ORDER BY month_id;
/*===============================================================
SECTION 2
CREATE FORECAST SETTINGS TABLE
===============================================================*/
DROP TABLE ts_settings PURGE;
CREATE TABLE ts_settings
(
setting_name VARCHAR2(30),
setting_value VARCHAR2(4000)
);
/* Select ARIMA algorithm */
INSERT INTO ts_settings VALUES
(
'ALGO_NAME',
'ALGO_ARIMA'
);
/* Forecast horizon */
INSERT INTO ts_settings VALUES
(
'ARIMA_FORECAST_HORIZON',
'3'
);
COMMIT;
/*===============================================================
SECTION 3
DROP MODEL IF EXISTS
===============================================================*/
BEGIN
DBMS_DATA_MINING.DROP_MODEL('SALES_FORECAST_MODEL');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
/*===============================================================
SECTION 4
CREATE TIME SERIES MODEL
===============================================================*/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL
(
model_name => 'SALES_FORECAST_MODEL',
mining_function => DBMS_DATA_MINING.TIME_SERIES,
data_table_name => 'SALES_DATA',
case_id_column_name => 'MONTH_ID',
target_column_name => 'SALES',
settings_table_name => 'TS_SETTINGS'
);
END;
/
/*===============================================================
VERIFY MODEL CREATION
===============================================================*/
SELECT
model_name,
mining_function,
algorithm
FROM user_mining_models
WHERE model_name='SALES_FORECAST_MODEL';
/*===============================================================
SECTION 5
CREATE FUTURE PERIOD TABLE
===============================================================*/
DROP TABLE future_sales_data PURGE;
CREATE TABLE future_sales_data
(
month_id DATE
);
INSERT INTO future_sales_data VALUES (DATE '2024-07-01');
INSERT INTO future_sales_data VALUES (DATE '2024-08-01');
INSERT INTO future_sales_data VALUES (DATE '2024-09-01');
COMMIT;
/*===============================================================
SECTION 6
FORECAST FUTURE SALES
===============================================================*/
SELECT
month_id,
PREDICTION
(
SALES_FORECAST_MODEL
USING month_id
) AS predicted_sales
FROM future_sales_data
ORDER BY month_id;
/*===============================================================
SECTION 7
VIEW FORECAST DETAILS
===============================================================*/
SELECT
month_id,
PREDICTION_DETAILS
(
SALES_FORECAST_MODEL
USING month_id
) AS forecast_information
FROM future_sales_data;
/*===============================================================
SECTION 8
VIEW MODEL PARAMETERS
===============================================================*/
SELECT *
FROM USER_MINING_MODEL_SETTINGS
WHERE MODEL_NAME='SALES_FORECAST_MODEL';
/*===============================================================
SECTION 9
BUSINESS INTERPRETATION
===============================================================
Example interpretation
If predicted_sales shows increasing trend,
business can prepare for higher demand.
Applications
Inventory planning
Revenue forecasting
Demand prediction
Production planning
===============================================================*/
/*===============================================================
SECTION 10
TREND ANALYSIS QUERY
===============================================================*/
SELECT
month_id,
sales,
LAG(sales) OVER (ORDER BY month_id) AS previous_month,
sales - LAG(sales) OVER (ORDER BY month_id) AS growth
FROM sales_data
ORDER BY month_id;
/*===============================================================
END OF PART 9
===============================================================
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.
===============================================================*/