「SQL Server」で重複行を削除する方法

重複行を削除する理由

データベース内の重複行は、データの整合性や分析の正確性に影響を与える可能性があります。そのため、重複行を適切に削除または処理することは重要です。

重複行を削除する一般的な手法

1. 一時テーブルを使用する:

   • 一時テーブルを作成して、重複行を除外したデータを格納します。
   • ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) を使用して、重複する行に一意の番号を割り当てます。
   • 最初に現れる行（ROW_NUMBER() = 1）のみを一時テーブルに挿入します。

   CREATE TABLE #TempTable (
       -- 列定義
   );
   
   INSERT INTO #TempTable (
       -- 列名
   )
   SELECT
       -- 列名
   FROM
       YourTable
   WHERE
       ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) = 1;
   

2. DELETE文を使用する:

   • WHERE句で重複条件を指定し、重複する行を削除します。

   DELETE FROM YourTable
   WHERE
       ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) > 1;
   

3. CTE（Common Table Expression）を使用する:

   • CTEで重複行を除外したデータセットを定義し、それをDELETE文で使用します。

   WITH CTE AS (
       SELECT
           ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS RowNumber,
           -- 列名
       FROM
           YourTable
   )
   DELETE FROM CTE
   WHERE
       RowNumber > 1;
   

注意:

• 重複条件: どの列に基づいて重複を判断するかを明確に定義する必要があります。
• データ損失: 重複行を削除する前に、バックアップを作成してデータ損失を防ぐことを推奨します。
• パフォーマンス: 大量のデータを処理する場合、インデックスが適切に作成されていることを確認してください。

例:

-- 重複する顧客IDを削除
DELETE FROM Customers
WHERE
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) > 1;

CREATE TABLE #TempTable (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(50),
    -- 他の列
);

INSERT INTO #TempTable (
    CustomerID,
    CustomerName
)
SELECT
    CustomerID,
    CustomerName
FROM
    Customers
WHERE
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) = 1;

-- 一時テーブルから元のテーブルにデータを挿入
INSERT INTO Customers (
    CustomerID,
    CustomerName
)
SELECT
    CustomerID,
    CustomerName
FROM
    #TempTable;

-- 一時テーブルを削除
DROP TABLE #TempTable;

DELETE FROM Customers
WHERE
    ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) > 1;

WITH CTE AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) AS RowNumber,
        CustomerID,
        CustomerName
    FROM
        Customers
)
DELETE FROM CTE
WHERE
    RowNumber > 1;

説明:

• CustomerIDは重複を判断する列です。
• ROW_NUMBER()関数は、同じCustomerIDを持つ行に一意の番号を割り当てます。
• PARTITION BY CustomerIDは、CustomerIDごとにグループ化します。
• ORDER BY CustomerIDは、グループ内の行を昇順でソートします。
• WHERE ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) = 1は、各グループの最初の行（重複していない行）を選択します。
• DELETE FROM Customers WHERE ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) > 1は、重複する行（2番目以降の行）を削除します。
• CTEを使用する方法は、複雑な条件や複数のテーブルを扱う場合に便利です。

SELECT DISTINCT
    CustomerID,
    CustomerName
FROM
    Customers;

この方法は、重複する行を削除して、各列の値が異なる行のみを返します。ただし、元のテーブルを変更することはできません。

GROUP BY句を使用する:

SELECT
    CustomerID,
    MAX(CustomerName) AS CustomerName
FROM
    Customers
GROUP BY
    CustomerID;

この方法は、重複する行をグループ化し、各グループの最初の行または最大値を返します。ただし、他の列の値は考慮されません。

EXISTSサブクエリを使用する:

DELETE FROM Customers
WHERE
    EXISTS (
        SELECT 1
        FROM Customers AS T2
        WHERE
            Customers.CustomerID = T2.CustomerID
            AND Customers.CustomerName = T2.CustomerName
            AND Customers.RowNumber > T2.RowNumber
    );

この方法は、各行に対して、同じ値を持つ別の行が存在するかをチェックし、存在する場合はその行を削除します。ただし、パフォーマンスが低下する可能性があります。

MERGE文を使用する:

WITH CTE AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID) AS RowNumber,
        CustomerID,
        CustomerName
    FROM
        Customers
)
MERGE INTO Customers AS Target
USING CTE AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED AND Source.RowNumber > 1 THEN DELETE;

この方法は、CTEを使用して重複行を識別し、MERGE文を使用して元のテーブルから重複行を削除します。

• これらの方法は、特定の状況や要件に応じて選択する必要があります。
• DISTINCTキーワードは、単純な重複削除に適していますが、他の列の値を考慮しません。
• GROUP BY句は、特定の列に基づいて重複を削除しますが、他の列の値は考慮しません。
• EXISTSサブクエリは、複雑な条件や複数のテーブルを扱う場合に適していますが、パフォーマンスが低下する可能性があります。
• MERGE文は、CTEと組み合わせて使用することで、効率的に重複行を削除できます。