3.9.2 Using a Forecasting Model

To produce a forecast, run the ML_PREDICT_TABLE routine on data with the same columns as the training model. datetime_index must be included. exogenous_variables must also be included, if used. Any extra columns, for example endogenous_variables, are ignored for the prediction, but included in the return table.

For instructions to use the ML_PREDICT_TABLE and ML_SCORE routines, see Section 3.7, “Predictions”, and Section 3.13.6, “Scoring Models”. For the complete list of option descriptions, see ML_PREDICT_TABLE and ML_SCORE.

As of MySQL 8.1.0 ML_SCORE does not require target_column_name for forecasting, and it can be set to NULL.

ML_PREDICT_ROW cannot be used with forecasting models.

Syntax Examples

• A forecasting example with univariate endogenous_variables. Before MySQL 8.0.32, the ML_PREDICT_TABLE routine does not include options, and the results do not include the ml_results column:

  mysql> CALL sys.ML_TRAIN('mlcorpus.opsd_germany_daily_train', 'consumption', 
         JSON_OBJECT('task', 'forecasting', 
         'datetime_index', 'ddate', 
         'endogenous_variables', JSON_ARRAY('consumption')), 
         @forecast_model);
  Query OK, 0 rows affected (11.51 sec)
  
  mysql> CALL sys.ML_MODEL_LOAD(@forecast_model, NULL);
  Query OK, 0 rows affected (1.07 sec)
  
  mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.opsd_germany_daily_test', 
            @forecast_model, 'mlcorpus.opsd_germany_daily_train_predictions1');
  Query OK, 0 rows affected (1.50 sec)
  
  mysql> SELECT * FROM opsd_germany_daily_train_predictions1 LIMIT 5;
  +------------+---------+--------+------------+------------+
  | ddate      | wind    | solar  | wind_solar | Prediction |
  +------------+---------+--------+------------+------------+
  | 2017-12-01 |  52.323 | 19.266 |     71.589 |    1528.13 |
  | 2017-12-02 | 126.274 | 16.459 |    142.733 |    1333.16 |
  | 2017-12-03 |  387.49 | 12.411 |    399.901 |     1276.1 |
  | 2017-12-04 | 479.798 | 10.747 |    490.545 |    1602.18 |
  | 2017-12-05 | 611.488 | 10.953 |    622.441 |    1615.38 |
  +------------+---------+--------+------------+------------+
  5 rows in set (0.01 sec)
  
  mysql> CALL sys.ML_SCORE('mlcorpus.opsd_germany_daily_test', 'consumption', 
            @forecast_model, 'neg_sym_mean_abs_percent_error', @score);
  Query OK, 0 rows affected (1.40 sec)
   
  mysql> SELECT @score;
  +----------------------+
  | @score               |
  +----------------------+
  | -0.07764234393835068 |
  +----------------------+
  1 row in set (0.00 sec)

• As of MySQL 8.0.32, a forecasting example with univariate endogenous_variables and exogenous_variables:

  mysql> CALL sys.ML_TRAIN('mlcorpus.opsd_germany_daily_train', 'consumption', 
         JSON_OBJECT('task', 'forecasting', 
         'datetime_index', 'ddate', 
         'endogenous_variables', JSON_ARRAY('consumption'), 
         'exogenous_variables', JSON_ARRAY('wind', 'solar', 'wind_solar')), 
         @forecast_model);
  Query OK, 0 rows affected (11.51 sec)
   
  mysql> CALL sys.ML_MODEL_LOAD(@forecast_model, NULL);
  Query OK, 0 rows affected (0.87 sec)
   
  mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.opsd_germany_daily_test', 
            @forecast_model, 'mlcorpus.opsd_germany_daily_train_predictions2', NULL);
  Query OK, 0 rows affected (1.30 sec)
   
  mysql> SELECT * FROM opsd_germany_daily_train_predictions2 LIMIT 5;
  +-----+------------+--------------------+--------------------+--------------------+--------------------+------------------------------------------------------+
  | _id | ddate      | consumption        | wind               | solar              | wind_solar         | ml_results                                           |
  +-----+------------+--------------------+--------------------+--------------------+--------------------+------------------------------------------------------+
  |   1 | 2015-12-30 | 1496.9310000000005 |  578.6919999999999 |             33.549 |            612.241 | {'predictions': {'consumption': 1513.6390000000001}} |
  |   2 | 2015-12-31 |           1533.091 |  586.7679999999999 |             33.653 |            620.421 | {'predictions': {'consumption': 1505.791}}           |
  |   3 | 2016-01-01 | 1521.9320000000002 |            385.009 | 44.772999999999996 |  429.7819999999999 | {'predictions': {'consumption': 1482.9890000000005}} |
  |   4 | 2016-01-02 |           1518.605 | 283.66299999999995 |              47.09 | 330.75300000000004 | {'predictions': {'consumption': 1237.624}}           |
  +-----+------------+--------------------+--------------------+--------------------+--------------------+------------------------------------------------------+
  4 rows in set (0.00 sec)
   
  mysql> CALL sys.ML_SCORE('mlcorpus.opsd_germany_daily_test', 'consumption', 
            @forecast_model, 'neg_sym_mean_abs_percent_error', @score);
  Query OK, 0 rows affected (1.11 sec)
   
  mysql> SELECT @score;
  +----------------------+
  | @score               |
  +----------------------+
  | -0.06471854448318481 |
  +----------------------+
  1 row in set (0.00 sec)

• As of MySQL 8.0.32, a forecasting example with multivariate endogenous_variables and exogenous_variables:

  mysql> CALL sys.ML_TRAIN('mlcorpus.opsd_germany_daily_train', 'consumption', 
            JSON_OBJECT('task', 'forecasting', 
            'datetime_index', 'ddate', 
            'endogenous_variables', JSON_ARRAY('consumption', 'wind'), 
            'exogenous_variables', JSON_ARRAY('solar')), 
            @forecast_model);
  Query OK, 0 rows affected (27.84 sec)
   
  mysql> CALL sys.ML_MODEL_LOAD(@forecast_model, NULL);
  Query OK, 0 rows affected (0.92 sec)
   
  mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.opsd_germany_daily_test', 
            @forecast_model, 'mlcorpus.opsd_germany_daily_train_predictions3', NULL);
  Query OK, 0 rows affected (2.79 sec)
   
  mysql> SELECT * FROM opsd_germany_daily_train_predictions3 LIMIT 5;
  +-----+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
  | _id | ddate      | consumption        | wind               | solar              | wind_solar         | ml_results                                                                       |
  +-----+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
  |   1 | 2015-12-30 | 1496.9310000000005 |  578.6919999999999 |             33.549 |            612.241 | {'predictions': {'consumption': 1274.5148683837483, 'wind': 272.6302044540897}}  |
  |   2 | 2015-12-31 |           1533.091 |  586.7679999999999 |             33.653 |            620.421 | {'predictions': {'consumption': 1369.0922461074088, 'wind': 245.13457844192516}} |
  |   3 | 2016-01-01 | 1521.9320000000002 |            385.009 | 44.772999999999996 |  429.7819999999999 | {'predictions': {'consumption': 1398.4766814573293, 'wind': 191.4273286304935}}  |
  |   4 | 2016-01-02 |           1518.605 | 283.66299999999995 |              47.09 | 330.75300000000004 | {'predictions': {'consumption': 1419.445174960485, 'wind': 110.31971564832713}}  |
  +-----+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
  4 rows in set (0.00 sec)
   
  mysql> CALL sys.ML_SCORE('mlcorpus.opsd_germany_daily_test', 'consumption', 
            @forecast_model, 'neg_sym_mean_abs_percent_error', @score);
  Query OK, 0 rows affected (2.62 sec)
   
  mysql> SELECT @score;
  +----------------------+
  | @score               |
  +----------------------+
  | -0.43969136476516724 |
  +----------------------+
  1 row in set (0.00 sec)

• As of MySQL 8.0.32, a forecasting example with multivariate endogenous_variables, exogenous_variables and include_column_list:

  mysql> CALL sys.ML_TRAIN('mlcorpus.opsd_germany_daily_train', 'consumption', 
            JSON_OBJECT('task', 'forecasting', 
            'datetime_index', 'ddate', 
            'endogenous_variables', JSON_ARRAY('consumption', 'wind'), 
            'exogenous_variables', JSON_ARRAY('solar'), 
            'include_column_list', JSON_ARRAY('solar')), 
            @forecast_model);
  Query OK, 0 rows affected (24.42 sec)
   
  mysql> CALL sys.ML_MODEL_LOAD(@forecast_model, NULL);
  Query OK, 0 rows affected (0.85 sec)
   
  mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.opsd_germany_daily_test', 
            @forecast_model, 'mlcorpus.opsd_germany_daily_train_predictions4', NULL);
  Query OK, 0 rows affected (3.12 sec)
   
  mysql> SELECT * FROM opsd_germany_daily_train_predictions4 LIMIT 5;
  +-----+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
  | _id | ddate      | consumption        | wind               | solar              | wind_solar         | ml_results                                                                       |
  +-----+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
  |   1 | 2015-12-30 | 1496.9310000000005 |  578.6919999999999 |             33.549 |            612.241 | {'predictions': {'consumption': 1295.020797044712, 'wind': 257.5489875255005}}   |
  |   2 | 2015-12-31 |           1533.091 |  586.7679999999999 |             33.653 |            620.421 | {'predictions': {'consumption': 1393.4633924459026, 'wind': 223.59810525502292}} |
  |   3 | 2016-01-01 | 1521.9320000000002 |            385.009 | 44.772999999999996 |  429.7819999999999 | {'predictions': {'consumption': 1419.49660442059, 'wind': 185.60024126155616}}   |
  |   4 | 2016-01-02 |           1518.605 | 283.66299999999995 |              47.09 | 330.75300000000004 | {'predictions': {'consumption': 1434.7619740666032, 'wind': 137.959271186024}}   |
  +-----+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
  4 rows in set (0.00 sec)
   
  mysql> CALL sys.ML_SCORE('mlcorpus.opsd_germany_daily_test', 'consumption', 
            @forecast_model, 'neg_sym_mean_abs_percent_error', @score);
  Query OK, 0 rows affected (2.73 sec)
   
  mysql> SELECT @score;
  +---------------------+
  | @score              |
  +---------------------+
  | -0.4276188611984253 |
  +---------------------+
  1 row in set (0.00 sec)