创建 k-means 模型以对伦敦自行车租赁数据集进行聚类

---

BigQuery ML 支持非监督式学习。您可以应用 k-means 算法，将数据按聚类进行分组。监督式机器学习与预测分析有关，与此不同的是，非监督式学习与描述性分析有关。您需要理解数据，以便作出数据驱动型决策。

在本教程中，您将在 BigQuery ML 中使用 k-means 模型，在伦敦自行车租赁公共数据集中构建数据聚类。伦敦自行车租赁数据包含伦敦 Santander 自行车租赁计划从 2011 年至今的租赁次数。数据包括起始和停止时间戳、车站名称和骑行时长。

本教程中的查询使用地理空间分析中提供的地理位置函数。如需详细了解地理空间分析，请参阅地理空间分析简介。

目标

在本教程中，您将执行以下操作：

• 创建 k-means 聚类模型。
• 根据 BigQuery ML 对聚类的可视化作出数据驱动型决策。

费用

本教程使用 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 ML 创建 k-means 模型，此模型根据自行车站的特性，为伦敦自行车租赁数据划分聚类。

创建 k-means 模型包含以下步骤。

• 第一步：创建用于存储模型的数据集。

  第一步是创建用于存储模型的数据集。

  注意：由于伦敦自行车租赁数据集存储在 EU 多区域位置，因此您的数据集也必须位于 EU。

• 第二步：检查训练数据。

  下一步是通过对 london_bicycles 表运行查询，检查用于训练聚类模型的数据。由于 k-means 是一种非监督式学习技术，因此模型训练不需要标签，您也不需要将数据拆分为训练数据和评估数据。

• 第三步：创建 k-means 模型。

  第三步是创建 k-means 模型。创建该模型时，聚类字段是 station_name，您根据车站距市中心的距离等车站特性为数据划分聚类。

• 第四步：使用 ML.PREDICT 函数预测车站的聚类。

  接下来，您使用 ML.PREDICT 函数预测一组给定车站的聚类。您预测包含字符串 Kennington 的所有车站名称的聚类。

• 第五步：利用模型作出数据驱动型决策。

  最后一步是利用模型作出数据驱动型决策。例如，根据模型结果，您可以确定增加容量将令哪些车站受益。

第一步：创建数据集

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

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

   转到 BigQuery 页面

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

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

   

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

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

   • 在位置类型部分，选择多区域，然后选择 EU (multiple regions in European Union)（欧盟[欧盟的多个区域]）。

     伦敦自行车租赁公共数据集存储在 EU 多区域。数据集必须位于同一位置。

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

     

第二步：检查训练数据

接下来，检查用于训练 k-means 模型的数据。在本教程中，您根据以下特性为自行车站划分聚类：

• 租赁时长
• 每天的行程数量
• 与市中心的距离

SQL

以下 GoogleSQL 查询用于检查用来训练 k-means 模型的数据。

#standardSQL
WITH
hs AS (
SELECT
  h.start_station_name AS station_name,
  IF
  (EXTRACT(DAYOFWEEK
    FROM
      h.start_date) = 1
    OR EXTRACT(DAYOFWEEK
    FROM
      h.start_date) = 7,
    "weekend",
    "weekday") AS isweekday,
  h.duration,
  ST_DISTANCE(ST_GEOGPOINT(s.longitude,
      s.latitude),
    ST_GEOGPOINT(-0.1,
      51.5))/1000 AS distance_from_city_center
FROM
  `bigquery-public-data.london_bicycles.cycle_hire` AS h
JOIN
  `bigquery-public-data.london_bicycles.cycle_stations` AS s
ON
  h.start_station_id = s.id
WHERE
  h.start_date BETWEEN CAST('2015-01-01 00:00:00' AS TIMESTAMP)
  AND CAST('2016-01-01 00:00:00' AS TIMESTAMP) ),
stationstats AS (
SELECT
  station_name,
  isweekday,
  AVG(duration) AS duration,
  COUNT(duration) AS num_trips,
  MAX(distance_from_city_center) AS distance_from_city_center
FROM
  hs
GROUP BY
  station_name, isweekday )
SELECT
*
FROM
stationstats
ORDER BY
distance_from_city_center ASC

查询详情

此查询提取有关自行车租赁的数据（包括 start_station_name 和 duration），并与 distance-from-city-center 等车站信息联接。然后，查询会在 stationstats 中计算车站的特性（包括平均骑行时长和行程数量），然后传递车站特性 distance_from_city_center。

此查询使用 WITH 子句定义子查询。此查询还使用 ST_DISTANCE 和 ST_GEOGPOINT 地理空间分析函数。如需详细了解这些函数，请参阅地理函数。如需详细了解地理空间分析，请参阅地理空间分析简介。

运行查询

以下查询会编译您的训练数据，本教程后面的 CREATE MODEL 语句中也用到该查询。

要运行查询，请执行以下操作：

1. 转到 BigQuery 页面。

转到 BigQuery

1. 在编辑器窗格中，运行以下 SQL 语句：

   WITH
     hs AS (
     SELECT
       h.start_station_name AS station_name,
       IF
       (EXTRACT(DAYOFWEEK
         FROM
           h.start_date) = 1
         OR EXTRACT(DAYOFWEEK
         FROM
           h.start_date) = 7,
         "weekend",
         "weekday") AS isweekday,
       h.duration,
       ST_DISTANCE(ST_GEOGPOINT(s.longitude,
           s.latitude),
         ST_GEOGPOINT(-0.1,
           51.5))/1000 AS distance_from_city_center
     FROM
       `bigquery-public-data.london_bicycles.cycle_hire` AS h
     JOIN
       `bigquery-public-data.london_bicycles.cycle_stations` AS s
     ON
       h.start_station_id = s.id
     WHERE
       h.start_date BETWEEN CAST('2015-01-01 00:00:00' AS TIMESTAMP)
       AND CAST('2016-01-01 00:00:00' AS TIMESTAMP) ),
     stationstats AS (
     SELECT
       station_name,
       isweekday,
       AVG(duration) AS duration,
       COUNT(duration) AS num_trips,
       MAX(distance_from_city_center) AS distance_from_city_center
     FROM
       hs
     GROUP BY
       station_name, isweekday )
   SELECT
     *
   FROM
     stationstats
   ORDER BY
     distance_from_city_center ASC

2. 查询完成后，点击查询文本区域下方的结果标签页。“结果”标签页显示您查询的用于训练模型的列：station_name、duration、num_trips、distance_from_city_center。结果应如下所示。

BigQuery DataFrame

在尝试此示例之前，请按照《BigQuery 快速入门：使用 BigQuery DataFrames》中的 BigQuery DataFrames 设置说明进行操作。如需了解详情，请参阅 BigQuery DataFrames 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为本地开发环境设置身份验证。

import datetime

import bigframes
import bigframes.pandas as bpd

bigframes.options.bigquery.project = your_gcp_project_id
# Compute in the EU multi-region to query the London bicycles dataset.
bigframes.options.bigquery.location = "EU"

# Extract the information you'll need to train the k-means model in this
# tutorial. Use the read_gbq function to represent cycle hires
# data as a DataFrame.
h = bpd.read_gbq(
    "bigquery-public-data.london_bicycles.cycle_hire",
    col_order=["start_station_name", "start_station_id", "start_date", "duration"],
).rename(
    columns={
        "start_station_name": "station_name",
        "start_station_id": "station_id",
    }
)

s = bpd.read_gbq(
    # Use ST_GEOPOINT and ST_DISTANCE to analyze geographical
    # data. These functions determine spatial relationships between
    # geographical features.
    """
    SELECT
    id,
    ST_DISTANCE(
        ST_GEOGPOINT(s.longitude, s.latitude),
        ST_GEOGPOINT(-0.1, 51.5)
    ) / 1000 AS distance_from_city_center
    FROM
    `bigquery-public-data.london_bicycles.cycle_stations` s
    """
)

# Define Python datetime objects in the UTC timezone for range comparison,
# because BigQuery stores timestamp data in the UTC timezone.
sample_time = datetime.datetime(2015, 1, 1, 0, 0, 0, tzinfo=datetime.timezone.utc)
sample_time2 = datetime.datetime(2016, 1, 1, 0, 0, 0, tzinfo=datetime.timezone.utc)

h = h.loc[(h["start_date"] >= sample_time) & (h["start_date"] <= sample_time2)]

# Replace each day-of-the-week number with the corresponding "weekday" or
# "weekend" label by using the Series.map method.
h = h.assign(
    isweekday=h.start_date.dt.dayofweek.map(
        {
            0: "weekday",
            1: "weekday",
            2: "weekday",
            3: "weekday",
            4: "weekday",
            5: "weekend",
            6: "weekend",
        }
    )
)

# Supplement each trip in "h" with the station distance information from
# "s" by merging the two DataFrames by station ID.
merged_df = h.merge(
    right=s,
    how="inner",
    left_on="station_id",
    right_on="id",
)

# Engineer features to cluster the stations. For each station, find the
# average trip duration, number of trips, and distance from city center.
stationstats = merged_df.groupby(["station_name", "isweekday"]).agg(
    {"duration": ["mean", "count"], "distance_from_city_center": "max"}
)
stationstats.columns = ["duration", "num_trips", "distance_from_city_center"]
stationstats = stationstats.sort_values(
    by="distance_from_city_center", ascending=True
).reset_index()

# Expected output results: >>> stationstats.head(3)
# station_name	isweekday duration  num_trips	distance_from_city_center
# Borough Road...	weekday	    1110	    5749	    0.12624
# Borough Road...	weekend	    2125	    1774	    0.12624
# Webber Street...	weekday	    795	        6517	    0.164021
#   3 rows × 5 columns

第三步：创建 k-means 模型

现在，您已检查完训练数据，下一步是使用数据创建 k-means 模型。

SQL

您可以使用包含选项 model_type=kmeans 的 CREATE MODEL 语句创建和训练 k-means 模型。

查询详情

CREATE MODEL 语句指定要使用的聚类数量为 4。在 SELECT 语句中，EXCEPT 子句不包括 station_name 列，因为 station_name 不是特征。此查询为每个 station_name 创建一个唯一行，并且 SELECT 语句中只提及特征。

如果您忽略 num_clusters 选项，BigQuery ML 会根据训练数据中的总行数选择一个合理的默认值。您也可以执行超参数调节来找到一个合适的数字。如需确定最佳聚类数量，您可以针对不同的 num_clusters 值运行 CREATE MODEL 查询，找到错误度量，并选取错误度量为最小值的点。您可以通过选择模型并点击评估标签页来获取错误度量。此标签页会显示 Davies–Bouldin 索引。

运行查询

以下查询向您用于检查训练数据的查询添加 CREATE MODEL 语句，并移除数据中的 id 字段。

如需运行查询并创建 k-means 模型，请执行以下操作：

1. 转到 BigQuery 页面。

转到 BigQuery

1. 在编辑器窗格中，运行以下 SQL 语句：

   CREATE OR REPLACE MODEL `bqml_tutorial.london_station_clusters`
     OPTIONS(model_type='kmeans', num_clusters=4) AS
   WITH
     hs AS (
     SELECT
       h.start_station_name AS station_name,
     IF
       (EXTRACT(DAYOFWEEK
         FROM
           h.start_date) = 1
         OR EXTRACT(DAYOFWEEK
         FROM
           h.start_date) = 7,
         "weekend",
         "weekday") AS isweekday,
       h.duration,
       ST_DISTANCE(ST_GEOGPOINT(s.longitude,
           s.latitude),
         ST_GEOGPOINT(-0.1,
           51.5))/1000 AS distance_from_city_center
     FROM
       `bigquery-public-data.london_bicycles.cycle_hire` AS h
     JOIN
       `bigquery-public-data.london_bicycles.cycle_stations` AS s
     ON
       h.start_station_id = s.id
     WHERE
       h.start_date BETWEEN CAST('2015-01-01 00:00:00' AS TIMESTAMP)
       AND CAST('2016-01-01 00:00:00' AS TIMESTAMP) ),
     stationstats AS (
     SELECT
       station_name,
       isweekday,
       AVG(duration) AS duration,
       COUNT(duration) AS num_trips,
       MAX(distance_from_city_center) AS distance_from_city_center
     FROM
       hs
     GROUP BY
       station_name, isweekday)
   SELECT
     * EXCEPT(station_name, isweekday)
   FROM
     stationstats

2. 在导航面板的资源部分中，展开项目名称，点击 bqml_tutorial，然后点击 london_station_clusters。

3. 点击架构标签页。模型架构列出了 BigQuery ML 用于执行聚类的 4 个车站特性。架构应如下所示。

1. 点击评估标签页。此标签页显示 k-means 模型标识的聚类的可视化。在数值特征下，条形图最多显示每个形心的 10 个最重要的数值特征值。您可以从下拉菜单中选择要可视化的特征。

BigQuery DataFrame

在尝试此示例之前，请按照《BigQuery 快速入门：使用 BigQuery DataFrames》中的 BigQuery DataFrames 设置说明进行操作。如需了解详情，请参阅 BigQuery DataFrames 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为本地开发环境设置身份验证。

from bigframes.ml.cluster import KMeans

# To determine an optimal number of clusters, construct and fit several
# K-Means objects with different values of num_clusters, find the error
# measure, and pick the point at which the error measure is at its minimum
# value.
cluster_model = KMeans(n_clusters=4)
cluster_model.fit(stationstats)
cluster_model.to_gbq(
    your_model_id,  # For example: "bqml_tutorial.london_station_clusters"
    replace=True,
)

第四步：使用 ML.PREDICT 函数预测车站的聚类

如需识别某个特定车站所属的聚类，请使用 ML.PREDICT SQL 函数或 predict BigQuery DataFrames 函数

SQL

查询详情

此查询使用 REGEXP_CONTAINS 函数查找 station_name 列中包含字符串“Kennington”的所有条目。ML.PREDICT 函数使用这些值来预测哪些聚类将包含这些车站。

运行查询

以下查询可预测名称中包含字符串“Kennington”的每个车站的聚类。

如需运行 ML.PREDICT 查询，请执行以下步骤：

1. 转到 BigQuery 页面。

转到 BigQuery

1. 在编辑器窗格中，运行以下 SQL 语句：

   WITH
     hs AS (
     SELECT
       h.start_station_name AS station_name,
       IF
       (EXTRACT(DAYOFWEEK
         FROM
           h.start_date) = 1
         OR EXTRACT(DAYOFWEEK
         FROM
           h.start_date) = 7,
         "weekend",
         "weekday") AS isweekday,
       h.duration,
       ST_DISTANCE(ST_GEOGPOINT(s.longitude,
           s.latitude),
         ST_GEOGPOINT(-0.1,
           51.5))/1000 AS distance_from_city_center
     FROM
       `bigquery-public-data.london_bicycles.cycle_hire` AS h
     JOIN
       `bigquery-public-data.london_bicycles.cycle_stations` AS s
     ON
       h.start_station_id = s.id
     WHERE
       h.start_date BETWEEN CAST('2015-01-01 00:00:00' AS TIMESTAMP)
       AND CAST('2016-01-01 00:00:00' AS TIMESTAMP) ),
     stationstats AS (
     SELECT
       station_name,
       isweekday,
       AVG(duration) AS duration,
       COUNT(duration) AS num_trips,
       MAX(distance_from_city_center) AS distance_from_city_center
     FROM
       hs
     GROUP BY
       station_name, isweekday )
   SELECT
     * EXCEPT(nearest_centroids_distance)
   FROM
     ML.PREDICT( MODEL `bqml_tutorial.london_station_clusters`,
       (
       SELECT
         *
       FROM
         stationstats
       WHERE
         REGEXP_CONTAINS(station_name, 'Kennington')))

2. 查询完成后，点击查询文本区域下方的结果标签页。结果应如下所示。

   

BigQuery DataFrame

在尝试此示例之前，请按照《BigQuery 快速入门：使用 BigQuery DataFrames》中的 BigQuery DataFrames 设置说明进行操作。如需了解详情，请参阅 BigQuery DataFrames 参考文档。

如需向 BigQuery 进行身份验证，请设置应用默认凭据。 如需了解详情，请参阅为本地开发环境设置身份验证。

# Select model you'll use for predictions. `read_gbq_model` loads model
# data from BigQuery, but you could also use the `cluster_model` object
# from previous steps.
cluster_model = bpd.read_gbq_model(
    your_model_id,
    # For example: "bqml_tutorial.london_station_clusters",
)

# Use 'contains' function to filter by stations containing the string
# "Kennington".
stationstats = stationstats.loc[
    stationstats["station_name"].str.contains("Kennington")
]

result = cluster_model.predict(stationstats)

# Expected output results:   >>>results.peek(3)
# CENTROID...	NEAREST...	station_name  isweekday	 duration num_trips dist...
# 	1	[{'CENTROID_ID'...	Borough...	  weekday	  1110	    5749	0.13
# 	2	[{'CENTROID_ID'...	Borough...	  weekend	  2125      1774	0.13
# 	1	[{'CENTROID_ID'...	Webber...	  weekday	  795	    6517	0.16
#   3 rows × 7 columns

第五步：利用模型作出数据驱动型决策

评估结果可帮助您解释不同聚类。 在以下示例中，形心 3 显示靠近市中心的一个繁忙城市车站。形心 2 显示第二个城市车站，该车站不太繁忙，用于较长的租期。形心 1 显示一个不太繁忙的城市车站，租期较短。形心 4 显示一个行程较长的郊区车站。

根据这些结果，您可以利用数据作出决策。 例如：

• 假设您需要对一种新型车锁进行实验。您应该选择哪些车站作为此次实验的对象？形心 1、形心 2 或形心 4 中的车站似乎是合乎逻辑的选择，因为它们不是最繁忙的车站。

• 假设您想要在一些车站投放赛车。您应该选择哪些车站？形心 4 是距离市中心较远且行程最长的车站组。这些车站适合作为赛车的候选车站。

清除数据

为避免因本教程中使用的资源导致您的 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.

后续步骤

• 如需大致了解 BigQuery ML，请参阅 BigQuery ML 简介。
• 如需了解如何创建模型，请参阅 CREATE MODEL 语法页面。