(ORA-00018) - Loại bỏ các hàng trùng lặp trong bảng

Làm các nào để xác định và loại bỏ các hàng trùng lặp trong bảng?

Nguyên nhân: Đôi khi chúng ta mắc những sai lầm trong insert khiến những giá trị trùng lặp xuất hiện. Vì vậy chúng ta cần phải loại bỏ những bản sao này (duplicate records). Tuy nhiên, trước tiên ta cần phải xác định được những hàng nào bị trùng.

Xác định dòng trùng lặp:
 Còn nhớ về DISTINCT, GROUP BY chứ? Chúng ta sẽ tiếp cận bằng cách này.
 Distinct cho biết có bao nhiêu dòng là duy nhất trong bảng. Nói cách khác, Distinct loại bỏ những bản sao y của hàng ra khỏi kết quả trả về. Tuy nhiên Distinct không cho ta biết được có bao nhiêu bản sao như thế này. Nhưng nếu tiếp cận từ Group By, ta có thể biết được.
Sử dụng COUNT và GROUP BY sẽ cho ta biết được có những dòng nào bị 'đúp' và 'đúp' bao nhiêu lần:

SQL> SELECT * FROM DUP_TEST;

        ID NAME    
---------- ----------
       101 Leesin  
       101 Leesin  
       102 Yasuo   
       103 Jinx    
       103 Jinx    
       103 Jinx    
       104 Messi   
       106 Ronaldo 
       105 Alistar 
       105 Alistar 

10 rows selected.

SQL> SELECT ID, COUNT(*) FROM DUP_TEST GROUP BY ID;

        ID   COUNT(*)
---------- ----------
       102          1
       101          2
       104          1
       105          2
       103          3
       106          1

6 rows selected.

Tiếp theo, ta sẽ lọc chỉ lấy kết quả những hàng nào đang bị 'đúp' bằng cách thêm điều kiện HAVING:

SQL> SELECT ID, COUNT(*) FROM DBDEV01.DUP_TEST GROUP BY ID HAVING COUNT(*) > 1;

        ID   COUNT(*)
---------- ----------
       101          2
       105          2
       103          3

3 rows selected.

Xóa trùng lặp:
Có nhiều cách để xóa các bản ghi trùng lặp ra khỏi bảng, kết quả sau khi xóa là ID phải là duy nhất trong bảng DUP_TEST. Hãy thử một trong những cách sau đây:

Cách 1: Sử dụng ROWID cùng với GROUP BY

SQL> SELECT * FROM DUP_TEST;

        ID NAME    
---------- ----------
       101 Leesin  
       101 Leesin  
       102 Yasuo   
       103 Jinx    
       103 Jinx    
       103 Jinx    
       104 Messi   
       106 Ronaldo 
       105 Alistar 
       105 Alistar 

10 rows selected.

SQL> DELETE DUP_TEST
WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM DUP_TEST GROUP BY ID);

4 rows deleted.

SQL> SELECT * FROM DUP_TEST;

        ID NAME    
---------- ----------
       101 Leesin  
       102 Yasuo   
       103 Jinx    
       104 Messi   
       106 Ronaldo 
       105 Alistar 

6 rows selected.

Cách 2: Sử dụng ROWID cùng với Self-join
Cách này tương tự cách 1, ngoại trừ không cần phải dùng mệnh đề Group By

SQL> SELECT * FROM DUP_TEST;

        ID NAME    
---------- ----------
       101 Leesin  
       101 Leesin  
       102 Yasuo   
       103 Jinx    
       103 Jinx    
       103 Jinx    
       104 Messi   
       106 Ronaldo 
       105 Alistar 
       105 Alistar 

10 rows selected.

SQL> DELETE DUP_TEST T1
WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM DUP_TEST T2 WHERE T1.ID = T2.ID);

4 rows deleted.

SQL> SELECT * FROM DUP_TEST;

        ID NAME    
---------- ----------
       101 Leesin  
       102 Yasuo   
       103 Jinx    
       104 Messi   
       106 Ronaldo 
       105 Alistar 

6 rows selected.

Cách 3: Sử dụng ROW_NUMBER()
ROW_NUMBER() là Analytical function được dùng để xếp hạng các hàng theo từng nhóm ID.

SQL> SELECT ID, NAME, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) RN FROM DUP_TEST;

        ID NAME               RN
---------- ---------- ----------
       101 Leesin              1
       101 Leesin              2
       102 Yasuo               1
       103 Jinx                1
       103 Jinx                2
       103 Jinx                3
       104 Messi               1
       105 Alistar             1
       105 Alistar             2
       106 Ronaldo             1

10 rows selected.

Nhìn vào kết quả phía trên, column RN đã đánh xếp hạng theo từng nhóm ID dựa trên mệnh đề ORDER BY: có 2 ID = 101, 1 ID = 102, 3 ID = 103, 1 ID = 104, 2 ID = 105 và 1 ID = 106.
Vậy để xóa những hàng bị 'đúp', ta chỉ cần 'where RN > 1' là đủ.

SQL> DELETE DUP_TEST
WHERE ROWID IN
(
    SELECT RID FROM
    (
        SELECT ROWID RID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) RN FROM DUP_TEST
    )
WHERE RN > 1
);

4 rows deleted.

SQL> SELECT * FROM DUP_TEST;

        ID NAME    
---------- ----------
       101 Leesin  
       102 Yasuo   
       103 Jinx    
       104 Messi   
       106 Ronaldo 
       105 Alistar 

6 rows selected.

Cách 4: Sử dụng DENSE_RANK()

SQL> SELECT ROWID RID, NAME, DENSE_RANK() OVER (PARTITION BY ID ORDER BY ROWID) DR FROM DUP_TEST;

RID                NAME               DR
------------------ ---------- ----------
AADEQzAAGAAAGblAAA Leesin              1
AADEQzAAGAAAGblAAB Leesin              2
AADEQzAAGAAAGblAAC Yasuo               1
AADEQzAAGAAAGblAAD Jinx                1
AADEQzAAGAAAGblAAE Jinx                2
AADEQzAAGAAAGblAAF Jinx                3
AADEQzAAGAAAGblAAG Messi               1
AADEQzAAGAAAGblAAI Alistar             1
AADEQzAAGAAAGblAAJ Alistar             2
AADEQzAAGAAAGblAAH Ronaldo             1

10 rows selected.

SQL> DELETE DUP_TEST
WHERE ROWID IN
(
    SELECT RID FROM
    (
        SELECT ROWID RID, DENSE_RANK() OVER (PARTITION BY ID ORDER BY ROWID) DR FROM DUP_TEST
    )
WHERE DR > 1
);

4 rows deleted.

SQL> SELECT * FROM DUP_TEST;

        ID NAME    
---------- ----------
       101 Leesin  
       102 Yasuo   
       103 Jinx    
       104 Messi   
       106 Ronaldo 
       105 Alistar 

6 rows selected.

Ở đây bạn có thể sử dụng được cả RANK() và DENSE_RANK() vì cả hai sẽ cung cấp bản ghi duy nhất khi Order by ROWID.

Kết: bạn có dùng bất kỳ các phương pháp nào ở trên để xóa đi các duplicate records tùy theo yêu cầu và khối lượng dữ liệu.

Hãy tham gia và để lại nhận xét của bạn vào bên dưới. Càng nhiều người tham gia, chúng ta càng được hưởng lợi nhiều =)). Vì vậy, để lại những suy nghĩ của bạn trước khi rời khỏi trang nhé.