SQL 数据科学:了解和利用联接

2023-11-03 23:50

本文主要是介绍SQL 数据科学:了解和利用联接,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

推荐:使用 NSDT场景编辑器助你快速搭建可编辑的3D应用场景

什么是 SQL 中的连接?

SQL 联接允许您基于公共列合并来自多个数据库表的数据。这样,您就可以将信息合并在一起,并在相关数据集之间创建有意义的连接。

SQL 中的连接类型

有几种类型的 SQL 联接:

  • 内联接
  • 左外连接
  • 右外连接
  • 完全外部联接
  • 交叉连接

让我们解释每种类型。

SQL 内部联接

内部联接仅返回在要联接的两个表中存在匹配项的行。它基于共享键或列合并两个表中的行,丢弃不匹配的行。

我们通过以下方式对此进行可视化。

SQL 数据科学:了解和利用联接

在 SQL 中,这种类型的连接是使用关键字 JOIN 或 INNER JOIN 执行的。

SQL 左外部联接

左外连接返回左侧(或第一个)表中的所有行和右侧(或第二个)表中的匹配行。如果没有匹配项,则返回右侧表中列的 NULL 值。

我们可以这样想象它。

SQL 数据科学:了解和利用联接

如果要在 SQL 中使用此联接,可以使用 LEFT OUTER JOIN 或 LEFT JOIN 关键字来实现。这是一篇讨论左联接与左外联接的文章。

SQL 右外联接

右联接与左联接相反。它返回右侧表中的所有行和左侧表中的匹配行。如果没有匹配项,则返回左侧表中列的 NULL 值。

SQL 数据科学:了解和利用联接

在 SQL 中,此连接类型是使用关键字 RIGHT OUTER JOIN 或 RIGHT JOIN 执行的。

SQL 完全外部联接

完全外部联接返回两个表中的所有行,尽可能匹配行,并为不匹配的行填充 NULL 值。

SQL 数据科学:了解和利用联接

SQL 中此联接的关键字是“完全外部联接”或“完全联接”。

SQL 交叉联接

这种类型的联接将一个表中的所有行与第二个表中的所有行合并在一起。换句话说,它返回笛卡尔积,即两个表行的所有可能组合。

这是可视化效果,使其更容易理解。

SQL 数据科学:了解和利用联接

在 SQL 中交叉联接时,关键字是 CROSS JOIN。

了解 SQL 联接语法

要在 SQL 中执行联接,您需要指定要联接的表、用于匹配的列以及要执行的联接类型。在 SQL 中联接表的基本语法如下:

SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;

此示例演示如何使用 JOIN。

引用 FROM 子句中的第一个(或左侧)表。然后,使用 JOIN 跟随它并引用第二个(或右侧)表。

然后是 ON 子句中的连接条件。您可以在此处指定将用于联接两个表的列。通常,它是一个共享列,它是一个表中的主键和第二个表中的外键。

注意:主键是表中每条记录的唯一标识符。外键在两个表之间建立链接,即它是第二个表中引用第一个表的列。我们将在示例中向您展示这意味着什么。

如果你想使用左联接、右联接或完全联接,你只需使用这些关键字而不是 JOIN ——代码中的其他一切都完全相同!

交叉连接的情况略有不同。其性质是联接两个表中的所有行组合。这就是为什么不需要 ON 子句,语法如下所示。

SELECT columns
FROM table1
CROSS JOIN table2;

换句话说,您只需在 FROM 中引用一个表,在 CROSS JOIN 中引用第二个表。

或者,您可以在 FROM 中引用这两个表并用逗号分隔它们 - 这是 CROSS JOIN 的简写。

SELECT columns
FROM table1, table2;

自连接:SQL 中一种特殊类型的连接

还有一种连接表的特定方法 - 将表与自身连接。这也称为自联表。

它不完全是一种独特的联接类型,因为前面提到的任何联接类型也可用于自联接。

自联接的语法与我之前向您展示的语法类似。主要区别在于 FROM 和 JOIN 中引用了相同的表。

SELECT columns
FROM table1 t1
JOIN table1 t2
ON t1.column = t2.column;

此外,您需要为表提供两个别名以区分它们。您正在做的是将表与自身联接,并将其视为两个表。

我只是想在这里提到这一点,但我不会进一步详细介绍。如果您对自加入感兴趣,请参阅这本关于 SQL 中自加入的图解指南。

SQL 联接示例

是时候向您展示我提到的所有内容在实践中是如何工作的了。我将使用 StrataScratch 中的 SQL JOIN 面试问题来展示 SQL 中每种不同类型的连接。

1. 连接示例

Microsoft的这个问题希望您列出每个项目并按员工计算项目的预算。

昂贵的项目

“给定映射到每个项目的项目和员工列表,按分配给每个员工的项目预算金额计算。输出应包括项目标题和项目预算,四舍五入到最接近的整数。首先按每位员工预算最高的项目对列表进行排序。

数据

这个问题给出了两个表格。

ms_projects

编号:国际
标题:瓦尔查尔
预算:国际

ms_emp_projects

emp_id:国际
project_id:国际

现在,表 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() 函数来计算每个项目的员工人数。然后,我将每个项目的预算除以员工人数。我还将结果转换为十进制值并将其四舍五入到零小数位。

输出

下面是查询返回的内容。

SQL 数据科学:了解和利用联接

2. 左连接示例

让我们在Airbnb面试问题上练习这个加入。它希望您找到每个城市的订单数量、客户数量和订单总成本。

客户订单和详细信息

“查找每个城市的订单数量、客户数量和订单总成本。仅包括至少下了 5 个订单的城市,并计算每个城市的所有客户,即使他们没有下订单。

输出每个计算以及相应的城市名称。

数据

您将获得客户和订单的表格。

客户

编号:国际
first_name:瓦尔查尔
last_name:瓦尔查尔
城市:瓦尔查尔
地址:瓦尔查尔
phone_number:瓦尔查尔

订单

编号:国际
cust_id:国际
order_date:日期时间
order_details:瓦尔查尔
total_order_cost:国际

共享列是来自表客户的 id,cust_id来自表订单。我将使用这些列来联接表。

法典

以下是使用左联接解决此问题的方法。

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(这是我们的左表)中引用表客户,并在客户 ID 列上将其与订单左连接。

现在我可以选择城市,使用 COUNT() 按城市获取订单和客户数量,并使用 SUM() 按城市计算总订单成本。

为了按城市获得所有这些计算,我按城市对输出进行分组。

问题中还有一个额外的要求:“仅包括至少下了 5 个订单的城市......”我使用“必须”仅显示具有五个或更多订单的城市来实现此目的。

问题是,为什么我使用了 LEFT JOIN 而不是 JOIN?线索在问题中:“...并计算每个城市的所有客户,即使他们没有下订单。可能并非所有客户都下了订单。这意味着我想显示表客户中的所有客户,这完全符合左连接的定义。

如果我使用 JOIN,结果将是错误的,因为我会错过没有下任何订单的客户。

注意:SQL 中连接的复杂性并不反映在它们的语法上,而是反映在它们的语义上! 如您所见,每个联接的编写方式相同,只是关键字发生了变化。但是,每个联接的工作方式不同,因此可以根据数据输出不同的结果。因此,您必须完全了解每个联接的作用,并选择能够准确返回您想要的联接!

输出

现在,让我们看一下输出。

SQL 数据科学:了解和利用联接

3. 右连接示例

右联接是左联接的镜像。这就是为什么我可以使用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 时,我切换了表的顺序。现在,表订单变为左订单,表客户订单变为右侧订单。连接条件保持不变。我只是切换了列的顺序以反映表的顺序,但没有必要这样做。

通过切换表的顺序并使用 RIGHT JOIN,我将再次输出所有客户,即使他们没有下任何订单。

查询的其余部分与上一示例中相同。输出也是如此。

注意:在实践中,右联接相对较少使用。对于SQL用户来说,LEFT JOIN似乎更自然,因此他们更频繁地使用它。任何可以用 RIGHT JOIN 完成的事情也可以用 LEFT JOIN 完成。因此,没有特定情况可能首选 RIGHT JOIN。

输出

SQL 数据科学:了解和利用联接

4. 完全连接示例

Salesforce和特斯拉的问题希望你计算2020年推出的产品公司数量与前一年推出的产品公司数量之间的净差异。

新产品

“你会得到一个按公司按年份列出的产品发布表。编写一个查询来计算 2020 年推出的产品公司数量与上一年推出的产品公司数量之间的净差额。输出公司名称以及与上一年相比发布的2020年净产品净差额。

数据

该问题提供了一个包含以下列的表。

car_launches

年:国际
company_name:瓦尔查尔
product_name:瓦尔查尔

当只有一个表时,我将如何连接表?嗯,让我们也看看吧!

法典

这个查询有点复杂,所以我会逐渐揭示它。

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;

现在,我将把这些查询变成子查询,并使用完全外部联接来联接它们。

SELECT *
FROM(SELECT company_name,product_name AS brand_2020FROM car_launchesWHERE YEAR = 2020) a
FULL OUTER JOIN(SELECT company_name,product_name AS brand_2019FROM car_launchesWHERE YEAR = 2019) b 
ON a.company_name = b.company_name;

子查询可以被视为表,因此可以连接。我给第一个子查询一个别名,并将其放在 FROM 子句中。然后,我使用“完全外部联接”将其与公司名称列上的第二个子查询联接。

通过使用这种类型的 SQL 联接,我将在 2020 年的所有公司和产品与 2019 年的所有公司和产品合并。

SQL 数据科学:了解和利用联接

现在我可以完成我的查询了。让我们选择公司名称。此外,我将使用 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_2020FROM car_launchesWHERE YEAR = 2020) a
FULL OUTER JOIN(SELECT company_name,product_name AS brand_2019FROM car_launchesWHERE YEAR = 2019) b 
ON a.company_name = b.company_name
GROUP BY a.company_name
ORDER BY company_name;

输出

以下是 2020 年和 2019 年之间的公司列表和推出的产品差异。

SQL 数据科学:了解和利用联接

5. 交叉连接示例

德勤的这个问题非常适合展示CROSS JOIN的工作原理。

最多两个数字

“给定一列数字,考虑两个数字的所有可能排列,假设数字对(x,y)和(y,x)是两个不同的排列。然后,对于每个排列,找到两个数字中的最大值。

输出三列:第一列、第二个数字和两列中的最大值。

该问题希望您找到两个数字的所有可能排列,假设数字对 (x,y) 和 (y,x) 是两个不同的排列。然后,我们需要找到每个排列的最大值。

数据

这个问题给了我们一个带有一列的表格。

deloitte_numbers

数:国际

法典

此代码是 CROSS JOIN 的一个示例,也是自连接的示例。

SELECT dn1.number AS number1,dn2.number AS number2,CASEWHEN dn1.number > dn2.number THEN dn1.numberELSE dn2.numberEND AS max_number
FROM deloitte_numbers AS dn1
CROSS JOIN deloitte_numbers AS dn2;

我在 FROM 中引用该表并给它一个别名。然后,我通过在交叉连接后引用它并为表提供另一个别名来将其与自身交叉连接。

现在可以使用一个表,因为它们是两个。我从每个表中选择列号。然后,我使用 CASE 语句设置一个条件,该条件将显示两个数字的最大数量。

为什么在这里使用交叉连接?请记住,它是一种 SQL 联接类型,将显示所有表中所有行的所有组合。这正是问题要问的!

输出

这是所有组合的快照以及两者的较高数字。

SQL 数据科学:了解和利用联接

将 SQL 联接用于数据科学

现在您已经知道如何使用 SQL 联接,问题是如何在数据科学中利用这些知识。

SQL 联接在数据科学任务(如数据浏览、数据清理和特征工程)中起着至关重要的作用。

下面是如何利用 SQL 联接的几个示例:

  1. 合并数据:通过联接表,可以将不同的数据源汇集在一起,从而分析多个数据集之间的关系和相关性。例如,将客户表与交易表联接可以提供对客户行为和购买模式的见解。
  1. 数据验证:联接可用于验证数据质量和完整性。通过比较来自不同表的数据,可以识别不一致、缺失值或异常值。这有助于您进行数据清理,并确保用于分析的数据准确可靠。
  1. 特征工程:联接有助于为机器学习模型创建新功能。通过合并相关表,您可以提取有意义的信息并生成捕获数据中重要关系的特征。这可以增强模型的预测能力。
  1. 聚合和分析:联接使您能够跨多个表执行复杂的聚合和分析。通过组合来自各种来源的数据,您可以全面了解数据并获得有价值的见解。例如,将销售表与产品表联接可以帮助您按产品类别或区域分析销售业绩。

SQL 联接的最佳做法

正如我已经提到的,联接的复杂性并没有体现在它们的语法中。您看到语法相对简单。

联接的最佳实践也反映了这一点,因为它们不关心编码本身,而是联接的作用和性能。

若要充分利用 SQL 中的联接,请考虑以下最佳做法。

  1. 了解您的数据: 熟悉数据中的结构和关系。这将帮助您选择适当的联接类型,并选择正确的列进行匹配。
  1. 使用索引:如果表很大或经常联接,请考虑在用于联接的列上添加索引。索引可以显著提高查询性能。
  1. 注意性能:联接大型表或多个表的计算成本可能很高。通过筛选数据、使用适当的联接类型并考虑使用临时表或子查询来优化查询。
  1. 测试和验证:始终验证联接结果以确保正确性。执行健全性检查并验证联接的数据是否符合您的预期和业务逻辑。

结论

SQL 联接是一个基本概念,使数据科学家能够合并和分析来自多个源的数据。通过了解不同类型的 SQL 联接、掌握其语法并有效利用它们,数据科学家可以解锁有价值的见解、验证数据质量并推动数据驱动的决策。

我用五个例子向您展示了如何做到这一点。现在,您可以利用 SQL 的强大功能并加入您的数据科学项目并取得更好的结果。

原文链接:SQL 数据科学:了解和利用联接 (mvrlink.com)

这篇关于SQL 数据科学:了解和利用联接的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/341727

相关文章

大模型研发全揭秘:客服工单数据标注的完整攻略

在人工智能(AI)领域,数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者,掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中,工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注,不仅能够帮助提升客服自动化系统的智能化水平,还能优化客户服务流程,提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

关于数据埋点,你需要了解这些基本知识

产品汪每天都在和数据打交道,你知道数据来自哪里吗? 移动app端内的用户行为数据大多来自埋点,了解一些埋点知识,能和数据分析师、技术侃大山,参与到前期的数据采集,更重要是让最终的埋点数据能为我所用,否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式,可以区分为: 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理:自动程度高的,能解决通用统计,便于统一化管理,但个性化定

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

使用SecondaryNameNode恢复NameNode的数据

1)需求: NameNode进程挂了并且存储的数据也丢失了,如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2)故障模拟 (1)kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 (2)删除NameNode存储的数据(/opt/module/hadoop-3.1.4/data/tmp/dfs/na

异构存储(冷热数据分离)

异构存储主要解决不同的数据,存储在不同类型的硬盘中,达到最佳性能的问题。 异构存储Shell操作 (1)查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies (2)为指定路径(数据存储目录)设置指定的存储策略 hdfs storagepolicies -setStoragePo

Hadoop集群数据均衡之磁盘间数据均衡

生产环境,由于硬盘空间不足,往往需要增加一块硬盘。刚加载的硬盘没有数据时,可以执行磁盘数据均衡命令。(Hadoop3.x新特性) plan后面带的节点的名字必须是已经存在的,并且是需要均衡的节点。 如果节点不存在,会报如下错误: 如果节点只有一个硬盘的话,不会创建均衡计划: (1)生成均衡计划 hdfs diskbalancer -plan hadoop102 (2)执行均衡计划 hd

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间