GSP407

概览

BigQuery 是 Google 推出的全托管式、无需运维、费用低廉的分析数据库。借助 BigQuery，您可以查询 TB 级的数据，而不必管理任何基础设施，也无需数据库管理员。BigQuery 使用 SQL，并且支持随用随付模式。BigQuery 让您可以专心分析数据，发掘有意义的数据洞见。

我们新推出了一个电子商务数据集，其中包含关于 Google Merchandise Store 的上百万条 Google Analytics 记录，并且已加载到 BigQuery 的表中。在本实验中，您将使用该数据集的副本。您可以根据本实验提供的示例场景来理解数据及移除重复信息的方法。然后，本实验将逐步引导您深入分析这些数据。

如要了解和使用所提供的 BigQuery 查询来分析这些数据，请参阅 BigQuery 查询语法参考文档。

目标

在本实验中，您将使用 BigQuery 执行以下操作：

• 访问电子商务数据集
• 查看数据集元数据
• 移除重复的条目
• 编写和执行查询

设置和要求

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

请阅读以下说明。实验是计时的，并且您无法暂停实验。计时器在您点击开始实验后即开始计时，显示 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 中固定实验项目

在本部分中，您将添加 data-to-insights 项目到您的环境资源中。

1. 点击导航菜单 > BigQuery。

您会看到“欢迎在 Cloud 控制台中使用 BigQuery”消息框。

注意：“欢迎在 Cloud 控制台中使用 BigQuery”消息框中提供了指向快速入门指南和界面更新信息的链接。

2. 点击完成。

默认情况下，BigQuery 网页界面中不会显示 BigQuery 公共数据集。如需打开公共数据集项目，请复制“data-to-insights”。

3. 点击 + 添加 > 按名称为项目加星标，然后将名称设置为 data-to-insights。点击加星标。

“探索器”部分现在列出了 data-to-insights 项目。

任务 2. 探索电子商务数据和识别重复记录

场景：您的数据分析团队已将某个电子商务网站的 Google Analytics 日志导出到 BigQuery，并新建了包含所有电子商务访问者会话原始数据的表。

探索 all_sessions_raw 表的数据：

1. 点击 data-to-insights 旁边的展开节点图标以展开该项目。
2. 展开 ecommerce（电子商务）。
3. 点击 all_sessions_raw。

在右侧窗格中，系统会打开一个板块，显示表数据的 3 种视图：

• “架构”标签页：“字段名称”“类型”“模式”和“说明”；用于整理数据的逻辑约束
• “详细信息”标签页：表元数据
• “预览”标签页：表预览

4. 点击详细信息标签页以查看表元数据。

问题：

识别重复的行

看到示例数据量可能有助于您更直观地理解数据集包含的内容。

1. 要预览表中的示例行而不使用 SQL，请点击预览标签页。

2. 滚动查看行。您无法通过单个字段来判断某一行是否具有唯一性，因此需要使用高级逻辑来识别重复的行。

3. 您将使用的查询（如下所示）会对每个字段执行 SQL GROUP BY 函数，并将每个字段值都相同的行计入 (COUNT) 计数：

• 如果每个字段都是唯一的，COUNT 会返回 1，表示没有所有字段值都相同的行。
• 如果有多个行的所有字段值都相同，系统会将这些行归为一组，COUNT 则会返回大于 1 的结果。

该查询的最后一部分是一个聚合过滤器，使用 HAVING 来仅显示重复条目计数 (COUNT) 大于 1 的结果。因此，拥有重复条目的记录数量等于结果表中行的数量。

4. 请复制以下查询并将其粘贴到查询编辑器中，然后运行查询以在所有列中查找重复的记录。

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

注意：对于您自己的数据集，即使您拥有唯一的键，在开始分析前，也建议使用 COUNT、GROUP BY 和 HAVING 来确认各行的唯一性。

点击检查我的进度，验证已完成以下目标： 识别重复的行

分析新建的 all_sessions 表

在本部分中，您将使用名为 all_sessions 且删除了重复信息的表。

场景：您的数据分析团队已为您提供了以下查询，您的架构专家也已根据您的架构，识别出每个记录中必须独一无二的键字段。

1. 运行该查询以确认没有重复条目存在，这次搜索 all_sessions 表：

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # find duplicates

该查询将返回 0 条记录。

注意：在 SQL 中，您可以对列索引执行 GROUP BY 或 ORDER BY，方法类似于执行 GROUP BY 1，而不是 GROUP BY fullVisitorId。

任务 3. 针对电子商务数据编写基本的 SQL

在本部分中，您的查询目标是获取对电子商务数据集的洞见。

编写一个查询来显示所有唯一身份访问者

您的查询通过计算 product_views 来确定总浏览次数，以及通过计算 fullVisitorID 来确定唯一身份访问者的数量。

1. 点击“+”（编写新查询）图标。
2. 在编辑器中编写以下查询：

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. 为确保语法正确，请确认实时查询验证器显示了绿色对勾图标。
4. 点击运行。 阅读结果以查看唯一身份访问者的数量。

结果：

5. 现在，编写一个查询来按引荐网站 (channelGrouping) 显示唯一身份访问者总数 (fullVisitorID)：

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

结果：

6. 编写一个查询，以按字母顺序列出所有唯一的产品名称 (v2ProductName)：

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

提示：在 SQL 中，ORDER BY 子句默认采用从 A 到 Z 的升序 (ASC) 排列。如果您想按降序显示，请尝试使用 ORDER BY field_name DESC。

结果：

此查询总共返回了 633 个产品（行）。

7. 编写一个查询来列出所有访问者（包括多次浏览同一产品的人）浏览次数最多的 5 个产品 (product_views)。您的查询会计算产品 (v2ProductName) 被浏览的次数 (product_views)，然后按降序列出前 5 个条目：

提示：在 Google Analytics 中，访问者在以下互动类型期间可以视为“浏览”产品：“page”“screenview”“event”“transaction”“item”“social”“exception”和“timing”。为满足我们的目的，请仅过滤出 type = 'PAGE'。

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

结果：

8. 额外操作：现在，优化该查询，不再计算多次查看同一产品的用户重复查看该产品的次数。每位访问者查看同一产品的次数都应仅计为 1 次。

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

提示：您可以使用 SQL WITH 子句将复杂的查询拆分成多个步骤。在本示例中，我们首先创建一个查询来查找每位访问者浏览的每个唯一的产品，并计为 1 次。然后，再创建一个查询来聚合所有访问者和产品。

结果：

9. 接下来，扩展上一个查询，以添加订购的不同产品的总数和订购的所有产品数量的总数 (productQuantity)：

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

结果：

问题：

10. 扩展该查询，以添加每个订单的平均产品数量（订购的产品数量总数/总订单数，或 SUM(productQuantity)/COUNT(productQuantity)）：

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

结果

问题：

22 液盎司的 YouTube 双层水杯的 avg_per_order 最高，为每个订单 9.38 个。

点击检查我的进度，验证已完成以下目标： 针对电子商务数据编写基本的 SQL

恭喜！

恭喜！在本实验中，您使用 BigQuery 查看并查询了数据，在产品营销的多个方面获得了有意义的数据洞见。您学会了如何访问电子商务数据集、查看数据集元数据、移除重复的条目以及编写和执行查询。

后续步骤/了解详情

• 探索 BigQuery 公共数据集。
• 已拥有 Google Analytics 账号，想要在 BigQuery 中查询您自己的数据集？请按照此导出指南中的说明操作。
• 查看 您可能不知道的 15 个 BigQuery 的优点。
• 参与其他实验以进一步了解 BigQuery：
  • 在 BigQuery 中排查常见的 SQL 错误
  • BigQuery 中的天气数据

Google Cloud 培训和认证

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

上次更新手册的时间：2024 年 4 月 2 日

上次测试实验的时间：2024 年 4 月 2 日

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