GSP009

概览

在本实验中，您将使用 BigQuery 分析美国国家海洋和大气管理局 (NOAA) 的历史天气观测数据，然后再结合纽约市 311 热线收到的市民投诉进行分析，这与 Reto Meier 的博文非常相似，这篇文章展示了数据变量之间如何相互关联。

您将亲自体验 Google Cloud 的多项重要能力，它们对科学家大有裨益：

1. 无服务器：无需将数据下载到机器上即可使用，数据集将保留在云端。
2. 易于使用：可对数据集运行临时 SQL 查询，而无需预先准备数据（例如建立索引）。
3. 扩缩：以交互方式对超大型数据集进行数据探索。您无需为了保证处理速度而对数据进行采样，直接处理即可。
4. 便捷共享：可以顺畅地对来自不同数据集的数据运行查询。BigQuery 是便捷的数据集共享工具。当然，您也可以选择不公开数据，或者仅与特定人员共享，并非所有数据都需要对外公开。

最终，您将找出哪些类型的市民投诉与天气相关。例如，您会发现一个并不意外的规律：室外气温较低时，关于住宅供暖设备的投诉最为常见。

学习内容

在本实验中，您将：

• 在 BigQuery 控制台中执行交互式查询。
• 整合多个数据集并对其进行分析。
• 使用 CORR 函数确定数据集之间存在正相关、负相关还是不相关。

前提条件

本实验是入门级实验，假设您具备一定的 BigQuery 和 SQL 使用经验。如果您从未接触过 BigQuery 或 MySQL，可以参加 BigQuery：Qwik Start - 控制台实验，快速了解这些 Google Cloud 服务。

设置和要求

点击“开始实验”按钮前的注意事项

请阅读以下说明。实验是计时的，并且您无法暂停实验。计时器在您点击开始实验后即开始计时，显示 Google Cloud 资源可供您使用多长时间。

此实操实验可让您在真实的云环境中开展实验活动，免受模拟或演示环境的局限。为此，我们会向您提供新的临时凭据，您可以在该实验的规定时间内通过此凭据登录和访问 Google Cloud。

为完成此实验，您需要：

• 能够使用标准的互联网浏览器（建议使用 Chrome 浏览器）。

注意：请使用无痕模式（推荐）或无痕浏览器窗口运行此实验。这可以避免您的个人账号与学生账号之间发生冲突，这种冲突可能导致您的个人账号产生额外费用。

• 完成实验的时间 - 请注意，实验开始后无法暂停。

注意：请仅使用学生账号完成本实验。如果您使用其他 Google Cloud 账号，则可能会向该账号收取费用。

如何开始实验并登录 Google Cloud 控制台

1. 点击开始实验按钮。如果该实验需要付费，系统会打开一个对话框供您选择支付方式。右侧是实验设置和访问权限面板，其中包含以下内容：

   • 打开 Google Cloud 控制台按钮
   • 您在本实验中必须使用的临时凭证（用户名和密码）
   • 帮助您逐步完成该实验所需的其他信息（如果需要）

   请注意，实验计时器位于页面顶部附近，将显示剩余时间。

2. 点击打开 Google Cloud 控制台（如果您使用的是 Chrome 浏览器，请右键点击并选择在无痕式窗口中打开链接）。

   该实验会启动资源并打开另一个标签页，显示“登录”页面。

   提示：可以将这些标签页分别放在不同的窗口中，并排显示。

   注意：如果您看见选择账号对话框，请点击使用其他账号。

3. 如有必要，请复制下方的用户名，然后将其粘贴到登录对话框中。

   {{{user_0.username | "<用户名>"}}}

   您也可以在实验设置和访问权限面板中找到“用户名”。

4. 点击下一步。

5. 复制下面的密码，然后将其粘贴到欢迎对话框中。

   {{{user_0.password | "<密码>"}}}

   您也可以在实验设置和访问权限面板中找到“密码”。

6. 点击下一步。

   重要提示：您必须使用实验提供的凭证。请勿使用您的 Google Cloud 账号凭证。 注意：在本实验中使用您自己的 Google Cloud 账号可能会产生额外费用。

7. 依次点击进入后续页面：

   • 接受条款及条件。
   • 由于这是临时账号，请勿添加账号恢复选项或双重身份验证。
   • 请勿注册免费试用。

片刻之后，系统会在此标签页中打开 Google Cloud 控制台。

注意：如需访问 Google Cloud 产品和服务，请点击导航菜单，或在搜索字段中输入服务或产品的名称。

任务 1. 探索天气数据

打开 BigQuery 控制台

1. 在 Google Cloud 控制台中，选择导航菜单 > BigQuery。

您会看到欢迎在 Cloud 控制台中使用 BigQuery 消息框，其中提供了指向快速入门指南和版本说明的链接。

2. 点击完成。

BigQuery 控制台即会打开。

3. 在探索器标签页中，点击 + 添加数据。

“添加数据”窗口随即打开。

4. 在“其他来源”下方，点击按名称为项目加星标。

5. 输入 bigquery-public-data，然后点击加星标。

在 BigQuery 控制台的“探索器”窗格中，您会看到两个项目：一个以您的实验项目 ID 命名，另一个名为 bigquery-public-data。

6. 在 BigQuery 控制台的探索器窗格中，展开 bigquery-public-data 数据集，然后点击数据集。在输入属性名称或值字段中，搜索 noaa_gsod，然后选择 gsod2014 表。

7. 在“表 (gsod2014)”窗口中，点击预览标签页。

8. 查看这些列以及其中的部分数据值。

9. 点击查询，然后替换以下查询：

SELECT -- Create a timestamp from the date components. stn, TIMESTAMP(CONCAT(year,"-",mo,"-",da)) AS timestamp, -- Replace numerical null values with actual null AVG(IF (temp=9999.9, null, temp)) AS temperature, AVG(IF (wdsp="999.9", null, CAST(wdsp AS Float64))) AS wind_speed, AVG(IF (prcp=99.99, 0, prcp)) AS precipitation FROM `bigquery-public-data.noaa_gsod.gsod20*` WHERE CAST(YEAR AS INT64) > 2010 AND CAST(MO AS INT64) = 6 AND CAST(DA AS INT64) = 12 AND (stn="725030" OR -- La Guardia stn="744860") -- JFK GROUP BY stn, timestamp ORDER BY timestamp DESC, stn ASC

10. 点击运行。查看结果，试着确定此查询的作用。

点击下方的检查我的进度，以确认您在本实验中的进度。

探索天气数据

任务 2. 探索纽约市民投诉数据

1. 在 BigQuery 控制台的“探索器”窗格中，选择新添加的 bigquery-public-data 数据集，然后点击数据集。在输入属性名称或值字段中，搜索 new_york_311 数据集，然后选择 311_service_requests 表。

2. 然后，点击预览标签页。此时控制台应该如下所示：

3. 查看这些列以及其中的部分数据值。

4. 如果编辑器已关闭，请点击“+”（SQL 查询）图标。

5. 将以下内容粘贴到查询编辑器中：

SELECT EXTRACT(YEAR FROM created_date) AS year, complaint_type, COUNT(1) AS num_complaints FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY year, complaint_type ORDER BY num_complaints DESC

6. 点击运行。

7. 查看结果，确定最常见的投诉有哪些。在本实验的后面部分，您将试着确定这些投诉是否与天气有关。

点击下方的检查我的进度，以确认您在本实验中的进度。

探索纽约市民投诉数据

任务 3. 保存新的天气数据表

1. 在 BigQuery 控制台的“探索器”窗格中，点击项目 ID 旁边的三点状图标，然后选择创建数据集。

2. 在“创建数据集”对话框中，将数据集 ID 设置为 demos，并将其他选项保留为各自的默认值。

3. 点击创建数据集。您的项目现在有一个名为 demos 的数据集。

4. 点击“+”（SQL 查询）图标，然后运行以下查询：

SELECT -- Create a timestamp from the date components. timestamp(concat(year,"-",mo,"-",da)) as timestamp, -- Replace numerical null values with actual nulls AVG(IF (temp=9999.9, null, temp)) AS temperature, AVG(IF (visib=999.9, null, visib)) AS visibility, AVG(IF (wdsp="999.9", null, CAST(wdsp AS Float64))) AS wind_speed, AVG(IF (gust=999.9, null, gust)) AS wind_gust, AVG(IF (prcp=99.99, null, prcp)) AS precipitation, AVG(IF (sndp=999.9, null, sndp)) AS snow_depth FROM `bigquery-public-data.noaa_gsod.gsod20*` WHERE CAST(YEAR AS INT64) > 2008 AND (stn="725030" OR -- La Guardia stn="744860") -- JFK GROUP BY timestamp

5. 在查询编辑器部分，依次点击修改 > 查询设置。

6. 在“查询设置”对话框中，设置以下字段。将所有其他字段保留为各自的默认值。

目标位置：选择为查询结果设置目标表

数据集：输入 .demos，并选择数据集

表 ID：输入 nyc_weather

结果大小：勾选允许大型结果（无大小限制）

7. 点击保存。

8. 点击运行。

结果现已保存到您创建的数据集 (demos) 中。

9. 返回修改 > 查询设置，然后在“目标位置”选项中选择将查询结果保存在临时表中。这样一来，demos 数据集就不会再作为未来查询的目标位置了。

10. 点击保存以关闭查询设置。

点击下方的检查我的进度，以确认您在本实验中的进度。

保存新的天气数据表

任务 4. 查找数据集之间的相关性

通过 CORR 函数计算得出的强相关性，意味着两个变量之间存在紧密且一致的关系。当一个变量的值增加时，另一个变量的值往往也会随之以可预测的方式增加（正相关）或减少（负相关）。从绝对值来看，相关系数大于或等于 0.7 通常被视为强相关。这意味着，一个变量的变化可以解释另一个变量至少 49% 的变动。

接下来，您将使用 CORR 函数来探究收到的投诉数量与每日气温之间是否存在相关性。

1. 创建 SQL 查询，点击“+”，然后运行以下查询：

SELECT descriptor, sum(complaint_count) as total_complaint_count, count(temperature) as data_count, ROUND(corr(temperature, avg_count),3) AS corr_count, ROUND(corr(temperature, avg_pct_count),3) AS corr_pct From ( SELECT avg(pct_count) as avg_pct_count, avg(day_count) as avg_count, sum(day_count) as complaint_count, descriptor, temperature FROM ( SELECT DATE(timestamp) AS date, temperature FROM demos.nyc_weather) a JOIN ( SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count FROM (SELECT DATE(created_date) AS date, concat(complaint_type, ": ", descriptor) as descriptor, COUNT(*) AS day_count FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY date, descriptor)x JOIN ( SELECT DATE(timestamp) AS date, COUNT(*) AS all_calls_count FROM `demos.nyc_weather` GROUP BY date )y ON x.date=y.date )b ON a.date = b.date GROUP BY descriptor, temperature ) GROUP BY descriptor HAVING total_complaint_count > 5000 AND ABS(corr_pct) > 0.5 AND data_count > 5 ORDER BY ABS(corr_pct) DESC

结果显示，供暖投诉与气温呈负相关（即寒冷天气供暖投诉较多），而关于枯树的投诉与气温呈正相关（即炎热天气投诉较多）。

接下来，将使用 CORR 函数来探究投诉数量与风速之间是否存在相关性。

2. 点击“+”（SQL 查询）图标，然后运行以下查询：

SELECT descriptor, sum(complaint_count) as total_complaint_count, count(wind_speed) as data_count, ROUND(corr(wind_speed, avg_count),3) AS corr_count, ROUND(corr(wind_speed, avg_pct_count),3) AS corr_pct From ( SELECT avg(pct_count) as avg_pct_count, avg(day_count) as avg_count, sum(day_count) as complaint_count, descriptor, wind_speed FROM ( SELECT DATE(timestamp) AS date, wind_speed FROM demos.nyc_weather) a JOIN ( SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count FROM (SELECT DATE(created_date) AS date, concat(complaint_type, ": ", descriptor) as descriptor, COUNT(*) AS day_count FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY date, descriptor)x JOIN ( SELECT DATE(timestamp) AS date, COUNT(*) AS all_calls_count FROM `demos.nyc_weather` GROUP BY date )y ON x.date=y.date )b ON a.date = b.date GROUP BY descriptor, wind_speed ) GROUP BY descriptor HAVING total_complaint_count > 5000 AND ABS(corr_pct) > 0.5 AND data_count > 5 ORDER BY ABS(corr_pct) DESC

3. 请注意，噪音相关投诉的 CORR 列均为负值，对于“为什么在有风的日子噪音投诉会减少”，您有什么大胆的猜想吗？这些系数是否具有统计显著性？

点击下方的检查我的进度，以确认您在本实验中的进度。

查找天气与投诉数量之间的相关性

恭喜！

在本实验中，您无需设置任何集群、创建任何索引，即可查询数据。您还整合了这两个数据集并关联了结果，从而获得了一些有趣的分析洞见。

后续步骤/了解详情

• 详细了解 BigQuery 中的 CORR() 函数。

• 详细了解 BigQuery 公共数据集。

Google Cloud 培训和认证

…可帮助您充分利用 Google Cloud 技术。我们的课程会讲解各项技能与最佳实践，可帮助您迅速上手使用并继续学习更深入的知识。我们提供从基础到高级的全方位培训，并有点播、直播和虚拟三种方式选择，让您可以按照自己的日程安排学习时间。各项认证可以帮助您核实并证明您在 Google Cloud 技术方面的技能与专业知识。

本手册的最后更新时间：2026 年 6 月 12 日

本实验的最后测试时间：2026 年 6 月 12 日

版权所有 2026 Google LLC 保留所有权利。Google 和 Google 徽标是 Google LLC 的商标。其他所有公司名和产品名可能是其各自相关公司的商标。