在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，我发现这些替代方法相当节省时间，而且功能强大，特别是在处理大型数据集时。此外，我还发现其中一些查询是很好的面试问题。

本文由mdnice多平台发布