使用纽约市花旗单车行程的单一查询进行多时序预测

---

在本教程中，您将学习如何创建一组时间序列模型，以便通过单个查询执行多个时间序列预测。您还将了解如何应用不同的快速训练策略来显著加快查询速度，以及如何评估预测准确率。您将使用 new_york.citibike_trips 数据。此数据包含有关纽约市花旗单车行程的信息。

在阅读本教程之前，我们强烈建议您阅读根据 Google Analytics 数据进行单个时序预测。

目标

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

• CREATE MODEL 语句：用于创建一个时序模型或一组时序模型。
• ML.ARIMA_EVALUATE 函数：用于评估模型。
• ML.ARIMA_COEFFICIENTS 函数：用于检查模型系数。
• ML.EXPLAIN_FORECAST 函数：用于检索时序的各个组件（例如季节性和趋势），可用于说明预测结果。
• Looker Studio：用于直观呈现预测结果。
• （可选）ML.FORECAST 函数：用于预测每日总访问次数。

费用

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

• BigQuery
• BigQuery ML

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

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

准备工作

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

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

   Enable the BigQuery API.

   Enable the API

第一步：创建数据集

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

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

   转到 BigQuery 页面

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

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

   

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

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

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

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

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

     

第二步（可选）：直观呈现您要预测的时间序列

在创建模型之前，最好先查看输入时序的情况。您可以使用 Looker 数据洞察实现此目的。

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

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

#standardSQL
SELECT
   EXTRACT(DATE from starttime) AS date,
   COUNT(*) AS num_trips
FROM
  `bigquery-public-data.new_york.citibike_trips`
GROUP BY date

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

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   SELECT
      EXTRACT(DATE from starttime) AS date,
      COUNT(*) AS num_trips
   FROM
     `bigquery-public-data.new_york.citibike_trips`
   GROUP BY date

3. 点击运行。

   查询运行后，输出类似于以下屏幕截图。 在屏幕截图中，您可以看到此时序有 1184 个每日数据点，这些数据点的时间跨度超过 4 年。点击探索数据按钮，然后点击 Explore with Looker Studio（使用 Looker 数据洞察探索）。Looker 数据洞察将在新标签页中打开。在该新标签页中完成以下步骤。

   

4. 在图表面板中，选择时序图表 (Time series chart)：

   

   注意：在左侧面板中将打开一个图表。如果您在此图表中看到“行数过多”，请点击屏幕左上角的“添加图表”。在下拉面板中，点击时序图表图标。

5. 在数据面板中的图表面板下方，转到指标部分。添加 num_trips 字段，然后移除默认指标记录计数 (Record Count)：

   

   完成上述步骤后，系统会显示以下图表。该图表显示输入时序具有每周和每年的模式。时序还呈现了上升的趋势。

   

第三步：创建时序模型以执行单个时序预测

接下来，使用纽约市花旗单车行程数据创建一个时序模型。

以下 GoogleSQL 查询会创建一个用于预测单车每日总行程数的模型。CREATE MODEL 子句将创建并训练一个名为 bqml_tutorial.nyc_citibike_arima_model 的模型。

#standardSQL
CREATE OR REPLACE MODEL `bqml_tutorial.nyc_citibike_arima_model`
OPTIONS
  (model_type = 'ARIMA_PLUS',
   time_series_timestamp_col = 'date',
   time_series_data_col = 'num_trips'
  ) AS
SELECT
   EXTRACT(DATE from starttime) AS date,
   COUNT(*) AS num_trips
FROM
  `bigquery-public-data.new_york.citibike_trips`
GROUP BY date

OPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='date', ...) 子句指示您正在创建一个基于 ARIMA 的时序模型。默认情况下，auto_arima=TRUE，因此 auto.ARIMA 算法会自动调整 ARIMA_PLUS 模型中的超参数。该算法适合数十个候选模型，选择具有最低 Akaike 信息标准 (AIC) 的模型。此外，由于默认设置为 data_frequency='AUTO_FREQUENCY'，因此训练过程会自动推断输入时序的数据频率。最后，CREATE MODEL 语句默认使用 decompose_time_series=TRUE，用户也可以通过获取单独的时序组件（例如季节性周期和节假日效应）来进一步了解如何预测时序。

运行 CREATE MODEL 查询以创建并训练模型：

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   CREATE OR REPLACE MODEL `bqml_tutorial.nyc_citibike_arima_model`
   OPTIONS
     (model_type = 'ARIMA_PLUS',
      time_series_timestamp_col = 'date',
      time_series_data_col = 'num_trips'
     ) AS
   SELECT
      EXTRACT(DATE from starttime) AS date,
      COUNT(*) AS num_trips
   FROM
     `bigquery-public-data.new_york.citibike_trips`
   GROUP BY date

3. 点击运行。

   查询大约需要 17 秒才能完成，之后您的模型 (nyc_citibike_arima_model) 会显示在导航面板中。由于查询使用 CREATE MODEL 语句来创建模型，因此您看不到查询结果。

注意：您可能想知道美国节假日是否会对时序产生影响。您可以尝试在查询中将 holiday_region='US' 添加到 OPTIONS 列表中。这样，如果时序中确实有节假日模式，您就可以对这些节假日时间点进行更准确的建模。

第四步：预测时序并直观呈现结果

为了说明时序的预测方式，请使用 ML.EXPLAIN_FORECAST 函数直观呈现所有子时序组件，例如季节性和趋势。

为此，请执行以下步骤：

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   SELECT
     *
   FROM
     ML.EXPLAIN_FORECAST(MODEL `bqml_tutorial.nyc_citibike_arima_model`,
                         STRUCT(365 AS horizon, 0.9 AS confidence_level))

3. 点击运行。

4. 查询完成后，点击探索数据按钮，然后点击 Explore with Looker Studio（使用 Looker 数据洞察探索）。浏览器中会打开一个新的标签页。然后，在图表面板中，找到时序图表 (Time series chart) 图标并点击它，如下面的屏幕截图所示。

   

   注意：在左侧面板中将打开一个图表。如果您在此图表中看到“行数过多”，请点击屏幕左上角的“添加图表”。在下拉面板中，点击时序图表图标。

5. 在数据面板中，执行以下操作：

   1. 在日期范围维度部分中，选择 time_series_timestamp (Date)。
   2. 在维度部分中，选择 time_series_timestamp (Date)。
   3. 在指标部分中，移除默认指标 Record Count，并添加以下内容：
      • time_series_data
      • prediction_interval_lower_bound
      • prediction_interval_upper_bound
      • trend
      • seasonal_period_yearly
      • seasonal_period_weekly
      • spikes_and_dips
      • step_changes

   

6. 在样式 (Style) 面板中，向下滚动到缺失数据选项，然后使用换行符 (Line Breaks)，而不是换行到零 (Line to Zero)。

   

   完成这些步骤后，左侧面板中将显示以下图表。

   

第五步：同时预测多个时序

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

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

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

 #standardSQL
CREATE OR REPLACE MODEL `bqml_tutorial.nyc_citibike_arima_model_group`
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 = 5
  ) AS
SELECT
   start_station_name,
   EXTRACT(DATE from starttime) AS date,
   COUNT(*) AS num_trips
FROM
  `bigquery-public-data.new_york.citibike_trips`
WHERE start_station_name LIKE '%Central Park%'
GROUP BY start_station_name, date

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，它用于注释不同的输入时序。auto_arima_max_order 选项可控制 auto.ARIMA 算法中超参数调节的搜索空间。最后，默认情况下，CREATE MODEL 语句使用 decompose_time_series=TRUE，并且用户可以通过提取分解结果，进一步了解如何在训练流水线中分析时序。

SELECT ... FROM ... GROUP BY ... 子句指示您将生成多个时序；每个时序都与一个不同的 start_station_name 相关联。为简单起见，使用 WHERE ... LIKE ... 子句将起始站点限定为名称中包含 Central Park 的站点。

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

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   CREATE OR REPLACE MODEL `bqml_tutorial.nyc_citibike_arima_model_group`
   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 = 5
     ) AS
   SELECT
      start_station_name,
      EXTRACT(DATE from starttime) AS date,
      COUNT(*) AS num_trips
   FROM
     `bigquery-public-data.new_york.citibike_trips`
   WHERE start_station_name LIKE '%Central Park%'
   GROUP BY start_station_name, date

3. 点击运行。

   查询大约需要 24 秒才能完成，之后您的模型 (nyc_citibike_arima_model_group) 会显示在导航面板中。由于查询使用 CREATE MODEL 语句，因此您看不到查询结果。

注意：此查询将创建 12 个时序模型，12 个输入时序中的每个序列使用一个模型。由于是并行执行的，因此时间成本（大约 24 秒）仅比创建单个时序模型的时间成本多 1.4 倍。但是，如果您移除 WHERE ... LIKE ... 子句，则系统将预测 600 多个时序；由于槽容量有限，系统将无法完全并行预测这些时序。因此，查询可能需要大约 15 分钟才能完成。如需降低查询运行时，同时模型质量可能略微降低，您可以将 AUTO_ARIMA_MAX_ORDER 的值从默认值 5 降低到 3 或 4。这会缩小 auto.ARIMA 算法中超参数调节的搜索空间。如需了解详情，请参阅 Large-scale time series forecasting best practices。

第六步：检查时序模型集合的评估指标

创建模型后，您可以使用 ML.ARIMA_EVALUATE 函数查看所有已创建模型的评估指标。

在以下 GoogleSQL 查询中，FROM 子句对模型 bqml_tutorial.nyc_citibike_arima_model_group 使用 ML.ARIMA_EVALUATE 函数。评估指标仅取决于训练输入，因此您的模型是唯一的输入。

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

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   SELECT
    *
   FROM
    ML.ARIMA_EVALUATE(MODEL `bqml_tutorial.nyc_citibike_arima_model_group`)

3. 点击运行。

4. 查询只需不到一秒的时间即可完成。查询完成后，点击查询文本区域下方的结果标签页。结果应如下所示：

   

   结果包括以下列：

   • start_station_name
   • non_seasonal_p
   • non_seasonal_d
   • non_seasonal_q
   • has_drift
   • log_likelihood
   • AIC
   • variance
   • seasonal_periods
   • has_holiday_effect
   • has_spikes_and_dips
   • has_step_changes
   • error_message

   第一列 start_station_name 注释系统拟合每个时序模型所依据的时序。它与 time_series_id_col 指定的内容相同。

   以下四列（non_seasonal_p、non_seasonal_d、non_seasonal_q 和 has_drift）定义了训练流水线中的 ARIMA 模型。后面的三个指标（log_likelihood、AIC、variance）与 ARIMA 模型拟合过程相关。拟合过程通过使用 auto.ARIMA 算法确定最佳的 ARIMA 模型（每个时序对应一个最佳模型）。

   仅当 decompose_time_series=TRUE 时，才填充 has_holiday_effect、has_spikes_and_dips 和 has_step_changes 列。

   seasonal_periods 列是输入时序内的季节性模式。每个时序可以有不同的季节性模式。例如，您可以从图中看到一个时序具有一个每年模式，而其他时序则没有。

注意：虽然 auto.ARIMA 会为每个时序评估几十个候选 ARIMA 模型，但 ML.ARIMA_EVALUATE 默认只会输出最佳模型的信息，以使得输出表紧凑。如需查看所有候选模型，您可以设置 show_all_candidate_models=true。

第七步：检查模型的系数

ML.ARIMA_COEFFICIENTS 函数用于检索 ARIMA_PLUS 模型 bqml_tutorial.nyc_citibike_arima_model_group 的模型系数。ML.ARIMA_COEFFICIENTS 接受该模型作为唯一输入。

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

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   SELECT
    *
   FROM
    ML.ARIMA_COEFFICIENTS(MODEL `bqml_tutorial.nyc_citibike_arima_model_group`)

3. 点击运行。

   查询只需不到一秒的时间即可完成。结果应类似于以下屏幕截图：

   

   结果包括以下列：

   • start_station_name
   • ar_coefficients
   • ma_coefficients
   • intercept_or_drift

   第一列 start_station_name 注释系统拟合每个时序模型所依据的时序。

   ar_coefficients 显示了 ARIMA 模型的自动回归 (AR) 部分的模型系数。同样，ma_coefficients 显示了移动平均 (MA) 部分的模型系数。它们都是数组，其长度分别等于 non_seasonal_p 和 non_seasonal_q。intercept_or_drift 是 ARIMA 模型中的常量项。

第八步：使用模型同时预测多个时序并提供说明

ML.EXPLAIN_FORECAST 函数会使用您的模型 bqml_tutorial.nyc_citibike_arima_model_group 预测未来的时序值以及预测间隔，同时返回该时序的所有单独的组件。

STRUCT(3 AS horizon, 0.9 AS confidence_level) 子句指示查询会预测三个未来的时间点，并生成置信度为 90% 的预测区间。ML.EXPLAIN_FORECAST 函数采用该模型以及几个可选的参数。

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

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   SELECT
    *
   FROM
    ML.EXPLAIN_FORECAST(MODEL `bqml_tutorial.nyc_citibike_arima_model_group`,
                        STRUCT(3 AS horizon, 0.9 AS confidence_level))

3. 点击运行。

   查询只需不到一秒的时间即可完成。结果应如下所示：

   

   结果包括以下列：

   • start_station_name
   • time_series_timestamp
   • time_series_type
   • time_series_data
   • time_series_adjusted_data
   • standard_error
   • confidence_level
   • prediction_interval_lower_bound
   • prediction_interval_lower_bound
   • trend
   • seasonal_period_yearly
   • seasonal_period_quarterly
   • seasonal_period_monthly
   • seasonal_period_weekly
   • seasonal_period_daily
   • holiday_effect
   • spikes_and_dips
   • step_changes
   • residual

   输出行按 start_station_name 排序，对于每个 start_station_name，输出行按 time_series_timestamp 的时间顺序排序。不同的组件以不同输出列的形式列出。如需了解详情，请参阅 ML.EXPLAIN_FORECAST 的定义。

（可选）第九步：使用模型同时预测多个时序

ML.FORECAST 函数也可用于通过模型 bqml_tutorial.nyc_citibike_arima_model_group 预测未来的时序值以及预测间隔。

与 ML.EXPLAIN_FORECAST 一样，STRUCT(3 AS horizon, 0.9 AS confidence_level) 子句指示对于每个时序，该查询都预测三个未来时间点，并生成 90% 置信度的预测间隔。

ML.FORECAST 函数采用该模型以及几个可选的参数。

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

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

2. 在查询编辑器文本区域中输入以下 GoogleSQL 查询。

   #standardSQL
   SELECT
    *
   FROM
    ML.FORECAST(MODEL `bqml_tutorial.nyc_citibike_arima_model_group`,
                STRUCT(3 AS horizon, 0.9 AS confidence_level))

3. 点击运行。

   查询只需不到一秒的时间即可完成。结果应如下所示：

   

   结果包括以下列：

   • start_station_name
   • forecast_timestamp
   • forecast_value
   • standard_error
   • confidence_level
   • prediction_interval_lower_bound
   • prediction_interval_upper_bound
   • confidence_interval_lower_bound（即将弃用）
   • confidence_interval_upper_bound（即将弃用）

   第一列 start_station_name 注释系统拟合每个时序模型所依据的时序。每个 start_station_name 的预测结果都有 horizon 行。

   对于每个 start_station_name，输出行将按照 forecast_timestamp 的时间顺序排序。在时序预测中，由下限和上限捕获的预测区间与 forecast_value 一样重要。forecast_value 是预测区间的中点。预测区间取决于 standard_error 和 confidence_level。

清理

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

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

删除数据集

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

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

   前往 BigQuery 页面

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

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

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

删除项目

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

注意：删除项目会产生以下影响：

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

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

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

后续步骤

• 了解如何加速 ARIMA_PLUS 以在数小时内预测一百万个时序。
• 如需详细了解机器学习，请参阅机器学习速成课程。
• 如需大致了解 BigQuery ML，请参阅 BigQuery ML 简介。
• 如需详细了解 Google Cloud 控制台，请参阅使用 Google Cloud 控制台。