寻找表中重复值的2个最佳SQL技巧
数据科学
消除重复记录以节省时间和金钱
重复记录无处不在！
并且是每个数据库用户都面临的最常见问题之一。
由于数据集成、系统故障、更新数据库时的人为错误以及缺乏数据验证检查等各种原因，此类重复记录存在于数据库中。
重复记录使数据不一致。这样的数据库会花费您或您的公司更多的金钱、时间和资源来维护和处理。重复记录不必要地消耗额外的存储空间并减慢查询执行速度。
因此，在进行分析之前，您必须至少从您正在查询的表中消除这些重复记录。
这就是为什么在面试中公司经常问有关处理重复记录的问题。
在本文中，您将探索 SQL 中识别重复记录的两种最佳、节省时间的方法。我沿途采取了有趣的例子来清楚地解释这些概念。
在本文末尾，还要不要忘记查看惊人的 SQL 资源。
让我们开始吧！
我正在使用 MySQL Workbench 和自己创建的销售数据子集，该子集使用 Faker 创建。您可以在我的 Github 存储库中免费获取它，遵循 MIT 许可！
这是数据集中订单表的一个小 18 x 6 子集，如下所示。它有一些重复记录。
在直接跳转到查询之前，您需要定义在表中查找重复记录的标准。有时单个列中的某些值重复，或者特定行中所有列中的值都重复，这可能是一种情况。
在本快速阅读中，您将探讨这两种可能性以及处理此类重复记录的方法。
识别重复记录的最简单方法是简单地计算每个记录在表中出现的次数。出现超过一次的记录是重复的。
使用 GROUP BY 和 HAVING 查找重复记录
函数 GROUP BY 在 SQL 中被广泛用于数据聚合。这意味着您可以基于一个或多个列中的值对记录进行分组，并获取聚合值，例如计数或其他列的总和。
记住这一点，让我们探索如何找出单个列中的重复值。
找到单列中的重复值
可能存在仅在一个列中存在重复值的情况。这样的重复记录的原因可能只是在进行数据输入或更新数据库时发生了人为错误。
让我们从订单表中选取一个示例，并找出其中重复的 OrderID。由于您需要计算每个 OrderID 在表中出现的次数，因此应按 OrderID 分组，如下所示。
SELECT OrderID , COUNT(*) as occurrences FROM ordersGROUP BY OrderID
上图中突出显示的记录（OrderIDs）在数据集中出现了多次，即它们是重复的。
但是，您不需要创建与上图中所见不同的单独列。您可以在GROUP BY 之后使用 HAVING 子句直接获取重复的 OrderIDs，如下所示。
SELECT OrderIDFROM ordersGROUP BY OrderIDHAVING COUNT(*) > 1;
所以，你只能得到与上表中突出显示的相同的重复的OrderIDs。
同样，在表中多列的值重复的情况下，也可能会出现这种情况。
查找多列中的重复值
虽然在整个表中重复了整个行，但是逻辑仍然相同，只是在GROUP BY子句中提到的列不同。
在这里，您需要按多列分组记录。
让我向您展示如何操作。
假设您想查看在表中多次出现的OrderID、Quantity和Product_Category的组合的记录。
SELECT OrderID , Quantity , Product_Category , COUNT(*) as occurrencesFROM ordersGROUP BY OrderID , Quantity , Product_Category
通过这种方法，您可以看到表中出现了多次的OrderID、Quantity和Product_Category列中值的组合。
同样，您只需要在查询末尾添加HAVING COUNT(*) > 1即可检索这些重复记录。
由于查找重复项的过程取决于记录在表中出现的次数，因此您也可以使用窗口函数ROW_NUMBER。
使用ROW_NUMBER()查找重复记录
窗口函数ROW_NUMBER()将唯一的连续号码分配给窗口中的每个记录，该窗口由PARTITION_BY子句定义。
因此，您实际上可以使用相同的列定义窗口，其中您希望有重复值。因此，如果一条记录出现多次，则重复记录将分配大于1的行号。
让我们继续使用相同的示例。
要获取表中多次出现的OrderID、Quantity和Product_Category组合的记录，您需要使用PARTITION_BY子句中的这些列定义窗口，如下所示。
SELECT OrderID , Quantity , Product_Category , ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_numFROM orders
这是您将获得的所有记录及其对应的行号，这些记录是由给定一组列进行分区的。因此，所有突出显示的记录的行号都为2，这些记录是重复的。
您可以将上面的整个查询作为子查询传递到下面的外部SELECT语句中，以仅获取重复记录。
SELECT OrderID , Quantity , Product_CategoryFROM ( SELECT OrderID , Quantity , Product_Category , ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_num FROM orders ) AS subqueryWHERE row_num > 1;
或者，如果您不想使用子查询，您可以创建一个CTE，并使用另一个查询从该CTE获取数据，如下所示。
WITH temp_orders AS(SELECT OrderID , Quantity , Product_Category , ROW_NUMBER() OVER (PARTITION BY OrderID, Quantity, Product_Category ORDER BY OrderID) AS row_numFROM orders)SELECT OrderID , Quantity , Product_CategoryFROM temp_ordersWHERE row_num > 1;此查询也将返回完全相同的输出。所以选择权在您手中。
要了解有关ROW_NUMBER()、CTE和GROUP BY的更多信息，请勿忘记在本文末尾查看有趣的资源！
当包含重复记录的数据用于进一步分析时，将会对正确记录产生混淆。因此，在进行分析之前，必须修复这类重复记录。
在项目的最早阶段处理重复记录肯定会节省您的时间、精力和计算费用。因此，查找重复项并采取措施处理它们将有助于您进行数据验证并获得高质量的数据。
尽管如此，这是数据分析师或数据工程师角色中最常见的面试问题之一。因此，掌握这些技巧并了解如何解决此问题肯定会帮助您通过面试。
我将涵盖更多有趣的主题和常见的SQL面试问题以及不同的解决方法 – 敬请关注！
有兴趣阅读 小猪AI 上的无限故事吗？
💡考虑成为 小猪AI 会员，以访问 小猪AI 上的无限故事和每日有趣的 小猪AI 电子报。我将获得您的费用的一小部分，对您没有额外费用。
💡一定要 注册并加入200多人，以便不会错过有关数据科学指南、技巧和窍门以及SQL和Python的最佳实践的其他文章。
请查看本文以详细了解 ROW_NUMBER() 和 CTE。
2022年你应该知道的5个高级SQL概念
掌握这些节省时间的高级SQL查询。
towardsdatascience.com
要精通SQL GROUP BY – 我强烈建议阅读下面的文章。
5个实用的SQL示例，以精通SQL GROUP BY
10分钟内易于复杂的SQL GROUP BY用例！
towardsdatascience.com
谢谢您的阅读！
