GSP408

概览

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

BigQuery 中已加载了一个新推出的电子商务数据集，该数据集包含关于 Google Merchandise Store 的上百万条 Google Analytics 记录。您将在本实验中使用该数据集的副本，并通过研究其中提供的字段和记录来获取洞见。

本实验将引导您逐步了解排查查询问题的逻辑。本实验提供了一些在现实生活场景中可能会发生的活动。假设您在整个实验期间都要与团队中新的数据分析师配合工作。他们向您提供了下文中的查询来回答针对您的电子商务数据集的一些问题。请根据回答修正他们的查询，以获得有意义的结果。

学习内容

在本实验中，您将学习如何执行以下任务：

• 将项目固定到 BigQuery 资源树上
• 使用 BigQuery 查询编辑器和查询验证器识别并排查 SQL 语法和逻辑错误

设置和要求

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

请阅读以下说明。实验是计时的，并且您无法暂停实验。计时器在您点击开始实验后即开始计时，显示 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. 点击导航菜单 > BigQuery。

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

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

2. 点击完成。

3. 默认情况下不会显示 BigQuery 公共数据集。如需打开公共数据集项目，请复制 data-to-insights。

4. 点击 + 添加 > 按名称为项目加星标，然后复制 data-to-insights 名称。

5. 点击加星标。

“探索器”部分会显示 data-to-insights 项目。

BigQuery 查询编辑器和查询验证器

对于下文各部分中的每个活动，本实验都提供了一些包含常见错误的查询供您进行问题排查。本实验将指导您注意哪些地方，并为您提供有关如何更正语法并返回有意义的结果的建议。

要按照排查步骤和建议操作，请复制相应查询并将其粘贴到 BigQuery 查询编辑器。如果存在错误，您会在包含错误的行旁边和查询验证器（底部角落）中看到红色感叹号。

如果您运行包含错误的查询，查询会失败，“作业信息”中会指出相应错误。

如果查询中没有错误，您会在查询验证器中看到一个绿色对勾标记。如果看到绿色对勾标记，请点击运行来运行该查询以查看输出结果。

注意：如需了解语法，请参阅标准 SQL 查询语法。

任务 2. 确认完成结账的客户总数

在本部分中，您的目标是构建一个查询，用来统计在您的网站中成功完成结账过程的唯一身份访问者的数量。相关数据位于您的数据分析师团队提供的 rev_transactions 表中。他们还提供了查询示例来帮助您开始分析数据，但您不确定这些查询编写得是否正确。

对包含查询验证器、别名和逗号错误的查询进行问题排查

• 查看下面的查询，并回答随后的问题：

#standardSQL SELECT FROM `data-to-inghts.ecommerce.rev_transactions` LIMIT 1000

• 下面这个更新过的查询有问题吗？

#standardSQL SELECT * FROM [data-to-insights:ecommerce.rev_transactions] LIMIT 1000

• 下面这个使用标准 SQL 的查询有问题吗？

#standardSQL SELECT FROM `data-to-insights.ecommerce.rev_transactions`

• 现在呢？下面的查询添加了一列：

#standardSQL SELECT fullVisitorId FROM `data-to-insights.ecommerce.rev_transactions`

• 现在呢？下面的查询添加了网页标题：

#standardSQL SELECT fullVisitorId hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• 现在呢？逗号缺失问题已更正。

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

答案：此查询虽然返回了结果，但您确定访问者未被重复计算吗？此外，查询只返回了一行数据回答“有多少唯一身份访问者完成了结账”这个问题。 在下一部分中，您将了解聚合查询结果的方法。

对包含逻辑错误、GROUP BY 语句和通配符过滤器的查询进行问题排查

• 要聚合下面的查询来回答“有多少唯一身份访问者完成了结账”这个问题，应该怎么做？

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• 现在怎么样？下面的查询中添加了聚合函数 COUNT()：

#standardSQL SELECT COUNT(fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions`

• 下面的查询中添加了 GROUP BY 和 DISTINCT 语句：

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY hits_page_pageTitle

结果

很好！结果不错，但看起来有些奇怪。

• 过滤数据，让结果中只显示 “Checkout Confirmation” 的访问者数量：

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_page_pageTitle = "Checkout Confirmation" GROUP BY hits_page_pageTitle

点击检查我的进度，验证已完成以下目标： 确定完成结账的客户总数

任务 3. 列出在您的电子商务网站完成交易最多的城市

排查聚合后的排序、计算字段和过滤错误

1. 将未编写完全的查询补充完整：

SELECT geoNetwork_city, totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY

可能的解决方法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city

2. 更新上面的查询，将排名靠前的城市列在前面。

可能的解决方法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY distinct_visitors DESC

3. 更新查询，创建一个新的计算字段，按城市返回每个订单的平均商品数量。

可能的解决方法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

结果

过滤聚合结果，仅返回 avg_products_ordered 值大于 20 的城市。

• 下面的查询有什么问题？

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` WHERE avg_products_ordered > 20 GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

可能的解决方法：

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city HAVING avg_products_ordered > 20 ORDER BY avg_products_ordered DESC

点击检查我的进度，验证已完成以下目标： 列出在您的电子商务网站完成交易最多的城市

任务 4. 确定每个商品类别中的商品总数

使用 NULL 值过滤，确定畅销商品

1. 下面的查询有什么问题？该如何改正？

#standardSQL SELECT hits_product_v2ProductName, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY 1,2

2. 下面的查询有什么问题？

#standardSQL SELECT COUNT(hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC

3. 更新上面的查询以仅计算每个商品类别中不同的商品。

可能的解决方法：

#standardSQL SELECT COUNT(DISTINCT hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC LIMIT 5

注意：

(not set) 可能表示相应商品未设置类别

${productitem.product.origCatName} 是显示类别的前端代码，这可能表明 Google Analytics 跟踪脚本在页面完全显示之前就开始触发。

点击检查我的进度，验证已完成以下目标： 确定每个商品类别中的商品总数

恭喜！

您排查并改正了 BigQuery 标准 SQL 中无法正常运行的查询。记得使用查询验证器检查错误的查询语法，不过，即使查询可以成功执行，也要注意查询结果。

后续步骤/了解详情

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

Google Cloud 培训和认证

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

上次更新手册的时间：2024 年 1 月 19 日

上次测试实验的时间：2023 年 8 月 28 日

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