数据科学中的SQL：理解和利用连接
数据科学中的SQL连接
数据科学是一个跨学科领域，它严重依赖于从大量数据中提取见解并做出明智决策。数据科学家工具箱中的一个基本工具是SQL（结构化查询语言），这是一种专为管理和操作关系型数据库而设计的编程语言。
在本文中，我将重点介绍SQL中最强大的特性之一：连接。
SQL中的连接是什么？
SQL连接允许您根据共同的列将来自多个数据库表的数据合并在一起。这样，您可以将信息合并在一起并创建相关数据集之间的有意义的连接。
SQL中的连接类型
有几种类型的SQL连接：
- 内连接
- 左外连接
- 右外连接
- 全外连接
- 交叉连接
让我们解释每种类型。
SQL内连接
内连接仅返回两个被连接表中存在匹配的行。它基于共享的关键字或列，将两个表的行合并在一起，丢弃不匹配的行。
我们可以用以下方式可视化这个过程。 
在SQL中，可以使用JOIN或INNER JOIN关键字执行这种类型的连接。
SQL左外连接
左外连接返回左（或第一个）表中的所有行和右（或第二个）表中的匹配行。如果没有匹配，它将为右表的列返回NULL值。
我们可以这样可视化它。
当想要在SQL中使用这种连接时，可以使用LEFT OUTER JOIN或LEFT JOIN关键字。这是一篇讨论左连接与左外连接的文章。
SQL右外连接
右连接是左连接的相反。它返回右表中的所有行和左表中的匹配行。如果没有匹配，它将为左表的列返回NULL值。
在SQL中，可以使用RIGHT OUTER JOIN或RIGHT JOIN关键字执行此连接类型。
SQL全外连接
全外连接返回两个表中的所有行，尽可能匹配行，并为不匹配的行填充NULL值。
在SQL中，这种连接的关键字是FULL OUTER JOIN或FULL JOIN。
SQL交叉连接
这种类型的连接将第一个表的所有行与第二个表的所有行组合在一起。换句话说，它返回笛卡尔积，即两个表行的所有可能组合。
这是一个可视化示例，可以更容易地理解。
在SQL中，交叉连接的关键字是CROSS JOIN。
理解SQL连接语法
在SQL中执行JOIN操作，我们需要指定要连接的表、用于匹配的列以及要执行的连接类型。SQL中连接表的基本语法如下：
SELECT 列名
FROM 表名1
JOIN 表名2
ON 表名1.列名 = 表名2.列名;
这个例子展示了如何使用JOIN。
在FROM子句中引用第一个（或左）表，然后使用JOIN引用第二个（或右）表。
然后在ON子句中指定连接条件。这是您指定要使用哪些列来连接两个表的地方。通常，这是一个在一个表中是主键并在第二个表中是外键的共享列。
注意：主键是表中每个记录的唯一标识符。外键建立两个表之间的链接，即它是第二个表中引用第一个表的列。我们将在示例中向您展示这是什么意思。
如果您想使用LEFT JOIN、RIGHT JOIN或FULL JOIN，只需使用这些关键字替换JOIN – 代码中的其他内容完全相同！
CROSS JOIN与其他情况略有不同。它的本质是将两个表的所有行组合在一起进行连接。这就是为什么不需要ON子句，并且语法如下。
SELECT 列名
FROM 表名1
CROSS JOIN 表名2;
换句话说，您只需在FROM子句中引用一个表，然后在CROSS JOIN中引用第二个表。
或者，您可以在FROM中引用两个表，并用逗号分隔它们 – 这是CROSS JOIN的简写形式。
SELECT 列名
FROM 表名1, 表名2;
自连接：SQL中的一种特殊连接类型
还有一种特殊的连接方式 – 将表与自身连接。这也被称为自连接表。
它并不是一种独特的连接类型，因为前面提到的任何一种连接类型都可以用于自连接。
自连接的语法与之前向您展示的相似。主要区别是在FROM和JOIN中引用同一个表。
SELECT 列名
FROM 表名1 t1
JOIN 表名1 t2
ON t1.列名 = t2.列名;
此外，您需要为表指定两个别名以区分它们。您所做的是将表与自身连接，并将其视为两个表。
我只是想在这里提及一下，但我不会进一步详细介绍。如果您对自连接感兴趣，请参阅有关SQL中自连接的详细指南。
SQL连接示例
现在是时候向您展示我之前提到的所有内容在实践中是如何工作的了。我将使用StrataScratch的SQL JOIN面试题展示SQL中每种不同类型的连接。
1. JOIN示例
这个由Microsoft提出的问题要求您列出每个项目并计算每个项目按员工分配的预算。
高成本项目
“给定一个项目和员工映射到每个项目的列表，按每个员工分配的项目预算金额计算。输出应包括项目标题和最接近的整数项目预算。将列表按每个员工的最高预算项目排在前面。”
数据
该问题给出了两个表。
ms_projects
| id： | int |
| title： | varchar |
| budget： | int |
ms_emp_projects
| emp_id： | int |
| project_id： | int |
现在，表 ms_projects 中的列 id 是该表的主键。在表 ms_emp_projects 中可以找到相同的列，只是名称不同：project_id。这是该表的外键，引用了第一个表。
我将使用这两个列来连接我的解决方案中的表。
代码
SELECT title AS project,
ROUND((budget/COUNT(emp_id)::FLOAT)::NUMERIC, 0) AS budget_emp_ratio
FROM ms_projects a
JOIN ms_emp_projects b
ON a.id = b.project_id
GROUP BY title, budget
ORDER BY budget_emp_ratio DESC;我使用 JOIN 连接了这两个表。表 ms_projects 在 FROM 中被引用，而 ms_emp_projects 在 JOIN 之后被引用。我给这两个表起了一个别名，这样以后就不需要使用表的长名称。
现在，我需要指定要连接表的列。我已经提到了哪些列是一个表的主键，哪些列是另一个表的外键，所以我在这里使用它们。
我将这两个列相等，因为我想获取所有项目 ID 相同的数据。我还在每个列前使用了表的别名。
现在，我可以在 SELECT 中列出两个表中的数据。第一列是项目名称，第二列是计算得到的结果。
这个计算使用 COUNT() 函数来计算每个项目的员工数量。然后，我将每个项目的预算除以员工数量。我还将结果转换为小数值，并将小数位数四舍五入为零位。
输出
这是查询的返回结果。
2. LEFT JOIN 示例
让我们在 Airbnb 面试问题中练习这个连接。它要求你找到每个城市的订单数量、客户数量和订单总成本。
Customer Orders and Details
“找到每个城市的订单数量、客户数量和订单总成本。只包括至少有 5 个订单的城市，并计算每个城市的所有客户数量，即使他们没有下订单。
输出每个计算结果以及相应的城市名称。”
数据
给定的表是 customers 和 orders。
customers
| id： | int |
| first_name： | varchar |
| last_name： | varchar |
| city： | varchar |
| address： | varchar |
| phone_number： | varchar |
orders
| id： | int |
| cust_id： | int |
| order_date： | datetime |
| order_details： | varchar |
| total_order_cost： | int |
共享的列是来自表 customers 的id和来自表 orders 的cust_id。我将使用这些列来连接这些表。
代码
以下是使用LEFT JOIN解决此问题的方法。
SELECT c.city,
COUNT(DISTINCT o.id) AS orders_per_city,
COUNT(DISTINCT c.id) AS customers_per_city,
SUM(o.total_order_cost) AS orders_cost_per_city
FROM customers c
LEFT JOIN orders o ON c.id = o.cust_id
GROUP BY c.city
HAVING COUNT(o.id) >=5;
我在FROM中引用表 customers （这是我们的左表），并在客户ID列上使用LEFT JOIN与 orders 连接它。
现在，我可以选择城市，使用COUNT()获取每个城市的订单和客户数量，并使用SUM()计算每个城市的总订单成本。
为了按城市分组输出所有这些计算结果，我将输出按城市分组。
问题中还有一个额外的要求：“只包括至少有5个订单的城市…”我使用HAVING来只显示具有五个或更多订单的城市来实现这一目标。
问题是，为什么我使用 LEFT JOIN 而不是 JOIN？ 答案在问题中：“…并计算每个城市的所有客户数量，即使他们没有下订单。”并非所有客户都下了订单。这意味着我想显示表 customers 中的所有客户，这完全符合LEFT JOIN的定义。
如果我使用JOIN，结果将是错误的，因为我会错过没有下订单的客户。
注意：在SQL中，连接的复杂性不体现在语法上，而体现在语义上！正如你所看到的，每个连接的写法都是一样的，只是关键字不同。然而，每个连接的工作方式不同，因此根据数据可能会输出不同的结果。因此，重要的是你充分理解每个连接的作用并选择返回你想要的结果的连接！
输出
现在，让我们来看一下输出。
3. RIGHT JOIN示例
RIGHT JOIN是LEFT JOIN的镜像。这就是为什么我可以使用RIGHT JOIN轻松解决前面的问题。让我向你展示如何做到这一点。
数据
表保持不变；我只是使用了不同类型的连接。
代码
SELECT c.city,
COUNT(DISTINCT o.id) AS orders_per_city,
COUNT(DISTINCT c.id) AS customers_per_city,
SUM(o.total_order_cost) AS orders_cost_per_city
FROM orders o
RIGHT JOIN customers c ON o.cust_id = c.id
GROUP BY c.city
HAVING COUNT(o.id) >=5;
以下是变化的部分。由于我使用了RIGHT JOIN，我交换了表的顺序。现在，表 orders 成为左表，表 customers 成为右表。连接条件保持不变。我只是交换了列的顺序以反映表的顺序，但这并不是必要的。
通过交换表的顺序并使用RIGHT JOIN，我再次会输出所有的客户，即使他们没有下订单。
查询的其余部分与前一个示例相同。输出也是一样的。
注意：在实践中， RIGHT JOIN相对较少使用。 LEFT JOIN对于SQL用户来说更自然，所以他们更常使用它。任何可以使用RIGHT JOIN完成的任务也可以使用LEFT JOIN完成。因此，没有特定的情况下可能更适用RIGHT JOIN。
输出
4. FULL JOIN示例
Salesforce和Tesla提出的问题要求你计算2020年公司推出的产品数量与上一年度公司推出的产品数量之间的净差。
新产品
“你有一个按年份和公司分类的产品发布表格。编写一个查询来计算2020年公司推出的产品数量与上一年公司推出的产品数量之间的净差。输出公司名称和2020年相对于上一年度的净产品发布差值。”
数据
该问题提供了一个具有以下列的表格。
car_launches
| year: | int |
| company_name: | varchar |
| product_name: | varchar |
当只有一个表格时，我该如何连接表格呢？嗯，我们也来看看这个！
代码
这个查询有点复杂，所以我将逐步揭示它。
SELECT company_name,
product_name AS brand_2020
FROM car_launches
WHERE YEAR = 2020;
第一个SELECT语句找到2020年的公司和产品名称。这个查询后来将被转换为子查询。
问题要求你找到2020年和2019年之间的差异。所以让我们写相同的查询，但是针对2019年。
SELECT company_name,
product_name AS brand_2019
FROM car_launches
WHERE YEAR = 2019;
现在我将这些查询转换为子查询，并使用FULL OUTER JOIN将它们连接在一起。
SELECT *
FROM
(SELECT company_name,
product_name AS brand_2020
FROM car_launches
WHERE YEAR = 2020) a
FULL OUTER JOIN
(SELECT company_name,
product_name AS brand_2019
FROM car_launches
WHERE YEAR = 2019) b
ON a.company_name = b.company_name;
子查询可以被视为表格，因此可以进行连接。我给第一个子查询起了一个别名，并将其放在FROM子句中。然后我使用FULL OUTER JOIN在公司名称列上将其与第二个子查询连接起来。
通过使用这种类型的SQL连接，我将获得2020年所有公司和产品与2019年所有公司和产品的合并。
现在我可以完成我的查询了。让我们选择公司名称。此外，我将使用COUNT()函数来查找每年推出的产品数量，然后进行相减以获得差异。最后，我将按公司名称对输出进行分组，并按公司名称字母顺序排序。
以下是完整的查询。
SELECT a.company_name,
(COUNT(DISTINCT a.brand_2020)-COUNT(DISTINCT b.brand_2019)) AS net_products
FROM
(SELECT company_name,
product_name AS brand_2020
FROM car_launches
WHERE YEAR = 2020) a
FULL OUTER JOIN
(SELECT company_name,
product_name AS brand_2019
FROM car_launches
WHERE YEAR = 2019) b
ON a.company_name = b.company_name
GROUP BY a.company_name
ORDER BY company_name;
输出
以下是2020年和2019年之间公司和发布产品的差异列表。
5. CROSS JOIN 示例
德勤公司的这个问题非常适合展示CROSS JOIN的工作原理。
两个数中的最大值
“给定一列数字，假设(x,y)和(y,x)是两个不同的排列，找出每个排列中的两个数字的最大值。
输出三列：第一个数字，第二个数字和两个数字的最大值。”
这个问题要求你找出假设(x,y)和(y,x)是两个不同的排列的所有可能排列。然后，我们需要找出每个排列的最大值。
数据
这个问题给了我们一个只有一列的表。
deloitte_numbers
| number: | int |
代码
这段代码是CROSS JOIN的一个示例，也是自连接的一个示例。
SELECT dn1.number AS number1,
dn2.number AS number2,
CASE
WHEN dn1.number > dn2.number THEN dn1.number
ELSE dn2.number
END AS max_number
FROM deloitte_numbers AS dn1
CROSS JOIN deloitte_numbers AS dn2;
我在FROM中引用了表，并给它起了一个别名。然后，我通过在CROSS JOIN之后引用它并给表起一个新的别名，将其与自身进行了CROSS JOIN。
现在可以将一个表用作两个表。我从每个表中选择了列号。然后，我使用CASE语句设置一个条件，显示两个数字中的最大数。
为什么要在这里使用CROSS JOIN？记住，它是一种SQL连接类型，将显示所有表中所有行的所有组合。这正是问题所要求的！
输出
以下是所有组合和两个数字中较大的数字的快照。
利用SQL连接进行数据科学
现在你知道如何使用SQL连接，问题是如何在数据科学中利用这些知识。
SQL连接在数据科学任务中起着至关重要的作用，如数据探索、数据清洗和特征工程。
以下是一些利用SQL连接的示例：
- 合并数据：连接表使您能够将不同来源的数据汇集在一起，从而可以分析多个数据集之间的关系和相关性。例如，将客户表与交易表连接在一起可以提供有关客户行为和购买模式的见解。
- 数据验证：可以使用连接来验证数据的质量和完整性。通过比较不同表中的数据，可以识别出不一致、缺失值或异常值。这有助于数据清洗，并确保用于分析的数据准确可靠。
- 特征工程：连接在创建机器学习模型的新特征方面发挥着重要作用。通过合并相关表，可以提取有意义的信息，并生成捕捉数据内重要关系的特征。这可以增强模型的预测能力。
- 聚合和分析：连接使您能够在多个表之间执行复杂的聚合和分析操作。通过组合来自各种来源的数据，可以获得对数据的全面视图，并得出有价值的见解。例如，将销售表与产品表连接可以帮助您分析产品类别或地区的销售业绩。
SQL连接的最佳实践
正如我已经提到的，连接的复杂性并不体现在其语法上。你已经看到语法相对简单。
连接的最佳实践也反映了这一点，因为它们不关注编码本身，而是关注连接的作用和性能。
为了充分利用SQL中的连接，请考虑以下最佳实践。
- 了解你的数据：熟悉数据的结构和关系。这将帮助你选择合适的连接类型，并选择正确的匹配列。
- 使用索引：如果你的表很大或经常进行连接，请考虑在用于连接的列上添加索引。索引可以显著提高查询性能。
- 注意性能问题：连接大表或多个表可能会消耗大量计算资源。通过过滤数据、使用合适的连接类型以及考虑使用临时表或子查询来优化查询。
- 测试和验证：始终验证连接结果以确保正确性。进行合理性检查，并验证连接的数据是否符合你的期望和业务逻辑。
结论
SQL连接是一个基本概念，它使你作为数据科学家能够合并和分析来自多个来源的数据。通过了解不同类型的SQL连接，掌握它们的语法，并有效地利用它们，数据科学家可以获得有价值的洞见，验证数据质量，并推动基于数据的决策。
我在五个示例中向你展示了如何做到这一点。现在轮到你利用SQL和连接的强大功能来完成你的数据科学项目，并取得更好的结果了。 Nate Rosidi 是一位数据科学家和产品策略家。他还是一位兼职教授，教授分析学，并且是StrataScratch的创始人，这是一个帮助数据科学家通过来自顶级公司的真实面试问题准备面试的平台。与他在Twitter上联系：StrataScratch或LinkedIn。
