The ML.EXPLAIN_FORECAST function

ML.EXPLAIN_FORECAST function

The ML.EXPLAIN_FORECAST function generates forecasts based on a trained ARIMA_PLUS time-series model. It only works on ARIMA_PLUS models with the training option decompose_time_series enabled.

ML.EXPLAIN_FORECAST syntax

ML.EXPLAIN_FORECAST(MODEL model_name,
                   [, STRUCT<horizon INT64, confidence_level FLOAT64> settings])

model_name

model_name is the name of the model that you're using for forecasting. If you do not have a default project configured, then prepend the project ID to the model name in following format: `[project_id].[dataset].[model]` (including the backticks); for example, `myproject.mydataset.mymodel`.

horizon

(Optional) Horizon is the number of time points to forecast. The horizon value is type INT64 and is part of the settings STRUCT. The default value is 3, and the maximum value is the horizon value that's specified in the CREATE MODEL statement for time-series models, or 1000 if not specified. When forecasting multiple time series at the same time, this parameter applies to each time series.

confidence_level

(Optional) The percentage of the future values that fall in the prediction interval. The confidence_level value is type FLOAT64 and is part of the settings STRUCT. The default value is 0.95. The valid input range is \[0, 1).

ML.EXPLAIN_FORECAST output

The ML.EXPLAIN_FORECAST function returns the following columns:

  • time_series_id_col or time_series_id_cols: the identifiers of a time series. Only present when forecasting multiple time series at once. The column names and types are inherited from the TIME_SERIES_ID_COL option as specified in the model creation query.
  • time_series_timestamp (TIMESTAMP)
  • time_series_type (STRING)
  • time_series_data (FLOAT64)
  • time_series_adjusted_data (FLOAT64)
  • standard_error (FLOAT64)
  • confidence_level (FLOAT64)
  • prediction_interval_lower_bound (FLOAT64)
  • prediction_interval_upper_bound (FLOAT64)
  • trend (FLOAT64)
  • seasonal_period_yearly (FLOAT64)
  • seasonal_period_quarterly (FLOAT64)
  • seasonal_period_monthly (FLOAT64)
  • seasonal_period_weekly (FLOAT64)
  • seasonal_period_daily (FLOAT64)
  • holiday_effect (FLOAT64)
  • spikes_and_dips (FLOAT64)
  • step_changes (FLOAT64)

The output of the ML.EXPLAIN_FORECAST query has the following properties:

  • For each time series, the output rows are sorted in chronological order of time_series_timestamp.
  • time_series_timestamp has a type of TIMESTAMP, regardless of the type of the input time_series_timestamp_col.
  • time_series_type has a value of either history or forecast. Those rows with history in this column are used in training, either directly from the training table, or from interpolation using the training data.
  • For history type of rows, time_series_data is either the training data or the interpolated value using the training data. For forecast type of rows, time_series_data is the forecast value.
  • For history type of rows, time_series_adjusted_data is the cleaned data after cleaning spikes and dips and adjusting the step changes. It is the aggregation of all the valid components: holiday effect, seasonal components, and trend. For forecast type of rows, time_series_adjusted_data is the forecast value, which is the same as the value of time_series_data.
  • For history type of rows, the values of standard_error are the same, which is the standard error of an ARIMA fitting result. For forecast type of rows, the values of standard_error increase with time, as the forecast values are becoming less reliable.
  • confidence_level is the user-specified value or, if unspecified, the default value. It is the same for forecast type of rows and NULL for history type of rows.
  • Only forecast type of rows can have values other than NULL in columns prediction_interval_lower_bound and prediction_interval_upper_bound.
  • Only history type of rows can have values other than NULL in columns spikes_and_dips and step_changes.
  • The value in column seasonal_period_yearly, seasonal_period_quarterly, seasonal_period_monthly, seasonal_period_weekly, seasonal_period_daily, holiday_effect, spikes_and_dips, or step_changes is NULL if the corresponding component is not found in the training time series.

ML.EXPLAIN_FORECAST example

The following query uses ML.EXPLAIN_FORECAST to forecast 30 time points with a confidence level of 0.8.

SELECT
  *
FROM
  ML.EXPLAIN_FORECAST(MODEL `mydataset.mymodel`,
                      STRUCT(30 AS horizon, 0.8 AS confidence_level))