重複レコードの削除方法

time 2016/01/28

ORACLEにおいて、データに重複があり内1レコードを残して他の重複レコードを削除するにはどのようにすればよいのか、重複データの確認方法と削除の方法をまとめました。

重複データの確認

削除の前に、まずは重複データの確認方法からです。

対象とするテーブル情報とデータ

●テーブル構造
テーブル：test_table
項目１：a（VARCHAR2(3)）
項目２：b（VARCHAR2(5)）
項目３：c（DATE）
論理的な一意キーは、a+b

●データ
A   B     C
--- ----- ----------
001 00001 2016/01/01
001 00002 2016/01/02
001 00002 2016/01/03
002 00001 2016/01/04
002 00001 2016/01/04
002 00001 2016/01/04
002 00002 2016/01/05

●テーブル構造

テーブル：test_table

項目１：a（VARCHAR2(3)）

項目２：b（VARCHAR2(5)）

項目３：c（DATE）

論理的な一意キーは、a+b

●データ

A B C

--- ----- ----------

001 00001 2016/01/01

001 00002 2016/01/02

001 00002 2016/01/03

002 00001 2016/01/04

002 00002 2016/01/05

一意になるはずのキーで集約し、件数が1件より大きいデータを抽出します。
HAVING句を使用することで集約後の値に対して条件を指定できます。

SELECT a
      ,b
      ,COUNT(*) cnt
FROM   test_table
GROUP BY a
        ,b
HAVING COUNT(*) > 1
;

A   B       CNT
--- ----- -----
001 00002     2
002 00001     3

SELECT a

,COUNT(*) cnt

FROM test_table

GROUP BY a

HAVING COUNT(*) > 1

;

A B CNT

--- ----- -----

001 00002 2

002 00001 3

キーを指定し、重複データを削除する

一意となるキーごとに削除する場合、一意キー以外の項目でデータに差異がある場合と、差異が全くない場合で方法が異なります。

一意キー以外の項目でデータに差異がある場合は、単純に一意キー以外の項目も条件として指定し、データを削除します。

DELETE FROM test_table
WHERE a = '001'
AND   b = '00002'
AND   c = TO_DATE('20160102','YYYYMMDD')
;

DELETE FROM test_table

WHERE a = '001'

AND b = '00002'

AND c = TO_DATE('20160102','YYYYMMDD')

;

一意キー以外の項目で差異が全くない場合は、ROWNUM疑似列を利用します。
ROWNUM疑似列を条件に使用することで、対象の件数を絞ることが出来ます。

DELETE FROM test_table
WHERE a = '002'
AND   b = '00001'
AND   ROWNUM < 3   -- 重複している件数を指定
;

DELETE FROM test_table

WHERE a = '002'

AND b = '00001'

AND ROWNUM < 3 -- 重複している件数を指定

;

上記で重複3件のうち2件を削除する事が出来ます。

重複データをまとめて削除する

対象テーブル内の重複データをまとめて削除する場合、どのデータを残してもよい場合と、優先順位をつける場合で方法が異なります。
どちらも、全体のデータ件数が多い場合や重複するデータ数が多い場合は、処理時間がかかることが想定されるます。事前に十分な検証を行いましょう。

どのデータを残してもよい場合は、ORACLEがレコードごとに一意になるようにふっているROWID擬似列を利用します。

DELETE FROM test_table t1
WHERE EXISTS(SELECT *
             FROM   test_table t2
             WHERE  t2.a = t1.a
             AND    t2.b = t1.b
             AND    t2.ROWID > t1.ROWID
            )
;

DELETE FROM test_table t1

WHERE EXISTS(SELECT *

FROM test_table t2

WHERE t2.a = t1.a

AND t2.b = t1.b

AND t2.ROWID > t1.ROWID

)

;

残したいデータに優先順位をつける場合（一意キー以外の項目で差異が全くない場合は、1レコードだけ残るように削除する）は、複数回DELETE文を発行します。
以下は、項目cが大きい値を優先して残す場合です。

DELETE FROM test_table t1
WHERE EXISTS(SELECT *
             FROM   test_table t2
             WHERE  t2.a = t1.a
             AND    t2.b = t1.b
             AND    t2.c > t1.c
            )
;

DELETE FROM test_table t1
WHERE EXISTS(SELECT *
             FROM   test_table t2
             WHERE  t2.a = t1.a
             AND    t2.b = t1.b
             AND    t2.ROWID > t1.ROWID
            )
;