HeatWave Release Notes
To produce a forecast, run the
ML_PREDICT_TABLE routine on
data with the same columns as the training model. The column
datetime_index 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 return table.
As of MySQL 8.4.0, prediction interval values are included in the prediction results. See Section 3.9.4, “Prediction Intervals” to learn more.
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. However, the target
column needs to be in the table to generate a valid score
value.
ML_PREDICT_ROW cannot be used
with forecasting models.
-
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', NULL); Query OK, 0 rows affected (1.50 sec) mysql> SELECT * FROM opsd_germany_daily_train_predictions1 LIMIT 5; +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ | _4aad19ca6e_pk_id | ddate | wind | solar | wind_solar | ml_results | +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2015-12-30 | 578.6919999999999 | 33.549 | 612.241 | {"predictions": {"consumption": 1513.6389999965475, "prediction_interval_consumption": [1303.3105599749329, 1723.9674400181623]}} | | 2 | 2015-12-31 | 586.7679999999999 | 33.653 | 620.421 | {"predictions": {"consumption": 1505.79100000697, "prediction_interval_consumption": [1295.4625599853553, 1716.1194400285847]}} | | 3 | 2016-01-01 | 385.009 | 44.772999999999996 | 429.7819999999999 | {"predictions": {"consumption": 1482.989000000031, "prediction_interval_consumption": [1272.6605599784164, 1693.3174400216458]}} | | 4 | 2016-01-02 | 283.66299999999995 | 47.09 | 330.75300000000004 | {"predictions": {"consumption": 1237.6239999992706, "prediction_interval_consumption": [1027.2955599776558, 1447.9524400208852]}} | +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ 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, NULL); 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_variablesandexogenous_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 | wind | solar | wind_solar | ml_results | +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2015-12-30 | 578.6919999999999 | 33.549 | 612.241 | {"predictions": {"consumption": 1513.6389999965475, "prediction_interval_consumption": [1303.3105599749329, 1723.9674400181623]}} | | 2 | 2015-12-31 | 586.7679999999999 | 33.653 | 620.421 | {"predictions": {"consumption": 1505.79100000697, "prediction_interval_consumption": [1295.4625599853553, 1716.1194400285847]}} | | 3 | 2016-01-01 | 385.009 | 44.772999999999996 | 429.7819999999999 | {"predictions": {"consumption": 1482.989000000031, "prediction_interval_consumption": [1272.6605599784164, 1693.3174400216458]}} | | 4 | 2016-01-02 | 283.66299999999995 | 47.09 | 330.75300000000004 | {"predictions": {"consumption": 1237.6239999992706, "prediction_interval_consumption": [1027.2955599776558, 1447.9524400208852]}} | +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ 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, NULL); 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_variablesandexogenous_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 | solar | wind_solar | ml_results | +-------------------+------------+--------------------+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2015-12-30 | 33.549 | 612.241 | {"predictions": {"consumption": 1274.514842248098, "wind": 272.63032046590695, "prediction_interval_consumption": [1130.6445275384583, 1418.3851569577375], "prediction_interval_wind": [171.3323796244025, 373.9282613074114]}} | | 2 | 2015-12-31 | 33.653 | 620.421 | {"predictions": {"consumption": 1369.092198011085, "wind": 245.1348248348579, "prediction_interval_consumption": [1200.6778960524775, 1537.5064999696924], "prediction_interval_wind": [106.09618981901079, 384.173459850705]}} | | 3 | 2016-01-01 | 44.772999999999996 | 429.7819999999999 | {"predictions": {"consumption": 1398.4766547816705, "wind": 191.42752338956123, "prediction_interval_consumption": [1214.0129021543537, 1582.9404074089873], "prediction_interval_wind": [48.74260887154139, 334.11243790758107]}} | | 4 | 2016-01-02 | 47.09 | 330.75300000000004 | {"predictions": {"consumption": 1419.4451594574473, "wind": 110.31967475663569, "prediction_interval_consumption": [1227.1056397359446, 1611.78467917895], "prediction_interval_wind": [-41.06355141895449, 261.7029009322259]}} | +-------------------+------------+--------------------+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 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, NULL); 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_variablesandinclude_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 | solar | wind_solar | ml_results | +-------------------+------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2015-12-30 | 33.549 | 612.241 | {"predictions": {"consumption": 1295.0208145972483, "wind": 257.5489706531487, "prediction_interval_consumption": [1152.1353221265401, 1437.9063070679567], "prediction_interval_wind": [153.91577001736468, 361.1821712889327]}} | | 2 | 2015-12-31 | 33.653 | 620.421 | {"predictions": {"consumption": 1393.4634314093155, "wind": 223.59806792105013, "prediction_interval_consumption": [1222.490318255932, 1564.4365445626993], "prediction_interval_wind": [83.77220617509019, 363.4239296670101]}} | | 3 | 2016-01-01 | 44.772999999999996 | 429.7819999999999 | {"predictions": {"consumption": 1419.49663923103, "wind": 185.60020827028058, "prediction_interval_consumption": [1233.5178144122572, 1605.4754640498027], "prediction_interval_wind": [41.36718047286506, 329.83323606769613]}} | | 4 | 2016-01-02 | 47.09 | 330.75300000000004 | {"predictions": {"consumption": 1434.761998553746, "wind": 137.95925414558872, "prediction_interval_consumption": [1239.695545974048, 1629.828451133444], "prediction_interval_wind": [-13.464716658503704, 289.3832249496811]}} | +-------------------+------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.opsd_germany_daily_test', 'consumption', @forecast_model2, 'neg_sym_mean_abs_percent_error', @score, NULL); Query OK, 0 rows affected (2.73 sec) mysql> SELECT @score; +---------------------+ | @score | +---------------------+ | -0.4276188611984253 | +---------------------+ 1 row in set (0.00 sec) -
A forecasting example that uses the
OrbitForecastermodel with one endogenous variable and one exogenous variable.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('solar'), 'model_list', JSON_ARRAY('OrbitForecaster')), @forecast_model2); Query OK, 0 rows affected (1 min 30.0743 sec) mysql> CALL sys.ML_MODEL_LOAD(@forecast_model2, NULL); Query OK, 0 rows affected (0.7906 sec) mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.opsd_germany_daily_test', @forecast_model2, 'mlcorpus.opsd_germany_daily_train_predictions', NULL); Query OK, 0 rows affected (1.9642 sec) mysql> SELECT * FROM opsd_germany_daily_train_predictions LIMIT 5; +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ | _4aad19ca6e_pk_id | ddate | wind | solar | wind_solar | ml_results | +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2015-12-30 | 578.6919999999999 | 33.549 | 612.241 | {"predictions": {"consumption": 1528.4492998993346, "prediction_interval_consumption": [1146.2846384873137, 1677.5791573779006]}} | | 2 | 2015-12-31 | 586.7679999999999 | 33.653 | 620.421 | {"predictions": {"consumption": 1522.6483844371946, "prediction_interval_consumption": [1258.7368362771717, 1678.3974153123088]}} | | 3 | 2016-01-01 | 385.009 | 44.772999999999996 | 429.7819999999999 | {"predictions": {"consumption": 1512.170787041866, "prediction_interval_consumption": [1249.1245643026623, 1608.452886671843]}} | | 4 | 2016-01-02 | 283.66299999999995 | 47.09 | 330.75300000000004 | {"predictions": {"consumption": 1272.6567578456688, "prediction_interval_consumption": [879.3696014867011, 1408.2583498648676]}} | +-------------------+------------+--------------------+--------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.0300 sec) mysql> CALL sys.ML_SCORE('mlcorpus.opsd_germany_daily_test', 'consumption', @forecast_model2, 'neg_sym_mean_abs_percent_error', @score, NULL); Query OK, 0 rows affected (1.8235 sec) mysql> SELECT @score; +----------------------+ | @score | +----------------------+ | -0.05177544429898262 | +----------------------+ 1 row in set (0.0594 sec)