创建 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. 登录您的 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 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. 在 Google Cloud 控制台中，进入管理资源页面。

   转到“管理资源”

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

后续步骤

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