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

一、重復(fù)數(shù)據(jù)的存在會(huì)導(dǎo)致什么問(wèn)題？

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

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

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

二、在SQL中查找重復(fù)數(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子句可以高效地識(shí)別SQL中的重復(fù)行。這種方法通過(guò)分組相同值的行，然后篩選出包含多條記錄的組，從而找出表中的重復(fù)項(xiàng)。

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í)別重復(fù)行的高效工具，尤其在處理大量數(shù)據(jù)時(shí)。它們?cè)试S計(jì)算當(dāng)前行及其相關(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;

接下來(lái)的兩種方法需要至少一個(gè)唯一標(biāo)識(shí)符，本文將在示例后解釋。

2.3 使用窗口函數(shù) ??

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

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 使用自連接 ??

自連接是另一種強(qiáng)大的SQL技術(shù)，允許一個(gè)表與自身連接，使其在查找重復(fù)行時(shí)特別有用。該方法將每一行與同一表中的每一行比較，使我們能夠識(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;

三、唯一標(biāo)識(shí)符與大表優(yōu)化

唯一標(biāo)識(shí)符的必要性

• 對(duì)于 EXISTS 和自連接方法，在查找完全重復(fù)項(xiàng)時(shí)需要一個(gè)唯一標(biāo)識(shí)符。這個(gè)唯一標(biāo)識(shí)符（通常是自增 ID 或主鍵）有助于區(qū)分在其它方面相同的行。

唯一標(biāo)識(shí)符的目的

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

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

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

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

四、刪除重復(fù)行

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

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY first_name, last_name, email, department -- 列出定義重復(fù)的所有列
               ORDER BY id -- 最好是主鍵或唯一標(biāo)識(shí)符
           ) AS rn
    FROM employees
)
DELETE FROM CTE WHERE rn > 1;

SELECT * FROM employees;

工作原理

• CTE 使用ROW_NUMBER() 在重復(fù)組內(nèi)為每一行分配一個(gè)編號(hào)。
• PARTITION BY子句定義什么構(gòu)成重復(fù)（列出所有應(yīng)相同的列）。
• ORDER BY決定保留哪個(gè)重復(fù)項(xiàng)（rn=1的那個(gè)）。
• DELETE語(yǔ)句刪除所有rn>1的行，有效刪除每組重復(fù)內(nèi)容。