MySQL :: HeatWave User Guide :: 3.9.2 Using a Forecasting Model

HeatWave User Guide / ... / Using a Forecasting Model

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.14.6, “Scoring Models”. For the complete list of option descriptions, see ML_PREDICT_TABLE and ML_SCORE.

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:

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)

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;
+-------------------+------------+--------------------+--------------------+--------------------+--------------------+------------------------------------------------------+
| _4aad19ca6e_pk_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)

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;
+-------------------+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
| _4aad19ca6e_pk_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)

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;
+-------------------+------------+--------------------+--------------------+--------------------+--------------------+----------------------------------------------------------------------------------+
| _4aad19ca6e_pk_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)

PREV HOME UP NEXT