ai_forecast 函数

适用于：勾选“是” Databricks SQL

Important

此函数的版本 1 为 公共预览版。 版本 2（建议）处于 Beta 版。

ai_forecast() 是一个表值函数，用于推断时序数据向前。 有关配置此函数的可用参数，请参阅参数。

该函数有两个版本。 经过研究优化的时序基础模型为版本 2 提供支持，以提高现成的准确性，版本 2 增加了对假日、外部协变量和非负预测的支持。 使用 version 参数选择哪个版本运行。 有关详细信息 ，请参阅参数 。

要求

  • Pro 或无服务器 SQL 仓库
  • 在 预测 AI Functions 预览版中注册工作区（建议的版本 2 是必需的）。 请参阅 Manage Azure Databricks 预览版。

Syntax

Tip

Azure Databricks建议使用版本 2。ai_forecast 版本 2 对版本 1 提供以下改进：

  • 一种经过研究优化的时序基础模型，用于改进现时的准确度
  • 内置假日支持 holiday_region
  • 外部协变量，包括未来和仅过去同变量，以及 covariate_col
  • 包含的非负预测 positive_only

若要使用版本 2，请传递 version => '2'。 确保已启用 预测 AI 函数 预览版。 请参阅 Manage Azure Databricks 预览版。

ai_forecast(observed, horizon, time_col, value_col
  [, group_col] [, covariate_col] [, prediction_interval_width]
  [, frequency] [, holiday_region] [, positive_only] [, version])

版本 1

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

Arguments

ai_forecast() 可以预测任意数量的组（请参阅 group_col）和每个组中最多 100 个指标（请参阅 value_col）。 对于组中的所有指标，预测频率相同，但可以不同组（请参阅 frequency）。

  • observed 是用作预测过程训练数据的表值输入。
    • 此输入关系必须包含一个“时间”列和一个或多个“值”列。 “Group”和“covariate”列是可选的。 将忽略输入关系中的其他任何列。
  • horizon 是一个可转换为时间戳的数量，表示预测结果的唯一正确结束时间。 在一个组中（参阅 group_col），预测结果的时间范围是从上次观察到边际。 如果边际小于上次观察时间，则不会生成任何结果。
  • time_col 是引用 “时间列” 的 observed字符串。 引用的 time_col 列必须是一个或一个 DATETIMESTAMP。
  • value_col 是引用 observed 中值列的字符串或字符串数组。 此参数引用的列必须可 DOUBLE强制转换为 。
  • group_col（可选）是表示 observed 中组列的字符串或字符串数组。 如果已指定，组列将用作分区条件，并且会单独为每个组生成预测。 如果未指定，则完整输入数据将视为单个组。
  • covariate_col （可选）是引用外部协变量列的 observed字符串或字符串数组。 共同变量是影响预测的其他变量，例如价格、营销支出或天气。 支持两种类型的协变量：
    • 未来的共同变量：值以预测范围而闻名，例如计划定价或计划活动。 若要以这种方式使用协变量，请在该行中包含 observed 覆盖预测地平线的行（最后一次观察后的日期，最多 horizon），其中填充了同变量， value_col 并且列（s）左 NULL。 ai_forecast 预测这些 NULL-target 行及其二元值的预测条件。
    • 过去唯一的协变量：值仅在历史时期已知，例如观测到的天气或宏观经济指标。 仅填充历史行上的共变量。 如果不在预测范围内提供协变量值， ai_forecast 请使用协变量作为仅过去值;这不是错误。
  • prediction_interval_width（可选）是一个介于 0 和 1 之间的值，表示预测间隔的宽度。 预测值 % prediction_interval_width 介于和{v}_upper之间{v}_lower。
  • frequency （可选）是 pandas 偏移别名字符串（例如， 'D'， 'W'， 'ME'） 'H'指定预测结果的时间粒度。 如果未指定，则会自动为每个组单独推断预测粒度。 如果指定，则它必须与每个组中输入数据的推断粒度匹配。
    • 组内推断的频率是最近观察的模式。 此推理是用户无法调整的便利操作。
    • 例如，具有 99 个“mondays”和 1 个“monday”的时序会导致“week”是推断的频率。
  • holiday_region （可选）是一个区域代码，用于为该区域启用假日效果的自动建模，例如 'US'。 如果未指定，则不会对假日效果进行建模。
  • positive_only （可选）设置为 TRUE时，将预测的值限制为非负值。 将此参数用于不能为负的指标，例如销售额、计数或库存。 默认值为 FALSE。
  • version （可选）：版本切换以支持迁移（'1' 对于版本 1 行为， '2' 对于版本 2 行为）。 如果未指定，则默认为版本 1。 版本 2 参数 （covariate_col， ， holiday_regionpositive_only） 需要version => '2'。

版本 1

  • observed 是用作预测过程训练数据的表值输入。
    • 此输入关系必须包含一个“时间”列和一个或多个“值”列。 “Group”和“parameters”列是可选的。 将忽略输入关系中的其他任何列。
  • horizon 是一个可转换为时间戳的数量，表示预测结果的唯一正确结束时间。 在一个组中（参阅 group_col），预测结果的时间范围是从上次观察到边际。 如果边际小于上次观察时间，则不会生成任何结果。
  • time_col 是引用 “时间列” 的 observed字符串。 引用的 time_col 列必须是一个或一个 DATETIMESTAMP。
  • value_col 是引用 observed 中值列的字符串或字符串数组。 此参数引用的列必须可 DOUBLE强制转换为 。
  • group_col（可选）是表示 observed 中组列的字符串或字符串数组。 如果已指定，组列将用作分区条件，并且会单独为每个组生成预测。 如果未指定，则完整输入数据将视为单个组。
  • prediction_interval_width（可选）是一个介于 0 和 1 之间的值，表示预测间隔的宽度。 预测值 % prediction_interval_width 介于和{v}_upper之间{v}_lower。
  • frequency（可选）是用于指定预测结果的时间粒度的时间单位或 pandas 偏移别名字符串。 如果未指定，则会自动为每个组单独推断预测粒度。 如果指定了频率值，则会将其统一应用于所有组。
    • 组内推断的频率是最近观察的模式。 此推理是用户无法调整的便利操作。
    • 例如，具有 99 个“mondays”和 1 个“monday”的时序会导致“week”是推断的频率。
  • seed （可选）是用于启动预测过程中使用的任何伪随机数生成器的数字。
  • parameters（可选）是字符串编码的 JSON 或表示预测过程参数化的列标识符的名称。 可以按任意顺序指定参数的任意组合，例如 {"weekly_order": 10, "global_cap": 1000}。 任何未指定的参数都会根据训练数据的属性自动确定。 支持以下参数：
    • global_cap 与 global_floor 可以一起使用也可以单独使用来定义指标值的可能域。 例如，{"global_floor": 0} 可用于将成本等指标始终限制为正。 这些约束全局应用于训练数据和预测数据，不能用于仅对预测值提供严格约束。
    • daily_order 和 weekly_order 会设置每日和每周季节性分量的傅立叶阶数。
  • version （可选）：版本切换以支持迁移（'1' 对于版本 1 行为， '2' 对于版本 2 行为）。 如果未指定，则默认为版本 1。 版本 2 参数 （covariate_col， ， holiday_regionpositive_only） 需要version => '2'。

Returns

包含预测数据的新行集。 输出架构包含其类型不变的时间列和分组列。 例如，如果输入时间列具有类型 DATE，则输出时间列类型也是 DATE。 每个值列都有三个输出列，其模式为 {v}_forecast、{v}_upper 和 {v}_lower。 无论输入值类型如何，预测的值列始终为类型 DOUBLE。 输出表仅包含预测的值，跨越观察数据结束到地平线之间的时间范围。

下表显示了AI_FORECAST执行的架构推理的一些示例：

输入表 Arguments 输出表
ts: TIMESTAMP
val: DOUBLE		 time_col => 'ts'
value_col => 'val'		 ts: TIMESTAMP
val_forecast: DOUBLE
val_upper: DOUBLE
val_lower: DOUBLE
ds: DATE
val BIGINT		 time_col => 'ds'
value_col => 'val'		 ds: DATE
val_forecast: DOUBLE
val_upper: DOUBLE
val_lower: DOUBLE
ts: TIMESTAMP
dim1: STRING
dollars: DECIMAL(10, 2)		 time_col => 'ts'
value_col => 'dollars'
group_col => 'dim1'		 ts: TIMESTAMP
dim1: STRING
dollars_forecast: DOUBLE
dollars_upper: DOUBLE
dollars_lower: DOUBLE
ts: TIMESTAMP
dim1: STRING
dim2: BIGINT
dollars: DECIMAL(10, 2)
users: BIGINT		 time_col => 'ts'
value_col => ARRAY('dollars', 'users')
group_col => ARRAY('dim1', 'dim2')		 ts: TIMESTAMP
dim1: STRING
dim2: BIGINT
dollars_forecast: DOUBLE
dollars_upper: DOUBLE
dollars_lower: DOUBLE
users_forecast: DOUBLE
users_upper: DOUBLE
users_lower: DOUBLE

版本 1

包含预测数据的新行集。 输出架构包含其类型不变的时间列和分组列。 例如，如果输入时间列具有类型 DATE，则输出时间列类型也是 DATE。 每个值列都有三个输出列，其模式为 {v}_forecast、{v}_upper 和 {v}_lower。 无论输入值类型如何，预测的值列始终为类型 DOUBLE。 输出表仅包含预测的值，跨越观察数据结束到地平线之间的时间范围。

下表显示了AI_FORECAST执行的架构推理的一些示例：

输入表 Arguments 输出表
ts: TIMESTAMP
val: DOUBLE		 time_col => 'ts'
value_col => 'val'		 ts: TIMESTAMP
val_forecast: DOUBLE
val_upper: DOUBLE
val_lower: DOUBLE
ds: DATE
val BIGINT		 time_col => 'ds'
value_col => 'val'		 ds: DATE
val_forecast: DOUBLE
val_upper: DOUBLE
val_lower: DOUBLE
ts: TIMESTAMP
dim1: STRING
dollars: DECIMAL(10, 2)		 time_col => 'ts'
value_col => 'dollars'
group_col => 'dim1'		 ts: TIMESTAMP
dim1: STRING
dollars_forecast: DOUBLE
dollars_upper: DOUBLE
dollars_lower: DOUBLE
ts: TIMESTAMP
dim1: STRING
dim2: BIGINT
dollars: DECIMAL(10, 2)
users: BIGINT		 time_col => 'ts'
value_col => ARRAY('dollars', 'users')
group_col => ARRAY('dim1', 'dim2')		 ts: TIMESTAMP
dim1: STRING
dim2: BIGINT
dollars_forecast: DOUBLE
dollars_upper: DOUBLE
dollars_lower: DOUBLE
users_forecast: DOUBLE
users_upper: DOUBLE
users_lower: DOUBLE

Examples

以下示例使用版本 2 预测到指定日期：


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',
  version => '2'
)

以下示例为美国的假日效果建模，并将预测限制为非负值：


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',
  holiday_region => 'US',
  positive_only => true,
  version => '2'
)

以下示例使用外部协变量。 该 observed 表（daily_sales） 包括 promotion 为历史时期和预测地平线填充的列，在 revenue 预测地平线行上保留 NULL ，因此 promotion 用作将来的协变量：


SELECT * FROM AI_FORECAST(
  TABLE(daily_sales),
  horizon => '2016-03-31',
  time_col => 'ds',
  value_col => 'revenue',
  covariate_col => 'promotion',
  version => '2'
)

版本 1

以下示例预测截止指定日期之前的情况：


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'
)

下面是一个更复杂的示例：


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}'
)

注释

ai_forecast 不会为表中的缺失条目或 NULL 条目填充 0。 如果可以推断缺失项的正确值，则必须在调用 ai_forecast 函数之前合并它们。 如果这些值确实缺失或未知，则可以将值保留为 NULL 或删除它们。

对于稀疏数据，最佳做法是合并缺失值或显式提供频率值以避免“自动”频率推理的意外输出。 例如，在 14 天内，两个条目上的“自动”频率推理推断频率为“14D”，即使“real”频率可能每周有 1 个缺失值。 合并缺少的条目会消除这种歧义。

以下示例演示如何将不同的预测参数应用于输入表中的不同组。 该示例使用 parameters 参数作为列标识符。 此方法使用户能够将以前确定的参数 JSON 存储在表中，并在新数据上重复使用它们。

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'
)

局限性

Beta 版期间存在以下限制：

  • 版本 2 处于 Beta 版，不是默认值。 若要使用版本 2，请通过设置 version => '2'选择加入。 版本 1（以公共预览版提供）保持默认值。
  • 默认预测过程是时序基础模型。 此模型是唯一受支持的预测过程。
  • 错误消息通过 Python UDTF 引擎传递，并包含 Python 回溯信息。 回溯的末尾包含实际错误消息。
  • 每次调用 ai_forecast 执行独立的推理。 如果使用不同ai_forecast值多次调用prediction_interval_width以生成嵌套预测间隔，则不保证生成的间隔正确嵌套。 若要比较预测间隔，请使用单个 ai_forecast 调用和一个 prediction_interval_width 值。

版本 1

公共预览版期间存在以下限制：

  • 版本 1 为公共预览版，是默认版本。 版本 2（在 Beta 版中）通过设置 version => '2'提供。
  • 版本 1 位于弃用路径上。 在即将发布的版本中，默认版本更改为版本 2，并且版本 1 已弃用。 若要在默认更改后继续使用版本 1 行为，请通过设置 version => '1'固定它。
  • 默认预测过程是一个类似 prophet 的分段线性和季节性模型。 此模型是唯一受支持的预测过程。
  • 错误消息通过 Python UDTF 引擎传递，并包含 Python 回溯信息。 回溯的末尾包含实际错误消息。
  • 每次调用 ai_forecast 执行独立的分位回归。 如果多次调用 ai_forecast 具有不同 prediction_interval_width 值来生成嵌套预测间隔，则无法保证正确嵌套生成的间隔，因为分位数是跨调用独立计算的，没有限制来验证正确的排序。 若要比较预测间隔，请使用单个 ai_forecast 调用和一个 prediction_interval_width 值。
  • 上次更新时间为