Documentation Home
HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 2.0Mb
PDF (A4) - 2.0Mb


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)