c

codeye

V1

2022/09/24阅读:8主题:默认主题

在SQL中寻找唯一记录的3种终极方法

在SQL中寻找唯一记录的3种终极方法 停止使用DISTINCT! 开始使用这些快速替代方法,以避免混淆!

照片:Luis Cortes on Unsplash 不使用DISTINCT关键字就能获得唯一记录! 🏆

在你的数据分析项目中,只要你需要从数据库中提取唯一记录,一个简单的答案就是使用DISTINCT!

毫无疑问,DISTINCT是用来返回唯一记录的,它的工作做得很好。但是,它不会告诉你,你使用的JOIN和过滤器是否正确,这实际上是造成重复的原因。

因此,我总结了3个最好的、安全的、节省时间的替代方法,它们返回与DISTINCT相同的输出,并且仍然保持代码的清洁和易于维护。💯

你可以使用下面的索引跳到你喜欢的部分。

  • UNION()
  • INTERSECT()
  • ROW_NUMBER()
  • GROUP BY

📍注意:我使用的是SQLite DB浏览器和一个自己创建的Dummy_Employees,你可以在我的Github repo上免费获得!在这里,我们开始吧。

好了,我们开始吧...🚀

首先,让我向你展示一下数据的样子。

虚假雇员数据集|图片来自作者 这是一个简单的10x4数据集,是我在《Faker》一文中创建的。一个惊人的、非常有用的Python库。📚

上图中用蓝色和红色突出显示的行在数据集中是重复的。

📚 你可以使用这个SQLite数据库来跟随本文的查询。

正如我在上一篇文章《2022年你应该知道的5个实用SQL查询》中提到的,在寻找唯一记录之前,你必须定义哪一列或哪几列组合构成唯一行。

对于寻找单一列中的唯一值,DISTINCT总是更方便。然而,对于从数据集中检索唯一行,这些替代方法可以保持代码的简洁和高效。

例如,让我们使用DISTINCT从数据集中获取唯一的employee_id, employee_name和department的组合。

SELECT DISTINCT employee_id,
                employee_name,
                department
FROM Dummy_employees

使用DISTINCT选择唯一的值 | 作者的图片 正如预期的那样,它只返回了一个属于employee_id 102和212的重复记录,最终得到了8条记录。

现在,让我们看看如何在不完全使用DISTINCT的情况下获得完全相同的结果。

UNION() 在SQL中,UNION是一个操作符,用来合并两个SELECT语句的结果。它类似于对集合的UNION操作。

此外,它还可以删除在结果数据集中出现的多条记录,只保留每条记录的单次出现。✅

你只需要写两个完全相同的SELECT语句,然后用操作符UNION连接它们,如下所示。


SELECT employee_id,
       employee_name,
       department
FROM Dummy_employees
UNION
SELECT employee_id,
       employee_name,
       department
FROM Dummy_employees

在不使用DISTINCT的情况下选择唯一的记录 | 作者的图片 这显示了与你用DISTINCT得到的相同的输出,只是记录的顺序不同。

现在,让我告诉你在后端发生了什么。

UNION在SQL中是如何去除重复记录的? 在这种情况下,UNION只是将两个独立的SELECT语句的输出连接起来,并且只保留一个重复的记录。

下一个有趣的选择唯一记录的方法是使用另一个运算符 - INTERSECT。

INTERSECT() 与前一个操作符类似,INTERSECT也被用来连接两个SELECT查询的结果,并且只返回两个SELECT查询输出中的共同记录。它与两个集合的交叉点相同。

INTERSECT也删除了在结果数据集中多次出现的记录,只保留每条记录的单次出现。✅

你只需要写两个完全相同的SELECT语句,然后用INTERSECT连接它们,如下所示。

SELECT employee_id,
       employee_name,
       department
FROM Dummy_employees
INTERSECT
SELECT employee_id,
       employee_name,
       department
FROM Dummy_employees

FROM Dummy_employees

在不使用DISTINCT的情况下选择唯一的记录 | 图片由作者提供 由于两个SELECT查询的输出结果相同,连接将产生10行数据。然后通过INTERSECTs固有的返回唯一记录的特性,只有一个重复的记录会被返回,导致最终输出8条记录。

注意:在使用UNION和INTERSECT时,两个SELECT语句中的列数和顺序必须相同。

接下来是获得唯一记录的方法。

ROW_NUMBER() 在SQL中,ROW_NUMBER()是一个窗口函数,为结果集的分区中的每一条记录分配一个连续的整数。

窗口函数。一个SQL函数,其输入值取自SELECT语句结果集中的一条或多条记录的 "窗口"。这个函数使用OVER子句和PARTITION BY和ORDER BY子句来制作一个或多个行的窗口。

因此,在每个分区中,行号1被分配给第一行。

下面是它的工作原理...

SELECT employee_id,
       employee_name,
       department,
       ROW_NUMBER() OVER(PARTITION BY employee_name,
                                      department,
                                      employee_id) as row_count
FROM Dummy_employees

ROW_NUMBER()是如何在SQL中工作的 | 图片来自作者 正如你所看到的,当employee_name为Abdul和Stella时,每个分区都有两行。因此,行号2被分配给这些重复的行。

因此,为了得到唯一的记录,你需要选择所有行号为1的行,即上表中row_count的值为1。

❓ 然而,这里有一个问题!!

你不能在WHERE子句中使用窗口函数,因为在SQL查询执行中,WHERE子句在计算窗口函数之前被处理。你可以在Agnieszka的这篇文章中了解更多关于SQL查询的执行顺序。

最终,你需要创建一个临时表来存储上述查询的输出,并需要另一个SELECT语句来获得不同的记录。你可以使用WITH子句或CTE(通用表表达式)来创建临时表。💯

让我们来看看如何从数据集中获得唯一的employee_id, employee_name和部门的组合。


WITH temporary_employees as
(
SELECT 
  employee_id,
  employee_name,
  department,
  ROW_NUMBER() OVER(PARTITION BY employee_name,
                                 department,
                                 employee_id) as row_count
FROM Dummy_employees
)
SELECT *
FROM temporary_employees
WHERE row_count = 1

在SQL中使用ROW_NUMBER()获得不同的记录 | 作者的图片 通过这种方式,你可以看到在输出中只有那些记录是有row_count = 1的。

这里,最后一列--row_count的生成只是为了提供信息。即使你不包括这一列,该查询仍然有效。

除了运算符和窗口函数之外,还有一个简单方便的方法来获得唯一的记录--GROUP BY

GROUP BY 在SQL中,GROUP BY子句是用来按一个或多个列来分组记录。它经常与COUNT(), MAX(), MIN(), SUM(), AVG()等聚合函数一起使用,以获得被分组行的聚合计算。

然而,它也可以在没有任何聚合函数的情况下使用,以获得独特或唯一的记录,如下所示。

SELECT employee_id,
       employee_name,
       department
FROM Dummy_employees
GROUP BY employee_id,
         employee_name,
         department

在SQL中使用GROUP BY获得唯一记录 | 图片来自作者 简单地说,你需要在GROUP BY子句中提到所有的列名来获得唯一记录。

几乎90%的时候,我觉得GROUP BY更方便,因为我总是想用聚合函数做一些其他计算。

这就是全部!

我希望你能很快地看完这篇文章,并觉得它很新鲜和有用。

我从过去3年开始使用SQL,我发现这些替代方法相当节省时间,而且功能强大,特别是在处理大型数据集时。此外,我还发现其中一些查询是很好的面试问题。

分类:

后端

标签:

后端

作者介绍

c
codeye
V1