利用 BigQuery 中的数百万个时序进行可伸缩预测

---

在本教程中，您将学习如何显著加快一组时序模型的训练速度，以通过单个查询执行多个时序预测。您还将了解如何评估预测准确率。

在除最后一步之外的所有步骤中，您将使用 new_york.citibike_trips 数据。此数据包含有关纽约市花旗单车行程的信息。此数据集仅包含数百个时序。它用于说明加快模型训练速度的各种策略。在最后一步中，您将使用 iowa_liquor_sales.sales 数据来预测超过一百万个时序。

在阅读本教程之前，您应该阅读使用纽约市花旗单车行程数据的单一查询进行多时序预测。您还应该阅读大规模时序预测最佳实践。

目标

在本教程中，您将使用以下内容：

• CREATE MODEL 语句：创建一个时序模型或一组时序模型。
• ML.EVALUATE 函数：评估预测准确率。
• AUTO_ARIMA_MAX_ORDER、TIME_SERIES_LENGTH_FRACTION、MIN_TIME_SERIES_LENGTH 和 MAX_TIME_SERIES_LENGTH 训练选项：显著缩短模型训练时间。

为简单起见，本教程未介绍如何使用 ML.FORECAST 或 ML.EXPLAIN_FORECAST 生成（可解释的）预测。如需了解如何使用这些函数，请参阅使用纽约市花旗单车行程数据的单一查询进行多时序预测。

费用

本教程使用 Google Cloud 的以下收费组件：

• BigQuery
• BigQuery ML

如需详细了解费用，请参阅 BigQuery 价格页面和 BigQuery ML 价格页面。

准备工作

1. 登录您的 Google Cloud 账号。如果您是 Google Cloud 新手，请创建一个账号来评估我们的产品在实际场景中的表现。新客户还可获享 $300 赠金，用于运行、测试和部署工作负载。

2. 在 Google Cloud Console 中的项目选择器页面上，选择或创建一个 Google Cloud 项目。

   注意：如果您不打算保留在此过程中创建的资源，请创建新的项目，而不要选择现有的项目。完成本教程介绍的步骤后，您可以删除所创建的项目，并移除与该项目关联的所有资源。

   转到“项目选择器”

3. 确保您的 Google Cloud 项目已启用结算功能。

4. 在 Google Cloud Console 中的项目选择器页面上，选择或创建一个 Google Cloud 项目。

   注意：如果您不打算保留在此过程中创建的资源，请创建新的项目，而不要选择现有的项目。完成本教程介绍的步骤后，您可以删除所创建的项目，并移除与该项目关联的所有资源。

   转到“项目选择器”

5. 确保您的 Google Cloud 项目已启用结算功能。

6. 新项目会自动启用 BigQuery。如需在现有项目中激活 BigQuery，请转到

   启用 BigQuery API。

   启用 API

第一步：创建数据集

创建 BigQuery 数据集以存储您的机器学习模型：

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery 页面

2. 在探索器窗格中，点击您的项目名称。

3. 点击 查看操作 > 创建数据集。

   

4. 在创建数据集页面上，执行以下操作：

   • 在数据集 ID 部分，输入 bqml_tutorial。

   • 在位置类型部分，选择多区域，然后选择 US (multiple regions in United States)（美国[美国的多个区域]）。

     公共数据集存储在 US 多区域中。为简单起见，请将数据集存储在同一位置。

   • 保持其余默认设置不变，然后点击创建数据集。

     

第二步：创建要预测的时序

在以下查询中，FROM bigquery-public-data.new_york.citibike_trips 子句指示您正在查询 new_york 数据集中的 citibike_trips 表。

CREATE OR REPLACE TABLE
  `bqml_tutorial.nyc_citibike_time_series` AS
WITH input_time_series AS
(
  SELECT
    start_station_name,
    EXTRACT(DATE FROM starttime) AS date,
    COUNT(*) AS num_trips
  FROM
    `bigquery-public-data.new_york.citibike_trips`
  GROUP BY
    start_station_name, date
)
SELECT table_1.*
FROM input_time_series AS table_1
INNER JOIN (
  SELECT start_station_name,  COUNT(*) AS num_points
  FROM input_time_series
  GROUP BY start_station_name) table_2
ON
  table_1.start_station_name = table_2.start_station_name
WHERE
  num_points > 400

如需运行查询，请按以下步骤操作：

1. 在 Google Cloud 控制台中，点击编写新查询按钮。

2. 在查询编辑器文本区域中输入上面的 GoogleSQL 查询。

3. 点击运行。

查询中的 SELECT 语句使用 EXTRACT 函数从 starttime 列中提取日期信息。该查询使用 COUNT(*) 子句获取花旗单车的每日总行程数。

table_1 有 679 个时序。该查询使用额外的 INNER JOIN 逻辑来选择所有包含超过 400 个时间点的时序，因此总共有 383 个时序。

第三步：使用默认参数同时预测多个时序

在此步骤中，您将预测从不同花旗单车站点开始的每日总行程数。为此，您必须预测多个时序。您可以编写多个 CREATE MODEL 查询，但这可能会是一个单调乏味且耗时的过程，尤其是当您有大量时序时。

为了改进此过程，BigQuery ML 允许您创建一组时序模型，以使用单个查询预测多个时序。此外，系统会同时拟合所有时序模型。

在以下 GoogleSQL 查询中，CREATE MODEL 子句会创建并训练一组名为 bqml_tutorial.nyc_citibike_arima_model_default 的模型。

CREATE OR REPLACE MODEL `bqml_tutorial.nyc_citibike_arima_model_default`
OPTIONS
  (model_type = 'ARIMA_PLUS',
   time_series_timestamp_col = 'date',
   time_series_data_col = 'num_trips',
   time_series_id_col = 'start_station_name'
  ) AS
SELECT *
FROM bqml_tutorial.nyc_citibike_time_series
WHERE date < '2016-06-01'

如需运行 CREATE MODEL 查询以创建并训练模型，请按以下步骤操作：

1. 在 Google Cloud 控制台中，点击编写新查询按钮。

2. 在查询编辑器文本区域中输入上面的 GoogleSQL 查询。

3. 点击运行。

   查询大约需要 14 分 25 秒才能完成。

OPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='date', ...) 子句指示您正在创建一组基于 ARIMA 的时序 ARIMA_PLUS 模型。除了 time_series_timestamp_col 和 time_series_data_col 之外，您还必须指定 time_series_id_col，它用于注释不同的输入时序。

此示例排除了时序中 2016-06-01 之后的时间点，以便之后使用 ML.EVALUATE 函数和这些时间点来评估预测准确率。

第四步：评估每个时序的预测准确率

在此步骤中，您将使用以下 ML.EVALUATE 查询来评估每个时序的预测准确率。

SELECT *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.nyc_citibike_arima_model_default`,
              TABLE `bqml_tutorial.nyc_citibike_time_series`,
              STRUCT(7 AS horizon, TRUE AS perform_aggregation))

要运行上述查询，请按以下步骤操作：

1. 在 Google Cloud 控制台中，点击编写新查询按钮。

2. 在查询编辑器文本区域中输入上面的 GoogleSQL 查询。

3. 点击运行。 此查询报告多个预测指标，包括：

   • 平均绝对误差
   • 均方误差
   • 平均绝对百分比误差
   • 对称平均绝对百分比误差

   结果应如下所示：

ML.EVALUATE 将上一步中训练的 ARIMA_PLUS 模型作为其第一个实参。

第二个实参是包含标准答案数据的数据表。查询会将这些预测结果与标准答案数据进行比较，以计算准确率指标。此时，nyc_citibike_time_series 包含 2016-06-01 之前和 2016-06-01 之后的时序点。2016-06-01 之后的点是标准答案数据。2016-06-01 之前的点用于训练模型以生成此日期之后的预测。计算指标只需要 2016-06-01 之后的点。2016-06-01 之前的点在指标计算中会被忽略。

第三个实参是包含两个形参的 STRUCT。horizon 为 7，表示查询根据 7 点预测计算预测准确率。请注意，如果用于比较的标准答案数据包含的点少于 7 个，则仅根据可用点计算准确率指标。perform_aggregation 的值为 TRUE，表示基于时间点汇总预测准确率指标。如果将 perform_aggregation 指定为 FALSE，则返回每个预测时间点的预测准确率。

第五步：评估所有时序的整体预测准确率

在此步骤中，您将使用以下查询来评估 383 个时序的整体预测准确率：

SELECT
  AVG(mean_absolute_percentage_error) AS MAPE,
  AVG(symmetric_mean_absolute_percentage_error) AS sMAPE
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.nyc_citibike_arima_model_default`,
              TABLE `bqml_tutorial.nyc_citibike_time_series`,
              STRUCT(7 AS horizon, TRUE AS perform_aggregation))

在 ML.EVALUATE 返回的预测指标中，只有平均绝对百分比误差和对称平均绝对百分比误差与时序值无关。因此，要评估一组时序的整体预测准确率，只有这两个指标的汇总结果才有意义。

此查询返回以下结果：MAPE 为 0.3471，sMAPE 为 0.2563。

第六步：使用较小的超参数搜索空间同时预测多个时序

在第三步中，所有训练选项都使用默认值（包括 auto_arima_max_order）。此选项控制 auto.ARIMA 算法中超参数调节的搜索空间。

在此步骤中，您将使用较小的超参数搜索空间。

CREATE OR REPLACE MODEL bqml_tutorial.nyc_citibike_arima_model_max_order_2
OPTIONS
  (model_type = 'ARIMA_PLUS',
   time_series_timestamp_col = 'date',
   time_series_data_col = 'num_trips',
   time_series_id_col = 'start_station_name',
   auto_arima_max_order = 2
  ) AS
SELECT *
FROM bqml_tutorial.nyc_citibike_time_series
WHERE date < '2016-06-01'

此查询将 auto_arima_max_order 从 5（默认值）减少到 2。

如需运行查询，请按以下步骤操作：

1. 在 Google Cloud 控制台中，点击编写新查询按钮。

2. 在查询编辑器文本区域中输入上面的 GoogleSQL 查询。

3. 点击运行。

   查询大约需要 1 分 45 秒才能完成。在前面的步骤中，当 auto_arima_max_order 为 5 时，查询需要 14 分 25 秒才能完成。因此，通过将 auto_arima_max_order 设置为 2，速度提升了约 7 倍。您可能想知道速度提升为什么不是 5/2=2.5 倍，这是因为在增加 auto_arima_max_order 的阶数时，不仅候选模型的数量增加，复杂度也会增加，从而使模型训练时间变长。

第七步：评估使用较小超参数搜索空间时的预测准确率

SELECT
  AVG(mean_absolute_percentage_error) AS MAPE,
  AVG(symmetric_mean_absolute_percentage_error) AS sMAPE
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.nyc_citibike_arima_model_max_order_2`,
              TABLE `bqml_tutorial.nyc_citibike_time_series`,
              STRUCT(7 AS horizon, TRUE AS perform_aggregation))

此查询返回以下结果：MAPE 为 0.3337，sMAPE 为 0.2337。

在第 5 步中，使用较大的超参数搜索空间时 (auto_arima_max_order = 5)，得到的 MAPE 为 0.3471，sMAPE 为 0.2563。因此在这种情况下，较小的超参数搜索空间实际上可提供更高的预测准确率。一个原因是，auto.ARIMA 算法仅针对整个建模流水线的趋势模块执行超参数调节。auto.ARIMA 算法选择的最佳 ARIMA 模型可能不会为整个流水线生成最佳预测结果。

第八步：使用较小的超参数搜索空间和智能快速训练策略同时预测多个时序

在此步骤中，您将使用较小的超参数搜索空间以及智能快速训练策略（使用一个或多个 max_time_series_length、max_time_series_length 或 time_series_length_fraction 训练选项）。

定期建模（如季节性）需要特定数量的时间点，而趋势建模需要较少的时间点。同时，趋势建模的计算开销比其他时序组件（如季节性）高得多。通过使用上面的快速训练选项，您可以使用部分时序高效地为趋势组件建模，同时其他时序组件使用所有时序。

此示例使用 max_time_series_length 实现快速训练。

CREATE OR REPLACE MODEL `bqml_tutorial.nyc_citibike_arima_model_max_order_2_fast_training`
OPTIONS
  (model_type = 'ARIMA_PLUS',
   time_series_timestamp_col = 'date',
   time_series_data_col = 'num_trips',
   time_series_id_col = 'start_station_name',
   auto_arima_max_order = 2,
   max_time_series_length = 30
  ) AS
SELECT *
FROM `bqml_tutorial.nyc_citibike_time_series`
WHERE date < '2016-06-01'

max_time_series_length 选项的值为 30，因此对于 383 个时序中的每一个，只有最近的 30 个时间点用于为趋势组件建模。为非趋势组件建模仍使用所有时序。

如需运行查询，请按以下步骤操作：

1. 在 Google Cloud 控制台中，点击编写新查询按钮。

2. 在查询编辑器文本区域中输入上面的 GoogleSQL 查询。

3. 点击运行。

   查询大约需要 35 秒才能完成。与不使用快速训练策略的训练查询（即需要 1 分 45 秒）相比，查询速度快了 3 倍。请注意，由于查询的非训练部分（例如数据预处理等）具有固定的时间开销，因此当时序的数量大大高于此例中的数量时，速度提升将更明显。对于一百万个时序，速度提升将接近时序长度与 max_time_series_length 值的比。在这种情况下，速度提升将大于 10 倍。

第九步：评估使用较小超参数搜索空间和智能快速训练策略的模型的预测准确率

SELECT
  AVG(mean_absolute_percentage_error) AS MAPE,
  AVG(symmetric_mean_absolute_percentage_error) AS sMAPE
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.nyc_citibike_arima_model_max_order_2_fast_training`,
              TABLE `bqml_tutorial.nyc_citibike_time_series`,
              STRUCT(7 AS horizon, TRUE AS perform_aggregation))

此查询返回以下结果：MAPE 为 0.3515，sMAPE 为 0.2473。

在之前的步骤中，在不使用快速训练策略的情况下，预测准确率结果为：MAPE 为 0.3337，sMAPE 为 0.2337。两组指标值相差不到 3%，没有显著的统计差异。

简而言之，您使用较小的超参数搜索空间和智能快速训练策略将模型训练速度提高了 20 倍以上，同时不牺牲预测准确率。如前所述，时序数量越多，智能快速训练策略的速度提升效果越明显。此外，ARIMA_PLUS 使用的底层 ARIMA 库已经过优化，运行速度比过去快了 5 倍。综合起来，这使我们可以实现在数小时内完成数百万个时序的预测。

第十步：预测一百万个时序

在此步骤中，您将使用公开的爱荷华州酒类销量数据来预测不同商店中超过一百万种酒类商品的销量。

CREATE OR REPLACE MODEL
  `bqml_tutorial.liquor_forecast_by_product`
OPTIONS(
  MODEL_TYPE = 'ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL = 'date',
  TIME_SERIES_DATA_COL = 'total_bottles_sold',
  TIME_SERIES_ID_COL = ['store_number', 'item_description'],
  HOLIDAY_REGION = 'US',
  AUTO_ARIMA_MAX_ORDER = 2,
  MAX_TIME_SERIES_LENGTH = 30
) AS
SELECT
  store_number,
  item_description,
  date,
  SUM(bottles_sold) as total_bottles_sold
FROM
  `bigquery-public-data.iowa_liquor_sales.sales`
WHERE date BETWEEN DATE("2015-01-01") AND DATE("2021-12-31")
GROUP BY store_number, item_description, date

模型训练仍然使用较小的超参数搜索空间和智能快速训练策略。查询大约需要 1 小时 16 分钟才能完成。

清理

为避免因本教程中使用的资源导致您的 Google Cloud 账号产生费用，请删除包含这些资源的项目，或者保留项目但删除各个资源。

• 删除您在教程中创建的项目。
• 或者，保留项目但删除数据集。

删除数据集

删除项目也将删除项目中的所有数据集和所有表。如果您希望重复使用该项目，则可以删除在本教程中创建的数据集：

1. 如有必要，请在 Google Cloud 控制台中打开 BigQuery 页面。

   前往 BigQuery 页面

2. 在导航窗格中，点击您创建的 bqml_tutorial 数据集。

3. 点击删除数据集以删除数据集、表和所有数据。

4. 在删除数据集对话框中，通过输入数据集的名称 (bqml_tutorial) 来确认该删除命令，然后点击删除。

删除项目

要删除项目，请执行以下操作：

警告：删除项目会产生以下影响

• 项目中的所有内容都会被删除。如果您将现有项目用于本文档中的任务，则删除该项目后，还将删除您已在该项目中完成的任何其他工作。
• 自定义项目 ID 丢失。创建此项目时，您可能创建了要在将来使用的自定义项目 ID。要保留使用该项目 ID 的网址（例如 appspot.com 网址），请删除项目内的所选资源，而不是删除整个项目。

如果您打算探索多个架构、教程或快速入门，则重复使用项目可以帮助您避免超出项目配额限制。

1. 在 Google Cloud 控制台中，进入管理资源页面。

   转到“管理资源”

2. 在项目列表中，选择要删除的项目，然后点击删除。
3. 在对话框中输入项目 ID，然后点击关闭以删除项目。

后续步骤

• 如需详细了解机器学习，请参阅机器学习速成课程。
• 如需大致了解 BigQuery ML，请参阅 BigQuery ML 简介。
• 如需详细了解 Google Cloud 控制台，请参阅使用 Google Cloud 控制台。