SQL Hapus Duplikasi: Metode Komprehensif dan Praktik Terbaik

Catatan ganda (duplicate) adalah masalah umum yang dapat mengganggu integritas data dan kinerja basis data. Menghapus duplikasi ini penting untuk menjaga keakuratan data, mengoptimalkan penyimpanan, dan meningkatkan kinerja kueri. Dalam artikel ini, kita akan membahas berbagai teknik untuk menghapus baris duplikat di SQL, disesuaikan dengan beragam kasus penggunaan dan sistem manajemen basis data.

Saat memulai, saya sangat menyarankan mengikuti kursus Introduction to SQL dan Learn SQL dari DataCamp untuk mempelajari dasar-dasar mengekstrak dan menganalisis data menggunakan SQL. Selain itu, saya menemukan SQL Basics Cheat Sheet yang dapat Anda unduh, sangat berguna sebagai referensi karena memuat fungsi-fungsi SQL paling umum.

TL;DR

• Gunakan SELECT DISTINCT atau GROUP BY untuk mengambil baris unik tanpa memodifikasi tabel
• Gunakan ROW_NUMBER() dengan CTE dan DELETE untuk kendali presisi atas duplikasi mana yang dihapus permanen
• Gunakan DELETE dengan subkueri (NOT IN / MIN()) untuk pendekatan deduplikasi yang sederhana di DBMS mana pun
• Untuk dataset besar, gunakan tabel sementara untuk memproses penghapusan duplikasi secara batch dengan aman
• Cegah duplikasi secara proaktif dengan primary key, unique constraint, dan normalisasi basis data yang tepat

Memahami Baris Duplikat di SQL

Baris duplikat di SQL mengacu pada rekaman dalam sebuah tabel yang berisi nilai identik pada seluruh atau sebagian kolom. Penyebab umum baris duplikat di SQL meliputi hal-hal berikut:

• Primary Key Tidak Ada: Ketika tabel tidak memiliki primary key atau unique constraint, tidak ada mekanisme yang mencegah penyisipan data duplikat. Ini dapat terjadi ketika sebuah tabel tidak dinormalisasi dan/atau terdapat masalah ketergantungan transitif.
• Masalah Integrasi Data: Saat menggabungkan dataset dari berbagai sumber, join yang tidak tepat atau inkonsistensi format data dapat secara tidak sengaja menimbulkan duplikasi.
• Kesalahan Entri Data Manual: Kesalahan manusia, seperti memasukkan rekaman yang sama berkali-kali, juga merupakan penyebab umum baris duplikat.

Di sisa artikel, kita akan melihat cara menghapus duplikasi di SQL, dan kita akan membaginya menjadi dua bagian. Pada bagian pertama, kita bahas cara menghapus duplikasi pada data yang Anda ambil untuk laporan atau dasbor; pada bagian kedua, kita lihat cara menghapus duplikasi di dalam basis data.

Cara Mengidentifikasi Baris Duplikat

Sebelum menghapus duplikasi, identifikasi baris mana yang terduplikasi. Gunakan GROUP BY dengan HAVING COUNT(*) > 1 untuk menemukan baris yang muncul lebih dari sekali:

SELECT Name, COUNT(*) AS duplicate_count
FROM customers
GROUP BY Name
HAVING COUNT(*) > 1;

Kueri ini mengembalikan setiap Name yang muncul lebih dari sekali, beserta jumlah kemunculannya. Anda dapat memperluasnya ke beberapa kolom dengan menambahkannya ke klausa SELECT dan GROUP BY.

Untuk melihat semua baris dengan peringkat yang menunjukkan posisinya dalam setiap grup duplikat, gunakan ROW_NUMBER():

SELECT ID, Name,
       ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID) AS row_num
FROM customers;

Baris dengan row_num > 1 adalah duplikat. Setelah diidentifikasi, pilih metode penghapusan yang sesuai dari bagian di bawah.

Metode Menghapus Duplikasi pada Data yang Anda Ambil

Ada berbagai metode untuk menghapus duplikasi saat mengambil rekaman di SQL. Setiap metode bergantung pada DBMS, seperti SQL Server, MySQL, dan PostgreSQL. Pada bagian ini, kita akan melihat metode-metode penghapusan duplikasi sambil menyoroti pertimbangan khusus untuk tiap basis data. Perlu diingat, metode ini memfilter data dan mengembalikan rekaman unik, serta tidak memodifikasi tabel dasarnya.

Menggunakan kata kunci DISTINCT

Kata kunci DISTINCT digunakan dalam pernyataan SELECT untuk mengambil baris unik. Sintaks DISTINCT untuk menghapus duplikasi serupa untuk database MySQL, PostgreSQL, dan SQL Server. Kueri di bawah akan mengambil nama pelanggan unik dari tabel customers.

SELECT DISTINCT Name 
FROM customers;

Menggunakan GROUP BY dengan fungsi agregat

Klausa GROUP BY yang dikombinasikan dengan fungsi agregat seperti MAX(), MIN(), atau COUNT() dapat membantu menghapus rekaman duplikat dari tabel. Klausa GROUP BY membantu memilih rekaman tertentu untuk dipertahankan sambil menghapus duplikasi lainnya.

Misalkan Anda ingin menghapus rekaman pelanggan duplikat tetapi mempertahankan yang memiliki ID tertinggi. Anda akan menggunakan klausa GROUP BY dengan fungsi MAX(), seperti di bawah ini.

-- Delete duplicate rows from the 'customers' table (aliased as c1)
DELETE c1
FROM customers c1
-- Find the maximum ID for each unique Name
JOIN (
    SELECT Name, MAX(ID) AS MaxID
    FROM customers
    GROUP BY Name
) c2
-- Match rows based on 'Name' and keep the row with the maximum ID
ON c1.Name = c2.Name 
AND c1.ID < c2.MaxID;

MySQL dan SQL Server mendukung sintaks GROUP BY dengan fungsi agregat dan klausa JOIN di atas. 

Menggunakan ROW_NUMBER() dengan Common Table Expressions (CTE)

Dengan fungsi ROW_NUMBER() yang dikombinasikan dengan Common Table Expression (CTE), Anda dapat memfilter duplikasi berdasarkan kriteria Anda. Fungsi ROW_NUMBER, saat digunakan dengan klausa PARTITION BY dan ORDER BY, memberikan nomor berurutan unik untuk setiap baris. Metode ini memungkinkan pemfilteran baris yang tidak memenuhi kriteria yang diperlukan.

Kueri berikut mengidentifikasi duplikasi dan menghapus semua kecuali kemunculan pertama.

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Select only the unique records where RowNum = 1
SELECT ID, Name
FROM CTE
WHERE RowNum = 1;

Metode ini bekerja baik untuk versi modern SQL Server, MySQL, dan PostgreSQL. Ini berguna untuk dataset yang lebih besar atau kondisi yang lebih kompleks, karena memungkinkan Anda menentukan dengan tepat duplikasi mana yang dipertahankan.

Menghapus duplikasi menggunakan self-JOIN

Self-join memungkinkan Anda membandingkan tabel dengan dirinya sendiri, sehingga membantu mengidentifikasi dan menghapus baris duplikat dengan membandingkan rekaman berdasarkan kriteria tertentu. Contoh berikut menggunakan self-join untuk menghapus baris dengan ID yang lebih tinggi, hanya mempertahankan kemunculan pertama dari setiap nama.

-- Delete duplicate rows using self-join
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.Name = c2.Name AND c1.ID > c2.ID;

Metode di atas bekerja pada database utama, termasuk SQL Server, MySQL, dan PostgreSQL. Lihat kursus Intermediate SQL kami untuk mempelajari lebih lanjut tentang penggunaan fungsi agregat dan join untuk memfilter data.

Metode Menghapus Duplikasi di Basis Data

Selain menghapus rekaman duplikat menggunakan kueri, Anda juga dapat menghapusnya secara permanen dari basis data. Pendekatan ini penting untuk menjaga kualitas data. Metode berikut digunakan untuk menghapus duplikasi dari basis data.

Menggunakan ROW_NUMBER() dan DELETE

Fungsi ROW_NUMBER() memberikan nomor berurutan pada baris dalam sebuah partisi yang ditentukan. Saat digunakan dengan pernyataan DELETE, ini membantu mengidentifikasi duplikasi dengan memberi peringkat baris berdasarkan kolom tertentu dan menghapus rekaman yang tidak diinginkan. Metode ini berlaku untuk versi modern MySQL (mulai 8.0), PostgreSQL, dan SQL Server.

Misalkan Anda ingin menghapus rekaman pelanggan duplikat berdasarkan kolom Name, dengan mempertahankan hanya kemunculan pertama (ID terkecil):

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Delete rows from the 'customers' table where the row number is greater than 1
DELETE FROM customers
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);

Menggunakan DELETE dengan subkueri

Terkadang, operasi DELETE sederhana menggunakan subkueri dapat menghapus duplikasi dari basis data. Metode ini cocok untuk versi MySQL atau PostgreSQL yang lebih lama di mana ROW_NUMBER() mungkin belum tersedia.

Kueri di bawah menghapus baris dari tabel customers di mana ID bukan yang minimum untuk setiap Name, sehingga hanya mempertahankan baris dengan ID terkecil untuk setiap Name unik.

-- Delete rows from the 'customers' table
DELETE FROM customers
WHERE ID NOT IN (
    -- Subquery to find the minimum ID for each unique Name
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

Menggunakan GROUP BY dengan klausa HAVING

Saat Anda perlu memeriksa nilai duplikat pada kolom tertentu, klausa GROUP BY yang dikombinasikan dengan klausa HAVING dapat digunakan untuk mengidentifikasi duplikasi. Metode ini memungkinkan Anda menghapus baris tertentu berdasarkan kriteria yang diberikan. Metode ini kompatibel dengan SQL Server, MySQL, dan PostgreSQL.

Kueri berikut terlebih dahulu mengidentifikasi nama mana yang muncul lebih dari sekali, lalu menghapus duplikat sambil mempertahankan baris dengan ID terkecil untuk setiap Name.

-- Step 1: Identify which Names have duplicates
SELECT Name, COUNT(*) AS duplicate_count
FROM customers
GROUP BY Name
HAVING COUNT(*) > 1;

-- Step 2: Delete duplicate rows, keeping the smallest ID for each Name
DELETE FROM customers
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

Menggunakan tabel sementara untuk pemrosesan batch

Tabel sementara efisien untuk pemrosesan batch dan menghapus duplikasi pada dataset besar. Metode ini berguna ketika kueri tunggal dapat menimbulkan masalah kinerja. Kueri berikut membuat tabel sementara untuk menyimpan ID minimum untuk setiap Name dan menghapus baris dari tabel customers di mana ID tidak ada di tabel temp_customers.

-- Create a temporary table with unique records
CREATE TEMPORARY TABLE temp_customers AS
SELECT MIN(ID) AS KeepID, Name
FROM customers
GROUP BY Name;

-- Delete duplicates not in the temporary table
DELETE FROM customers
WHERE ID NOT IN (SELECT KeepID FROM temp_customers);

-- Clean up
DROP TABLE temp_customers;

Sintaks di atas yang menggunakan CREATE TEMPORARY TABLE hanya didukung pada database MySQL dan PostgreSQL. 

Menghapus Duplikasi di SQL Server

SQL Server menawarkan berbagai metode untuk menghapus rekaman duplikat dari basis data. Metode tersebut termasuk menggunakan DISTINCT dengan INTO, ROW_NUMBER(), dan tabel sementara.

Menggunakan DISTINCT dengan INTO

Anda dapat menggunakan kata kunci DISTINCT dalam pernyataan SELECT untuk membuat tabel baru dengan rekaman unik. Anda dapat menghapus tabel lama setelah memverifikasi tabel baru memiliki rekaman yang ditentukan. Contoh berikut membuat tabel unique_customers dengan rekaman unik dari tabel customers.

-- Select distinct rows from 'customers' and create a new table 'unique_customers'
SELECT DISTINCT *
INTO unique_customers
FROM customers;
-- Drop the original 'customers' table to remove it from the database
DROP TABLE customers;
-- Rename the 'unique_customers' table to 'customers' to replace the original table
EXEC sp_rename 'unique_customers', 'customers';

Menggunakan ROW_NUMBER()

Anda juga dapat menggunakan fungsi ROW_NUMBER() untuk menghapus rekaman duplikat di SQL Server. Misalkan Anda memiliki tabel Customers dengan baris duplikat berdasarkan kolom CustomerName, dan Anda ingin menghapus semua kecuali kemunculan pertama untuk setiap grup duplikat.

-- Common Table Expression (CTE) to assign a row number to each customer 
WITH CTE AS (
    SELECT CustomerID, CustomerName, ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY CustomerID ASC) AS RowNum
    FROM Customers
)
-- Delete rows from the CTE
DELETE FROM CTE
WHERE RowNum > 1;

Menggunakan tabel sementara

Karena SQL Server tidak mendukung fungsi CREATE TEMPORARY TABLE, Anda menggunakan fungsi SELECT INTO. Tabel sementara di SQL Server menggunakan awalan # pada nama tabel.

-- Create a temporary table
SELECT MIN(CustomerID) AS ID, CustomerName
INTO #temp_customers
FROM customers
GROUP BY CustomerName;
-- Delete rows from the 'customers' table where the ID is not in the temporary table
DELETE FROM customers
WHERE CustomerID NOT IN (SELECT ID FROM #temp_customers);
-- Optionally drop the temporary table after use
DROP TABLE #temp_customers;

Saya menyarankan mencoba jalur keterampilan SQL Server Fundamentals kami untuk meningkatkan keterampilan menggabungkan tabel dan analisis data Anda. Jalur karier SQL Server Developer akan membekali Anda dengan keterampilan untuk menulis, menelusuri kesalahan, dan mengoptimalkan kueri menggunakan SQL Server.  

Referensi Cepat: Metode Deduplikasi SQL

Tabel di bawah merangkum semua metode deduplikasi yang dibahas dalam artikel ini, sehingga Anda dapat dengan cepat memilih pendekatan yang tepat untuk situasi Anda.

Praktik Terbaik

Baris duplikat adalah masalah umum yang memengaruhi kualitas data dan kinerja basis data. Pertimbangkan praktik terbaik berikut untuk mencegah rekaman duplikat dimasukkan ke basis data Anda.

• Gunakan Primary Key: Kolom primary key memastikan setiap rekaman berisi informasi unik, sehingga mencegah nilai duplikat masuk ke tabel.
• Terapkan Unique Constraint: Menerapkan unique constraint pada kolom apa pun memastikan tidak ada duplikasi pada kolom non-primary key, seperti alamat email atau nomor telepon.
• Desain Basis Data dan Normalisasi yang Tepat: Desain skema yang efektif dan normalisasi basis data membantu mengurangi redundansi dan data duplikat. Pendekatan ini memastikan setiap rekaman disimpan pada tabel tertentu.
• Gunakan Indeks Unik: Gunakan indeks unik untuk memastikan kombinasi kolom tertentu bersifat unik tanpa memerlukan constraint tingkat tabel penuh di seluruh dataset.
• Audit Data Berkala: Lakukan audit data secara rutin dengan menjalankan kueri untuk mengidentifikasi potensi duplikasi berdasarkan aturan bisnis Anda.

Kesimpulan

Mengidentifikasi dan menghapus baris duplikat penting untuk menjaga efisiensi basis data dan keakuratan data. Selalu menjadi praktik terbaik untuk mencadangkan data Anda sebelum melakukan modifikasi guna memastikan tidak terjadi kehilangan data secara tidak sengaja.

Jika Anda tertarik menjadi analis data yang andal, lihat jalur karier Associate Data Analyst in SQL kami untuk mempelajari keterampilan yang diperlukan. Kursus Reporting in SQL juga tepat jika Anda ingin belajar membangun dasbor profesional menggunakan SQL. Terakhir, saya merekomendasikan memperoleh SQL Associate Certification untuk menunjukkan penguasaan Anda menggunakan SQL untuk analisis data dan menonjol di antara profesional data lainnya.