6.7.3.5 Generate Predictions for a Forecasting Model

After training the model, you can generate predictions.

To generate predictions, use the sample data from the electricity_demand_test dataset. Even though the table has labels for the demand target column, the column is not considered when generating predictions. This allows you to compare the predictions to the actual values in the dataset and determine if the predictions are reliable. Once you determine the trained model is reliable for generating predictions, you can start using unlabeled datasets for generating predictions.

The datetime_index column must be included. If using exogenous_variables, they must also be included. Any extra columns, for example endogenous_variables, are ignored for the prediction, but included in the output table.

As of MySQL 8.4.0, prediction interval values are included in the prediction results. See Prediction Intervals to learn more.

You cannot run ML_PREDICT_ROW with forecasting models.

Before You Begin

Complete the following tasks:

• Prepare Data for a Forecasting Model.

• Review how to Train a Forecasting Model.

Generate Forecasts for a Table

1. If not already done, load the model. You can use the session variable for the model that is valid for the duration of the connection. Alternatively, you can use the model handle previously set. For the option to set the user name, you can set it to NULL.

   The following example uses the session variable.

   mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);

   The following example uses the model handle.

   mysql> CALL sys.ML_MODEL_LOAD('forecasting_use_case', NULL);

2. Make predictions for the test dataset by using the ML_PREDICT_TABLE routine.

   mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);

   Replace table_name, model_handle, and output_table_name with your own values. Add options as needed.

   As of MySQL 9.4.1, you have the option to specify the input table and output table as the same table if specific conditions are met. See Input Tables and Output Tables to learn more.

   The following example runs ML_PREDICT_TABLE on the testing dataset previously created.

   mysql> CALL sys.ML_PREDICT_TABLE('forecasting_data.electricity_demand_test', @model, 'forecasting_data.electricity_demand_predictions', NULL);

   Where:

   • forecasting_data.electricity_demand_test is the fully qualified name of the input table that contains the data to generate predictions for (database_name.table_name).

   • @model is the session variable for the model handle.

   • forecasting_data.electricity_demand_predictions is the fully qualified name of the output table with predictions (database_name.table_name).

   • NULL sets no options for the routine.

3. Query the demand, and ml_results columns from the output table. This allows you to compare the real demand with the generated forecast. You can also review the lower bound and upper bound prediction interval values for each forecast. Since no prediction interval value is set when running ML_PREDICT_TABLE, the default value of 0.95 is used.

   mysql> SELECT Approved, Prediction, ml_results FROM electricity_demand_predictions;
   +---------+-------------------------------------------------------------------------------------------------------------------------+
   | demand  | ml_results                                                                                                              |
   +---------+-------------------------------------------------------------------------------------------------------------------------+
   | 1379.42 | {"predictions": {"demand": 1316.5263873105694, "prediction_interval_demand": [1312.6487504526897, 1320.404024168449]}}  |
   | 1426.11 | {"predictions": {"demand": 1322.148597544633, "prediction_interval_demand": [1317.7966015800637, 1326.5005935092024]}}  |
   | 1381.74 | {"predictions": {"demand": 1327.6276527841787, "prediction_interval_demand": [1322.8480699970519, 1332.4072355713056]}} |
   | 1488.34 | {"predictions": {"demand": 1332.9671980996688, "prediction_interval_demand": [1327.7951891070384, 1338.1392070922993]}} |
   +---------+-------------------------------------------------------------------------------------------------------------------------+
   4 rows in set (0.0455 sec)

What's Next

• Learn how to Score a Forecasting Model