GSP412

概览

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

通过联接数据表，您可以获得有关数据集的实用分析洞见。不过，在您联接数据时，有一些常见陷阱可能会导致您获得的结果有缺陷。本实验重点介绍如何避开这些陷阱。联接类型：

• 交叉联接：此联接类型会将第一个数据集的每一行与第二个数据集的每一行合并，并在输出中显示每个组合。
• 内联接：要求两个表中都存在键值，这样才会在结果表中显示记录。仅当两个表中的键值匹配时，才在合并中显示记录。
• 左联接：无论右表中是否存在匹配，左表中的每一行都会在结果中显示。
• 右联接：与左联接相反。无论左表中是否存在匹配，右表中的每一行都会在结果中显示。

如需详细了解联接，请参阅“联接”页面。

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

如需了解可帮助您跟踪和更新查询的语法信息，请参阅标准 SQL 查询语法。

您将执行的操作

在本实验中，您将学习如何完成以下操作：

• 使用 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 产品和服务，请点击导航菜单，或在搜索字段中输入服务或产品的名称。

打开 BigQuery 控制台

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

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

2. 点击完成。

BigQuery 控制台即会打开。

任务 1. 创建新数据集来存储表

在 BigQuery 项目中，创建一个名为 ecommerce 的新数据集。

1. 点击项目 ID 旁边的三点状图标，然后选择创建数据集。

创建数据集对话框将打开。

2. 将“数据集 ID”设置为 ecommerce。

3. 将其他选项保留为各自的默认值，然后点击创建数据集。

在左侧窗格中，您会看到您的项目下列出了 ecommerce 表。

点击检查我的进度，验证已完成以下目标：

创建新数据集

任务 2. 在 BigQuery 中固定实验项目

场景：您的团队为您提供了一个新数据集，其中包含在您电子商务网站上销售的每种商品的存货水平。您想要了解网站上的哪些商品和字段可用于联接到其他数据集。

具有新数据集的项目是 data-to-insights。

1. 在 Google Cloud 控制台中，点击导航菜单 () 中的 BigQuery。

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

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

2. 点击完成。

3. 默认情况下不会显示 BigQuery 公共数据集。如需打开公共数据集项目，请复制 data-to-insights（以便在下一步中粘贴到对话框中）。

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

5. 点击加星标。

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

任务 3. 检查字段

接下来，了解网站上的哪些商品和字段可用于创建查询，分析数据集。

1. 在包含资源的左侧窗格中，前往 data-to-insights > ecommerce > all_sessions_raw。

2. 在右侧的查询编辑器下，点击架构标签页，查看各个字段及其相关信息。

任务 4. 识别电子商务数据集中的键字段

进一步检查商品和字段。您想要了解网站上的哪些商品和字段可用于联接到其他数据集。

检查记录

在本部分中，您将了解您网站上的商品名称和商品 SKU 数量，以及其中任一字段是否唯一。

1. 了解网站上的商品名称和商品 SKU 数量。复制以下查询并将其粘贴到 BigQuery 编辑器中：

# 标准 SQL # 该网站上有多少种商品？ SELECT DISTINCT productSKU, v2ProductName FROM `data-to-insights.ecommerce.all_sessions_raw`

2. 点击运行。

查看控制台中的分页结果，了解返回的记录总数。

不过…结果是否意味着有这么多唯一的商品 SKU？作为一名数据分析师，您首先要运行的查询之一就是查看数据值的唯一性。

3. 清除之前的查询，然后使用 DISTINCT 运行以下查询，列出不重复的 SKU 的数量：

# 标准 SQL # 找出唯一 SKU 的数量 SELECT DISTINCT productSKU FROM `data-to-insights.ecommerce.all_sessions_raw`

检查 SKU 和名称之间的关系

接下来，确定哪些商品具有多个 SKU，以及哪些 SKU 对应多个商品名称。

1. 清除之前的查询，然后运行以下查询，确定某些商品名称是否具有多个 SKU。使用 STRING_AGG() 函数来汇总与一个商品名称相关联的所有商品 SKU，并以逗号分隔值形式显示。

SELECT v2ProductName, COUNT(DISTINCT productSKU) AS SKU_count, STRING_AGG(DISTINCT productSKU LIMIT 5) AS SKU FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU IS NOT NULL GROUP BY v2ProductName HAVING SKU_count > 1 ORDER BY SKU_count DESC

2. 点击运行。

结果：

电子商务网站目录显示，每个商品名称可能包含多个选项（尺寸、颜色），其中每个选项均以单独的 SKU 销售。

如您所见，1 个商品可以有 12 个 SKU。那么，1 个 SKU 是否可以属于多个商品呢？

• 要弄清楚这个问题，请清除之前的查询，然后运行下面的查询：

SELECT productSKU, COUNT(DISTINCT v2ProductName) AS product_count, STRING_AGG(DISTINCT v2ProductName LIMIT 5) AS product_name FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE v2ProductName IS NOT NULL GROUP BY productSKU HAVING product_count > 1 ORDER BY product_count DESC

注意：请尝试将 STRING_AGG() 替换为 ARRAY_AGG()。很酷吧？BigQuery 原生支持嵌套的数组值。如需了解详情，可参阅数组使用指南。

在下一部分中，您将了解为何这种多对多的数据关系会成为问题。

点击检查我的进度，验证已完成以下目标：

识别电子商务数据集中的键字段

任务 5. 陷阱：非唯一键

在库存跟踪中，SKU 旨在唯一标识一种（且仅限一种）商品。对于我们来说，在从其他表中查找信息时，SKU 将是 JOIN 条件的基础。您将会看到，使用非唯一键可能会导致出现严重的数据问题。

1. 编写查询，识别 SKU 'GGOEGPJC019099' 对应的所有商品名称。

可能的解决方法：

SELECT DISTINCT v2ProductName, productSKU FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGPJC019099'

2. 点击运行。

v2ProductName	productSKU
7" Dog Frisbee	GGOEGPJC019099
7" Dog Frisbee	GGOEGPJC019099
Google 7-inch Dog Flying Disc Blue	GGOEGPJC019099

从查询结果来看，同一个商品有三个不同的名称。在此示例中，其中一个名称包含一个特殊字符，另一个名称则略有不同：

将网站数据与商品库存清单联接到一起

接下来，我们来看看当单个 SKU 对应多个商品时，联接数据集会造成怎样的影响。首先查看商品库存数据集（products 表），确定此 SKU 在数据集中是否唯一。

• 清除之前的查询，然后运行下面的查询：

SELECT SKU, name, stockLevel FROM `data-to-insights.ecommerce.products` WHERE SKU = 'GGOEGPJC019099'

联接陷阱：意外的多对一 SKU 关系

您现在有两个数据集：一个是商品库存存货水平数据集，另一个是网站分析数据集。使用 JOIN 函数，将库存数据集与网站上的商品名称和 SKU 联接起来，这样您就可以获得与网站上销售的每个商品相关联的商品库存存货水平。

1. 清除之前的查询，然后运行下面的查询：

SELECT DISTINCT website.v2ProductName, website.productSKU, inventory.stockLevel FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE productSKU = 'GGOEGPJC019099'

接下来，扩展之前的查询，使用简单的 SUM 函数计算各项商品的现有库存量。

2. 清除之前的查询，然后运行下面的查询：

WITH inventory_per_sku AS ( SELECT DISTINCT website.v2ProductName, website.productSKU, inventory.stockLevel FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE productSKU = 'GGOEGPJC019099' ) SELECT productSKU, SUM(stockLevel) AS total_inventory FROM inventory_per_sku GROUP BY productSKU

不会吧！结果为 154 x 3 = 462，也就是将库存算成了三倍！这叫做意外交叉联接（稍后将探讨这个话题）。

点击检查我的进度，验证已完成以下目标：

陷阱：非唯一键

任务 6. 联接陷阱解决方法：在联接前使用唯一的 SKU

有哪些方法可以解决三重计数这一难题？首先，您需要仅选择网站上的唯一 SKU，然后再联接到其他数据集。

您知道，单个 SKU 可能对应多个商品名称（例如 7" Dog Frisbee）。

1. 将所有可能的名称汇集到一个数组中：

SELECT productSKU, ARRAY_AGG(DISTINCT v2ProductName) AS push_all_names_into_array FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGAAX0098' GROUP BY productSKU

现在，数据行中显示的是每个唯一 SKU，而不是各个商品名称。

2. 如果您想对商品名称进行去重，甚至可以使用 LIMIT 函数来限制数组，如下所示：

SELECT productSKU, ARRAY_AGG(DISTINCT v2ProductName LIMIT 1) AS push_all_names_into_array FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE productSKU = 'GGOEGAAX0098' GROUP BY productSKU

联接陷阱：联接后数据记录丢失

现在，您可以再次联接商品库存数据集。

1. 清除之前的查询，然后运行下面的查询：

#standardSQL SELECT DISTINCT website.productSKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU

在联接数据集后，似乎丢失了 819 个 SKU。通过在字段中添加更具体的内容（每个数据集中的一个 SKU 列）来进行调查：

2. 清除之前的查询，然后运行下面的查询：

#standardSQL # pull ID fields from both tables SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # IDs are present in both tables, how can you dig deeper?

联接这 1,090 条记录后，SKU 似乎同时出现在这两个数据集中。如何找到缺少的记录？

联接陷阱解决方法：选择正确的联接类型并使用 NULL 函数进行过滤

默认的 JOIN 类型为 INNER JOIN（内联接），这意味着只有在联接的左表和右表中都有 SKU 匹配项时，系统才会返回记录。

1. 重写之前的查询以使用不同的联接类型，纳入网站表中的所有记录，无论是否有匹配的商品库存 SKU 记录。可用的联接类型包括：INNER JOIN（内联接）、LEFT JOIN（左联接）、RIGHT JOIN（右联接）、FULL JOIN（全联接）、CROSS JOIN（交叉联接）。

可能的解决方法：

#standardSQL # the secret is in the JOIN type # pull ID fields from both tables SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU

2. 点击运行。

您已成功使用 LEFT JOIN（左联接）在结果中返回网站上的全部 1,909 个原始 SKU。

商品库存集内缺少多少个 SKU？

1. 编写查询，过滤库存表中的 NULL 值。

可能的解决方法：

#standardSQL # find product SKUs in website table but not in product inventory table SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE inventory.SKU IS NULL

2. 点击运行。

问题：缺少多少个商品？

回答：商品库存数据集内缺少 819 个商品（SKU 为空）。

• 清除之前的查询，然后运行下面的查询，确认是否使用了网站数据集内的任一特定 SKU：

#standardSQL # you can even pick one and confirm SELECT * FROM `data-to-insights.ecommerce.products` WHERE SKU = 'GGOEGATJ060517' # query returns zero results

那么，如果情况相反会怎样呢？是否有任何商品位于商品库存数据集中，但未在网站上显示？

1. 使用不同的联接类型编写查询以进行调查。

可能的解决方法：

#standardSQL # reverse the join # find records in website but not in inventory SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website RIGHT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL

2. 点击运行。

回答：是的。网站数据集内缺少两个商品 SKU

接下来，添加商品库存数据集中的更多字段，了解更多详情。

• 清除之前的查询，然后运行下面的查询：

# 标准 SQL # 这些商品是什么？ # 在 SELECT 语句中添加更多字段 SELECT DISTINCT website.productSKU AS website_SKU, inventory.* FROM `data-to-insights.ecommerce.all_sessions_raw` AS website RIGHT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL

为何电子商务网站数据集内缺少以下商品？

website_SKU	SKU	name	orderedQuantity	stockLevel	restockingLeadTime	sentimentScore	sentimentMagnitude
null	GGOBJGOWUSG69402	USB wired soundbar - in store only	10	15	2	1.0	1.0
null	GGADFBSBKS42347	PC gaming speakers	0	100	1	null	null

可能的回答：

• 其中一项是新商品（订单数为 0，且无 sentimentScore），另一项商品“仅限实体店内销售”
• 另一项是订单数为 0 的新商品

为何新商品未出现在网站数据集中？

• 网站数据集记录的是以往的客户订单交易数据，尚未售出的全新商品只有在有人浏览或购买后，才会显示在网站分析数据集中。

注意：在生产环境中执行查询时，您通常不会用到 RIGHT JOIN（右联接）。您只需使用 LEFT JOIN（左联接），然后切换表的排序即可。

如果您想要列出网站或库存数据集中缺少的所有商品，该如何执行查询？

1. 使用不同的联接类型编写查询。

可能的解决方法：

# 标准 SQL SELECT DISTINCT website.productSKU AS website_SKU, inventory.SKU AS inventory_SKU FROM `data-to-insights.ecommerce.all_sessions_raw` AS website FULL JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU WHERE website.productSKU IS NULL OR inventory.SKU IS NULL

2. 点击运行。

结果是 819 + 2 = 821 个商品 SKU。

LEFT JOIN（左联接）+ RIGHT JOIN（右联接）= FULL JOIN（全联接），这表示无论是否有匹配的联接键，都会返回两个表中的所有记录。然后，您只需过滤掉两侧表中不匹配的项目即可

联接陷阱：意外交叉联接

不了解数据表键之间的关系（1 对 1、1 对多、多对多）可能会返回意外结果，也会大大降低查询性能。

最后一种联接类型是 CROSS JOIN（交叉联接）。

创建一个新表，记录要针对“清仓”类别的商品应用的网站级折扣百分比。

1. 清除之前的查询，然后运行下面的查询：

# 标准 SQL CREATE OR REPLACE TABLE ecommerce.site_wide_promotion AS SELECT .05 AS discount;

在左侧窗格中，site_wide_promotion 现已在项目和数据集下的“资源”部分中列出。

2. 清除之前的查询，然后运行下面的查询，确定清仓商品的数量：

SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%'

注意：使用 CROSS JOIN（交叉联接）时，您会发现没有联接条件（例如 ON 或 USING）。相应字段只是与第一个数据集相乘，为所有商品应用 5% 的折扣。

查看在折扣表中意外添加多条记录的影响。

3. 清除之前的查询，然后运行下面的查询，在促销表中再多插入两条记录：

INSERT INTO ecommerce.site_wide_promotion (discount) VALUES (.04), (.03);

接下来，查看促销表中的数据值。

4. 清除之前的查询，然后运行下面的查询：

SELECT discount FROM ecommerce.site_wide_promotion

系统返回了多少条记录？

回答：3 条

将折扣再次应用到全部 82 个清仓商品后，会发生什么情况？

5. 清除之前的查询，然后运行下面的查询：

SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%'

系统返回了多少个商品？

回答：目前返回了 246 个商品，而不是 82 个商品。返回的记录数量比一开始使用的原始表中的数量要多。

接下来，通过检查一个商品 SKU 来调查根本原因。

6. 清除之前的查询，然后运行下面的查询：

# 标准 SQL SELECT DISTINCT productSKU, v2ProductCategory, discount FROM `data-to-insights.ecommerce.all_sessions_raw` AS website CROSS JOIN ecommerce.site_wide_promotion WHERE v2ProductCategory LIKE '%Clearance%' AND productSKU = 'GGOEGOLC013299'

CROSS JOIN（交叉联接）有何影响？

回答：由于交叉联接的折扣代码有 3 个，意味着会将原始数据集乘以 3。

注意：并非只有交叉联接会有这种表现。如果数据关系为多对多，即使是一般联接也可能意外变成交叉联接，这很容易导致意外返回数百万、甚至是数十亿条记录。

解决方法是在联接之前先了解数据关系，并且不要假设键是唯一的。

点击检查我的进度，验证已完成以下目标：

联接陷阱解决方法

恭喜！

您已顺利完成了本实验，并了解了如何识别重复的记录，以及何时使用各种类型的 JOIN，从而避开一些严重的 SQL 联接陷阱。太棒了！

后续步骤/了解详情

• 已拥有 Google Analytics 账号，想要在 BigQuery 中查询您自己的数据集？请按照此导出指南中的说明操作。
• 详细了解有关为优化查询计算提供指导的最佳实践。
• 如果您想要进一步练习 SQL JOIN 语法，请参阅 BigQuery JOIN 文档。
• 请参阅以下实验：
  • 在 BigQuery 中使用 JSON、数组和结构体
  • 适用于 BigQuery 和 Cloud SQL 的 SQL 简介
  • 使用 BigQuery ML 预测模型来预测出租车费

Google Cloud 培训和认证

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

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

上次测试实验的时间：2023 年 9 月 20 日

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