The AI.EVALUATE function

This document describes theAI.EVALUATE function, which lets youevaluateTimesFM forecasted data against areference time series based on historical data.

Syntax

SELECT  *FROM  AI.EVALUATE(    { TABLEHISTORY_TIME_SERIES_TABLE | (HISTORY_TIME_SERIES_QUERY_STATEMENT) },    { TABLEACTUAL_TIME_SERIES_TABLE | (ACTUAL_TIME_SERIES_QUERY_STATEMENT) },    data_col => 'DATA_COL',    timestamp_col => 'TIMESTAMP_COL',    [, model => 'MODEL']    [, id_cols =>ID_COLS]    [, horizon =>HORIZON]  )

Arguments

AI.EVALUATE takes the following arguments:

• HISTORY_TIME_SERIES_TABLE: the name of the table thatcontains historical time series data which is used to generate a forecast.For example,`mydataset.mytable`.The forecasted values are then evaluated against the data in theACTUAL_TIME_SERIES_TABLE orACTUAL_TIME_SERIES_QUERY_STATEMENT argument.

  If the table is in a different project, then you must prepend theproject ID to the table name in the following format, including backticks:

  `[PROJECT_ID].[DATASET].[TABLE]`

  For example,`myproject.mydataset.mytable`.

• HISTORY_TIME_SERIES_QUERY_STATEMENT: theGoogleSQL querythat generates historical time series data which is used to generate aforecast. The forecasted values are then evaluated against the data in theACTUAL_TIME_SERIES_TABLE orACTUAL_TIME_SERIES_QUERY_STATEMENTargument. See theGoogleSQL query syntaxpage for the supported SQL syntax of theHISTORY_TIME_SERIES_QUERY_STATEMENTclause.

• ACTUAL_TIME_SERIES_TABLE: the name of the table thatcontains the actual time series data. For example,`mydataset.mytable`. The data in this table is evaluatedagainst the forecasted values for the historical time series provided by theHISTORY_TIME_SERIES_TABLE orHISTORY_TIME_SERIES_QUERY_STATEMENTargument.

  If the table is in a different project, then you must prepend theproject ID to the table name in the following format, including backticks:

  `[PROJECT_ID].[DATASET].[TABLE]`

  For example,`myproject.mydataset.mytable`.

• ACTUAL_TIME_SERIES_QUERY_STATEMENT: the GoogleSQLquery that generates the actual time series data. The data from thisquery is evaluated against the forecasted values for the historical timeseries provided by theHISTORY_TIME_SERIES_TABLE orHISTORY_TIME_SERIES_QUERY_STATEMENT argument. See theGoogleSQL query syntaxpage for the supported SQL syntax of theACTUAL_TIME_SERIES_QUERY_STATEMENT clause.

• DATA_COL: aSTRING value that specifies the name ofthe time series data column. The data column must use one of the followingdata types:

  • INT64
  • NUMERIC
  • BIGNUMERIC
  • FLOAT64

• TIMESTAMP_COL: aSTRING value that specifies thename of the timestamp column. The timestamp column must use one ofthe following data types:

  • TIMESTAMP
  • DATE
  • DATETIME

• MODEL: aSTRING value that specifies the nameof the model to use. Supported models includeTimesFM 2.0 andTimesFM 2.5.The default value isTimesFM 2.0.

• ID_COLS: anARRAY<STRING> value that specifies thenames of one or more ID columns. Each unique combination of IDs identifies aunique time series to evaluate. Specify one or more values for this argumentin order to evaluate multiple time series using a single query. The columnsthat you specify must use one of the following data types:

  • STRING
  • INT64
  • ARRAY<STRING>
  • ARRAY<INT64>

• HORIZON: anINT64 value that specifies the number offorecasted time points to evaluate. The default value is1024. The validinput range is[1, 10,000].

Output

AI.EVALUATE returns the following columns:

• id_col orid_cols: the identifiers of a timeseries. Only present when evaluating multiple time series at once. Thecolumn names and types are inherited from theID_COLS argument.
• mean_absolute_error: aFLOAT64 value that contains themean absolute errorfor the time series.
• mean_squared_error: aFLOAT64 value that contains themean squared error forthe time series.
• root_mean_squared_error: aFLOAT64 value that contains theroot mean squared errorfor the time series.
• mean_absolute_percentage_error: aFLOAT64 value that contains themean absolute percentage errorfor the time series.
• symmetric_mean_absolute_percentage_error: aFLOAT64 value thatcontains thesymmetric mean absolute percentage errorfor the time series.
• ai_evaluate_status: aSTRING value that contains the evaluation status.The value is empty if the operation was successful. If the operation wasn'tsuccessful, the value is the error string. A common error isThe time series data is too short. This error indicates that there wasn'tenough historical data in the time series to generate forecasted data toevaluate. A minimum of 3 data points is required.

Examples

The following examples show how to use theAI.EVALUATE function.

Evaluate a single times series

The following example evaluates historical bike trips against actual bike tripsfor a single time series:

WITHcitibike_tripsAS(SELECTEXTRACT(DATEFROMstarttime)ASdate,COUNT(*)ASnum_tripsFROM`bigquery-public-data.new_york.citibike_trips`GROUPBYdate)SELECT*FROMAI.EVALUATE((SELECT*FROMcitibike_tripsWHEREdate <'2016-07-01'),(SELECT*FROMcitibike_tripsWHEREdate>='2016-07-01'),data_col=>'num_trips',timestamp_col=>'date');

The result is similar to the following:

+---------------------+--------------------+-------------------------+--------------------------------+------------------------------------------+--------------------+| mean_absolute_error | mean_squared_error | root_mean_squared_error | mean_absolute_percentage_error | symmetric_mean_absolute_percentage_error | ai_evaluate_status |+---------------------+--------------------+-------------------------+--------------------------------+------------------------------------------+--------------------+| 7512.2744140624982  | 88702684.834815472 | 9418.210277691589       | 16.068001108491149             | 15.740030591250889                       | null               |+---------------------+--------------------+-------------------------+--------------------------------+------------------------------------------+--------------------+

Evaluate multiple time series

The following example evaluates historical bike trips against actual bike tripsfor multiple time series:

WITH  citibike_trips AS (    SELECT EXTRACT(DATE FROM starttime) AS date, usertype, COUNT(*) AS num_trips    FROM `bigquery-public-data.new_york.citibike_trips`    GROUP BY date, usertype  )SELECT *FROM  AI.EVALUATE(    (SELECT * FROM citibike_trips WHERE date< '2016-07-01'),    (SELECT * FROM citibike_trips WHERE date >= '2016-07-01'),    data_col => 'num_trips',    timestamp_col => 'date',    id_cols => ['usertype']);

Locations

AI.EVALUATE and the TimesFM model are available in allsupported BigQuery ML locations.

Pricing

AI.EVALUATE usage is billed at the evaluation, inspection, and predictionrate documented in theBigQuery ML on-demand pricing sectionof theBigQuery ML pricing page.

What's next

• Tryusing a TimesFM model with theAI.FORECAST function.
• For information about forecasting in BigQuery ML, seeForecasting overview.
• For more information about supported SQL statements and functions fortime series forecasting models, seeEnd-to-end user journeys for time series forecasting models.