SQL 中查找重復數據的四種方法
數據庫中的重復數據可能導致存儲成本增加、查詢性能下降、分析結果不準確以及數據管理混亂。本文概述了四種 SQL 技術來檢測和處理這些重復數據：使用GROUP BY和HAVING識別重復行的分組，采用諸如ROW_NUMBER()的窗口函數進行高效分析，利用EXISTS操作符檢查特定的重復條件，以及執行自連接以比較同一表中的行。本文還討論了某些方法需要唯一標識符的必要性，并提供了針對大表的優化技術，例如索引和分區。最后，展示了如何使用公共表表達式（CTE）結合 ROW_NUMBER() 函數刪除重復行。
一、重復數據的存在會導致什么問題？
數據庫中的重復數據是一個常見問題，可能對數據完整性、存儲效率和整體系統性能產生重大影響。重復數據的存在可能導致以下幾個問題。
- 不必要的數據占用空間，從而增加存儲成本
- 處理冗余信息導致查詢性能下降
- 報告和分析不準確，可能導致錯誤的商業決策
- 數據管理和客戶互動時的混淆
識別和管理重復行對于維護干凈、高效和可靠的數據庫至關重要。在本篇文章將探討查找重復行的各種SQL技術，讓您邁出提高數據質量和數據庫性能的第一步。
二、在SQL中查找重復數據
首先創建并插入一些數據。
CREATE TABLE employees (
id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department VARCHAR(50)
);
INSERT INTO employees VALUES
(1, 'John', 'Doe', 'john.doe@example.com', 'Sales'),
(2, 'Jane', 'Smith', 'jane.smith@example.com', 'Marketing'),
(3, 'Bob', 'Johnson', 'bob.johnson@example.com', 'IT'),
(4, 'Alice', 'Williams', 'alice.williams@example.com', 'HR'),
(5, 'John', 'Doe', 'john.doe@example.com', 'Sales'),
(6, 'Sarah', 'Brown', 'sarah.brown@example.com', 'Marketing'),
(7, 'Bob', 'Johnson', 'bob.johnson@example.com', 'IT');2.1 使用GROUP BY和HAVING ??
使用GROUP BY和HAVING子句可以高效地識別SQL中的重復行。這種方法通過分組相同值的行，然后篩選出包含多條記錄的組，從而找出表中的重復項。
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY first_name,last_name,email,department
ORDER BY id
)AS rn
FROM employees
)
SELECT *
FROM cte
WHERE rn>1;2.2 使用窗口函數 ??
窗口函數是SQL識別重復行的高效工具，尤其在處理大量數據時。它們允許計算當前行及其相關行，提供靈活分析手段。例如ROW_NUMBER() 這樣的窗口函數可能更有效。
SELECT DISTINCT t1.*
FROM employees t1
INNER JOIN employees t2 ON
t1.first_name = t2.first_name AND
t1.last_name = t2.last_name AND
t1.email = t2.email AND
t1.department = t2.department AND
t1.id > t2.id;接下來的兩種方法需要至少一個唯一標識符，本文將在示例后解釋。
2.3 使用窗口函數 ??
SQL 中的EXISTS操作符提供了另一種檢查滿足特定條件的行是否存在的方法，這使得其在識別重復數據時特別有用。該方法在處理復雜條件時可能比某些替代方案更高效。
SELECT t1.*
FROM employees t1
WHERE EXISTS (
SELECT 1
FROM employees t2
WHERE t1.first_name = t2.first_name AND
t1.last_name = t2.last_name AND
t1.email = t2.email AND
t1.department = t2.department
AND t1.id > t2.id
);2.4 使用自連接 ??
自連接是另一種強大的SQL技術，允許一個表與自身連接，使其在查找重復行時特別有用。該方法將每一行與同一表中的每一行比較，使我們能夠識別在指定列中具有相同值的記錄。
SELECT DISTINCT t1.*
FROM employees t1
INNER JOIN employees t2 ON
t1.first_name = t2.first_name AND
t1.last_name = t2.last_name AND
t1.email = t2.email AND
t1.department = t2.department AND
t1.id > t2.id;三、唯一標識符與大表優化
唯一標識符的必要性
- 對于 EXISTS 和自連接方法，在查找完全重復項時需要一個唯一標識符。這個唯一標識符（通常是自增 ID 或主鍵）有助于區分在其它方面相同的行。
唯一標識符的目的
- 允許比較行而不將一行與自身匹配。能夠從每組重復中僅選擇一行。
數據表較大時可考慮的優化技術
- 確保在您檢查重復項的列上有適當的索引。
- 如果可能，針對您經常檢查的重復項列使用分區。
- 考慮使用臨時表或公共表表達式（CTE）來簡化復雜查詢。
- 使用EXPLAIN PLAN分析查詢性能并進行相應優化。
對于非常大的表，考慮使用批處理或并行查詢執行（如果您的數據庫系統支持）。有效方法將取決于特定數據庫系統、表結構和數據分布。可測試多種方法，以找到適合特定用例的最佳方案。
四、刪除重復行
讓我們看看如何從表中的完全重復項中刪除行，可使用公共表表達式（CTE）和ROW_NUMBER() 函數的組合。
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY first_name, last_name, email, department -- 列出定義重復的所有列
ORDER BY id -- 最好是主鍵或唯一標識符
) AS rn
FROM employees
)
DELETE FROM CTE WHERE rn > 1;
SELECT * FROM employees;工作原理
- CTE 使用ROW_NUMBER() 在重復組內為每一行分配一個編號。
- PARTITION BY子句定義什么構成重復（列出所有應相同的列）。
- ORDER BY決定保留哪個重復項（rn=1的那個）。
- DELETE語句刪除所有rn>1的行，有效刪除每組重復內容。
