數(shù)據(jù)庫中的重復數(shù)據(jù)可能導致存儲成本增加、查詢性能下降、分析結(jié)果不準確以及數(shù)據(jù)管理混亂。本文概述了四種 SQL 技術(shù)來檢測和處理這些重復數(shù)據(jù)：使用GROUP BY和HAVING識別重復行的分組，采用諸如ROW_NUMBER()的窗口函數(shù)進行高效分析，利用EXISTS操作符檢查特定的重復條件，以及執(zhí)行自連接以比較同一表中的行。本文還討論了某些方法需要唯一標識符的必要性，并提供了針對大表的優(yōu)化技術(shù)，例如索引和分區(qū)。最后，展示了如何使用公共表表達式（CTE）結(jié)合 ROW_NUMBER() 函數(shù)刪除重復行。

一、重復數(shù)據(jù)的存在會導致什么問題？

數(shù)據(jù)庫中的重復數(shù)據(jù)是一個常見問題，可能對數(shù)據(jù)完整性、存儲效率和整體系統(tǒng)性能產(chǎn)生重大影響。重復數(shù)據(jù)的存在可能導致以下幾個問題。

• 不必要的數(shù)據(jù)占用空間，從而增加存儲成本
• 處理冗余信息導致查詢性能下降
• 報告和分析不準確，可能導致錯誤的商業(yè)決策
• 數(shù)據(jù)管理和客戶互動時的混淆

識別和管理重復行對于維護干凈、高效和可靠的數(shù)據(jù)庫至關(guān)重要。在本篇文章將探討查找重復行的各種SQL技術(shù)，讓您邁出提高數(shù)據(jù)質(zhì)量和數(shù)據(jù)庫性能的第一步。

二、在SQL中查找重復數(shù)據(jù)

首先創(chuàng)建并插入一些數(shù)據(jù)。

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 使用窗口函數(shù) ??

窗口函數(shù)是SQL識別重復行的高效工具，尤其在處理大量數(shù)據(jù)時。它們允許計算當前行及其相關(guān)行，提供靈活分析手段。例如ROW_NUMBER() 這樣的窗口函數(shù)可能更有效。

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 使用窗口函數(shù) ??

SQL 中的EXISTS操作符提供了另一種檢查滿足特定條件的行是否存在的方法，這使得其在識別重復數(shù)據(jù)時特別有用。該方法在處理復雜條件時可能比某些替代方案更高效。

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技術(shù)，允許一個表與自身連接，使其在查找重復行時特別有用。該方法將每一行與同一表中的每一行比較，使我們能夠識別在指定列中具有相同值的記錄。

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;

三、唯一標識符與大表優(yōu)化

唯一標識符的必要性

• 對于 EXISTS 和自連接方法，在查找完全重復項時需要一個唯一標識符。這個唯一標識符（通常是自增 ID 或主鍵）有助于區(qū)分在其它方面相同的行。

唯一標識符的目的

• 允許比較行而不將一行與自身匹配。能夠從每組重復中僅選擇一行。

數(shù)據(jù)表較大時可考慮的優(yōu)化技術(shù)

• 確保在您檢查重復項的列上有適當?shù)乃饕?/li>
• 如果可能，針對您經(jīng)常檢查的重復項列使用分區(qū)。
• 考慮使用臨時表或公共表表達式（CTE）來簡化復雜查詢。
• 使用EXPLAIN PLAN分析查詢性能并進行相應優(yōu)化。

對于非常大的表，考慮使用批處理或并行查詢執(zhí)行（如果您的數(shù)據(jù)庫系統(tǒng)支持）。有效方法將取決于特定數(shù)據(jù)庫系統(tǒng)、表結(jié)構(gòu)和數(shù)據(jù)分布。可測試多種方法，以找到適合特定用例的最佳方案。

四、刪除重復行

讓我們看看如何從表中的完全重復項中刪除行，可使用公共表表達式（CTE）和ROW_NUMBER() 函數(shù)的組合。

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() 在重復組內(nèi)為每一行分配一個編號。
• PARTITION BY子句定義什么構(gòu)成重復（列出所有應相同的列）。
• ORDER BY決定保留哪個重復項（rn=1的那個）。
• DELETE語句刪除所有rn>1的行，有效刪除每組重復內(nèi)容。