不良品の削減と顧客満足度の向上：SQL Server を使用して製品データの重複を特定する方法

2024-05-25

SQL Server で複数の列で重複を見つける方法

DISTINCT キーワードを使用する

最も基本的な方法は、DISTINCT キーワードを使用することです。これは、選択された列の組み合わせに基づいて重複する行を排除するものです。

SELECT DISTINCT 列1, 列2, 列3
FROM テーブル名;

このクエリは、列1、列2、列3 の値が一致する行を 1 行だけ返します。

欠点:

重複行を完全に削除することはできません。各重複行から 1 行のみが返されます。
列の型が異なる場合、または列に NULL 値が含まれている場合、正しく動作しない場合があります。

GROUP BY 句を使用すると、各グループ内の行を 1 行に集計できます。重複をカウントするには、COUNT 集計関数を使用できます。

SELECT 列1, 列2, 列3, COUNT(*) AS 重複カウント
FROM テーブル名
GROUP BY 列1, 列2, 列3
HAVING COUNT(*) > 1;

このクエリは、列1、列2、列3 の値が一致する行のグループをすべて返します。各グループには、そのグループ内の重複行の数を示す 重複カウント 列が含まれます。

重複行を完全に削除できます。

DISTINCT よりも処理速度が遅い場合があります。

COMMON TABLE EXPRESSION (CTE) を使用する

より複雑な重複検出シナリオの場合は、CTE を使用できます。CTE を使用すると、一時的な結果セットを作成して、その結果セットに対してクエリを実行できます。

WITH cte_重複 AS (
    SELECT 列1, 列2, 列3, ROW_NUMBER() OVER (PARTITION BY 列1, 列2, 列3 ORDER BY 列1, 列2, 列3) AS 行番号
    FROM テーブル名
)
SELECT *
FROM cte_重複
WHERE 行番号 > 1;

このクエリは、列1、列2、列3 の値が一致する行のグループをすべて返します。各行には、そのグループ内の行の番号を示す 行番号 列が含まれます。 行番号 が 1 より大きい行はすべて重複しているため、これらの行のみが返されます。

複雑な重複検出シナリオに対応できます。
CTE を再利用して、さまざまなクエリで使用できます。

DISTINCT や GROUP BY よりも複雑で、理解しにくい場合があります。

最適な方法を選択する

使用する方法は、特定のニーズとデータセットによって異なります。

単純な重複検出の場合は、DISTINCT を使用するのが最善です。
重複行を完全に削除する必要がある場合は、GROUP BY を使用します。
複雑な重複検出シナリオの場合は、CTE を使用します。

ヒント

複数の列で重複を検出する場合は、インデックスを作成するとパフォーマンスが向上します。
大規模なテーブルの場合は、クエリをより効率的にするために、WHERE 句を使用して結果を絞り込むことができます。
重複データを特定したら、削除、修正、またはフラグを立てることができます。

サンプルコード：SQL Server で複数の列で重複を見つける

テーブル構造:

CREATE TABLE customers (
  customer_id INT IDENTITY PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

INSERT INTO customers (name, email)
VALUES
  ('John Doe', '[email protected]'),
  ('Jane Doe', '[email protected]'),
  ('John Doe', '[email protected]'),
  ('Peter Jones', '[email protected]'),
  ('Mary Smith', '[email protected]');

重複を検出するクエリ:

WITH cte_重複 AS (
    SELECT name, email, ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY name, email) AS 行番号
    FROM customers
)
SELECT *
FROM cte_重複
WHERE 行番号 > 1;

このクエリは以下の結果を返します:

name        email       行番号
John Doe    [email protected] 2
John Doe    [email protected] 3

説明:

WITH 句を使用して、cte_重複 という名前の CTE を定義します。
CTE は、name と email 列でパーティション化された結果セットを返します。
各パーティション内の行は、name と email の値に基づいて昇順に並べ替えられます。
ROW_NUMBER ウィンドウ関数を使用して、各行に 行番号 を割り当てます。
SELECT ステートメントは、行番号 が 1 より大きい行のみを返します。

このコードは、基本的な例です。実際のシナリオでは、必要に応じてクエリを調整する必要があります。

複数の列で重複を検出するには、name と email を置き換えるだけです。
特定の条件に一致する重複のみを検出するには、WHERE 句を追加できます。
重複データを削除するには、DELETE ステートメントを使用できます。

SQL Server で複数の列で重複を見つけるその他の方法

サブクエリを使用する

SELECT *
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM customers AS c2
    WHERE c2.customer_id <> c1.customer_id
    AND c2.name = c1.name
    AND c2.email = c1.email
);

このクエリは、customers テーブル内のすべての行をループし、各行を同じ name と email の値を持つ他の行と比較します。一致する行が見つかった場合、元の行は結果セットに返されます。

シンプルで理解しやすい

他の方法よりも非効率的な場合がある

窓関数を使用する

SELECT *,
       COUNT(*) OVER (PARTITION BY name, email) AS 重複カウント
FROM customers;

このクエリは、customers テーブル内のすべての行をループし、各行の 重複カウント を計算します。 重複カウント は、同じ name と email の値を持つ行の数を示します。 重複カウント が 1 より大きい行はすべて重複しているため、これらの行のみが結果セットに返されます。