ai_forecast function

Applies to: Databricks SQL

ai_forecast() is a table-valued function that extrapolates time series data forward in time. See Arguments for available arguments to configure this function.

The function has two versions. A research-optimized time series foundation model powers Version 2 for improved out-of-the-box accuracy, and Version 2 adds support for holidays, external covariates, and non-negative forecasts. Use the version argument to select which version runs. See Arguments for details.

Requirements

• Pro or Serverless SQL warehouse

Syntax

Version 1

ai_forecast(observed, horizon, time_col, value_col
  [, group_col] [, prediction_interval_width] [, frequency]
  [, seed] [, parameters] [, version])

Arguments

ai_forecast() can forecast any number of groups (see group_col) and up to 100 metrics (see value_col) within each group. The forecast frequency is the same for all metrics in a group but can differ across groups (see frequency).

Version 1

• observed is the table-valued input that is used as training data for the forecasting procedure.
  • This input relation must contain one "time" column and one or more "value" columns. "Group" and "parameters" columns are optional. Any additional columns in the input relation are ignored.
• horizon is a timestamp-castable quantity representing the right-exclusive end time of the forecasting results. Within a group (see group_col) forecast results span the time between the last observation and horizon. If horizon is less than the last observation time, then no results are generated.
• time_col is a string referencing the "time column" in observed. The column referenced by time_col must be a DATE or a TIMESTAMP.
• value_col is a string or an array of strings referencing value columns in observed. The columns referenced by this argument must be castable to DOUBLE.
• group_col (optional) is a string or an array of strings representing the group columns in observed. If specified, group columns are used as partitioning criteria, and forecasts are generated for each group independently. If unspecified, the full input data is treated as a single group.
• prediction_interval_width (optional) is a value between 0 and 1 representing the width of the prediction interval. Forecasted values have a prediction_interval_width % probability of falling between {v}_upper and {v}_lower.
• frequency (optional) is a time unit or pandas offset alias string specifying the time granularity of the forecast results. If unspecified, the forecast granularity is automatically inferred for each group independently. If a frequency value is specified, it is applied equally to all groups.
  • The inferred frequency within a group is the mode of the most recent observations. This inference is a convenience operation that is not tunable by the user.
  • For example, a time series with 99 "mondays" and 1 "tuesday" results in the "week" being the inferred frequency.
• seed (optional) is a number used to start any pseudorandom number generators used in the forecasting procedure.
• parameters (optional) is a string-encoded JSON or the name of a column identifier that represents the parameterization of the forecasting procedure. Any combination of parameters can be specified in any order, for example, {"weekly_order": 10, "global_cap": 1000}. Any unspecified parameters are automatically determined based on the attributes of the training data. The following parameters are supported:
  • global_cap and global_floor can be used together or independently to define the possible domain of the metric values. {"global_floor": 0}, for example, can be used to constrain a metric like cost to always be positive. These constraints apply globally to the training data and the forecasted data, and can not be used to provide tight constraints on the forecasted values only.
  • daily_order and weekly_order set the fourier order of the daily and weekly seasonality components.
• version (optional): Version switch to support migration ('1' for version 1 behavior, '2' for version 2 behavior). If unspecified, defaults to version 1. The version 2 arguments (covariate_col, holiday_region, positive_only) require version => '2'.

Returns

Version 1

A new set of rows containing the forecasted data. The output schema contains the time and group columns with their types unchanged. For example, if the input time column has type DATE, then the output time column type is also DATE. For each value column there are three output columns with the pattern {v}_forecast, {v}_upper, and {v}_lower. Regardless of the input value types, the forecasted value columns are always type DOUBLE. The output table contains forecasted values only, spanning the range of time between the end of the observed data until horizon.

The following table shows some examples of the schema inference performed by AI_FORECAST:

Examples

Version 1

The following example forecasts until a specified date:

WITH
aggregated AS (
  SELECT
    DATE(tpep_pickup_datetime) AS ds,
    SUM(fare_amount) AS revenue
  FROM
    samples.nyctaxi.trips
  GROUP BY
    1
)
SELECT * FROM AI_FORECAST(
  TABLE(aggregated),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => 'revenue'
)

The following is a more complex example:

WITH
aggregated AS (
  SELECT
    DATE(tpep_pickup_datetime) AS ds,
    dropoff_zip,
    SUM(fare_amount) AS revenue,
    COUNT(*) AS n_trips
  FROM
    samples.nyctaxi.trips
  GROUP BY
    1, 2
),
spine AS (
  SELECT all_dates.ds, all_zipcodes.dropoff_zip
  FROM (SELECT DISTINCT ds FROM aggregated) all_dates
  CROSS JOIN (SELECT DISTINCT dropoff_zip FROM aggregated) all_zipcodes
)
SELECT * FROM AI_FORECAST(
  TABLE(
    SELECT
      spine.*,
      COALESCE(aggregated.revenue, 0) AS revenue,
      COALESCE(aggregated.n_trips, 0) AS n_trips
    FROM spine LEFT JOIN aggregated USING (ds, dropoff_zip)
  ),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => ARRAY('revenue', 'n_trips'),
  group_col => 'dropoff_zip',
  prediction_interval_width => 0.9,
  parameters => '{"global_floor": 0}'
)

For sparse data, it is best practice to coalesce missing values or provide a frequency value explicitly to avoid unexpected output from the "auto" frequency inference. For example, "auto" frequency inference on two entries 14 days apart infers a frequency of "14D" even if the "real" frequency might be weekly with 1 missing value. Coalescing the missing entries removes this ambiguity.

The following example shows how different forecast parameters are applied to different groups in the input table. The example uses the parameters argument as a column identifier. This approach enables users to store previously-determined parameter JSONs in a table and reuse them on new data.

WITH past AS (
  SELECT
    CASE
      WHEN fare_amount < 30 THEN 'Under $30'
      ELSE '$30 or more'
    END AS revenue_bucket,
    CASE
      WHEN fare_amount < 30 THEN '{"daily_order": 0}'
      ELSE '{"daily_order": "auto"}'
    END AS parameters,
    DATE(tpep_pickup_datetime) AS ds,
    SUM(fare_amount) AS revenue
  FROM samples.nyctaxi.trips
  GROUP BY ALL
)
SELECT * FROM AI_FORECAST(
  TABLE(past),
  horizon => (SELECT MAX(ds) + INTERVAL 30 DAYS FROM past),
  time_col => 'ds',
  value_col => 'revenue',
  group_col => ARRAY('revenue_bucket'),
  parameters => 'parameters'
)

Limitations

Version 1

The following limitations apply during the Public Preview:

• Version 1 is in Public Preview and is the default version..
• Version 1 is on a deprecation path. To keep using version 1 behavior after the default changes, pin it by setting version => '1'.
• The default forecasting procedure is a prophet-like piecewise linear and seasonality model. This model is the only supported forecasting procedure available.
• Error messages are delivered through the Python UDTF engine, and contain Python trace back information. The end of the trace back contains the actual error message.
• Each invocation of ai_forecast performs an independent quantile regression. If you call ai_forecast multiple times with different prediction_interval_width values to produce nested prediction intervals, the resulting intervals are not guaranteed to be properly nested because the quantiles are computed independently across calls, with no constraint to verify proper ordering. To compare prediction intervals, use a single ai_forecast call with one prediction_interval_width value.