通过

ai_forecast 函数

  • 项目

适用于： 勾选“是” Databricks SQL

重要

此功能处于公共预览阶段。 请联系你的 Databricks 帐户团队以参与预览版。

ai_forecast() 是一个表值函数 (TVF)，旨在推断未来的时间序列数据。 有关配置此函数的可用参数，请参阅参数。

要求

  • 仅适用于运行 Databricks SQL 版本 2024.35 及更高版本的 Pro SQL 仓库。
  • 查看 Databricks SQL 定价页。

语法


ai_forecast(
  observed TABLE,
  horizon DATE | TIMESTAMP | STRING,
  time_col STRING,
  value_col STRING | ARRAY<STRING>,
  group_col STRING | ARRAY<STRING> | NULL DEFAULT NULL,
  prediction_interval_width DOUBLE DEFAULT 0.95,
  frequency STRING DEFAULT 'auto',
  seed INTEGER | NULL DEFAULT NULL,
  parameters STRING DEFAULT '{}'
)

参数

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

以下是此函数的可用参数：

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

返回

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

以下是 AI_FORECAST 执行的架构推理的一些示例：

输入表 参数 输出表
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

示例

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


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

请注意，表不实现 0 或空条目的情况很常见。 如果可以推断出缺失条目的值（例如 0），则应在调用预测函数之前合并这些值。 如果这些值确实缺失或未知，则可以将其保留为 NULL。

对于非常稀疏的数据，最佳做法是合并缺失值或显式提供频率值，以避免“自动”频率推理产生意外输出。 例如，对相隔 14 天的两个条目进行“自动”频率推理将推断出频率为“14D”，即使“实际”频率可能是每周一次且有 1 个缺失值。 合并缺少的条目会消除这种歧义。

最后，我们将演示一个示例，其中不同的预测参数被应用于输入表中的不同组：

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

需要注意的是列标识符用作了 parameters 参数。 这样用户可以将之前确定的参数 JSON 存储在表中，并在新数据上重用它们。

限制

预览期间存在以下限制：

  • 默认预测过程是一个类似 prophet 的分段线性和季节性模型。 这是唯一可用的受支持的预测过程。
  • 错误消息通过 Python UDTF 引擎传递，并包含 Python 回溯信息。 回溯的末尾包含实际错误消息。