SQL Xoá Trùng: Phương Pháp Toàn Diện và Thực Tiễn Tốt Nhất

Bản ghi trùng lặp là vấn đề phổ biến có thể làm ảnh hưởng đến tính toàn vẹn dữ liệu và hiệu năng cơ sở dữ liệu. Loại bỏ các bản ghi trùng lặp là điều cần thiết để duy trì độ chính xác dữ liệu, tối ưu hoá lưu trữ và cải thiện hiệu năng truy vấn. Trong bài viết này, chúng ta sẽ khám phá nhiều kỹ thuật để xoá các hàng trùng lặp trong SQL, phù hợp với nhiều trường hợp sử dụng và hệ quản trị cơ sở dữ liệu khác nhau.

Khi bắt đầu, tôi rất khuyến nghị các khoá học Introduction to SQL và Learn SQL của DataCamp để nắm vững kiến thức nền tảng về trích xuất và phân tích dữ liệu bằng SQL. Ngoài ra, tôi thấy SQL Basics Cheat Sheet mà bạn có thể tải xuống là tài liệu tham khảo hữu ích vì nó tổng hợp các hàm SQL phổ biến nhất.

TL;DR

• Sử dụng SELECT DISTINCT hoặc GROUP BY để truy xuất các hàng duy nhất mà không sửa đổi bảng
• Sử dụng ROW_NUMBER() với CTE và DELETE để kiểm soát chính xác việc xoá vĩnh viễn những bản trùng nào
• Sử dụng DELETE với truy vấn con (NOT IN / MIN()) để khử trùng lặp đơn giản trên mọi Hệ QTCSDL
• Với tập dữ liệu lớn, dùng bảng tạm để xử lý xoá trùng theo lô một cách an toàn
• Phòng ngừa trùng lặp chủ động với khoá chính, ràng buộc duy nhất và chuẩn hoá cơ sở dữ liệu đúng cách

Tìm hiểu Các Hàng Trùng Lặp trong SQL

Hàng trùng lặp trong SQL là các bản ghi trong một bảng có giá trị giống hệt nhau trên toàn bộ hoặc một số cột được chọn. Các nguyên nhân phổ biến gây ra trùng lặp bao gồm:

• Thiếu Khoá Chính: Khi bảng không có khoá chính hoặc ràng buộc duy nhất, không có cơ chế để ngăn chèn dữ liệu trùng lặp. Điều này có thể xảy ra khi bảng chưa được chuẩn hoá và/hoặc có vấn đề về phụ thuộc bắc cầu.
• Vấn đề Tích hợp Dữ liệu: Khi hợp nhất dữ liệu từ nhiều nguồn, phép nối không đúng hoặc không nhất quán định dạng dữ liệu có thể vô tình tạo ra trùng lặp.
• Lỗi Nhập Liệu Thủ Công: Lỗi do con người, như nhập cùng một bản ghi nhiều lần, cũng là nguyên nhân phổ biến gây trùng lặp.

Trong phần còn lại của bài viết, chúng ta sẽ xem cách xoá trùng trong SQL, chia làm hai phần. Phần đầu, chúng ta đề cập cách loại bỏ trùng trong dữ liệu bạn truy xuất cho báo cáo hoặc dashboard; phần hai, chúng ta xem cách xoá trùng ngay trong cơ sở dữ liệu.

Cách Xác định Các Hàng Trùng Lặp

Trước khi xoá trùng, hãy xác định hàng nào bị trùng. Dùng GROUP BY với HAVING COUNT(*) > 1 để tìm các hàng xuất hiện nhiều hơn một lần:

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

Truy vấn này trả về mỗi Name xuất hiện nhiều hơn một lần kèm theo số lần xuất hiện. Bạn có thể mở rộng sang nhiều cột bằng cách thêm chúng vào cả mệnh đề SELECT và GROUP BY.

Để xem tất cả các hàng với thứ hạng thể hiện vị trí trong mỗi nhóm trùng, dùng ROW_NUMBER():

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

Những hàng có row_num > 1 là bản trùng. Sau khi xác định, hãy chọn phương pháp xoá phù hợp ở các phần dưới.

Phương pháp Loại bỏ Trùng trong Dữ liệu Bạn Truy xuất

Có nhiều cách để loại bỏ trùng khi truy xuất bản ghi trong SQL. Mỗi cách phụ thuộc vào Hệ QTCSDL như SQL Server, MySQL và PostgreSQL. Phần này trình bày các phương pháp loại bỏ trùng, đồng thời lưu ý các điểm đặc thù cho từng hệ. Lưu ý, các phương pháp này chỉ lọc dữ liệu và trả về bản ghi duy nhất, không sửa đổi bảng gốc.

Sử dụng từ khóa DISTINCT

Từ khoá DISTINCT được dùng trong câu lệnh SELECT để truy xuất các hàng duy nhất. Cú pháp DISTINCT để loại bỏ trùng tương tự trên MySQL, PostgreSQL và SQL Server. Truy vấn dưới đây sẽ truy xuất các tên khách hàng duy nhất từ bảng customers.

SELECT DISTINCT Name 
FROM customers;

Sử dụng GROUP BY với các hàm tổng hợp

Mệnh đề GROUP BY kết hợp với các hàm tổng hợp như MAX(), MIN() hoặc COUNT() có thể giúp loại bỏ bản ghi trùng khỏi bảng. Mệnh đề GROUP BY hỗ trợ chọn bản ghi cụ thể để giữ lại trong khi xoá các bản trùng khác.

Giả sử bạn muốn xoá các bản ghi khách hàng trùng lặp nhưng giữ lại bản có ID cao nhất. Bạn sẽ dùng GROUP BY với hàm MAX() như dưới đây.

-- 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 và SQL Server hỗ trợ cú pháp trên của GROUP BY với hàm tổng hợp và mệnh đề JOIN.

Sử dụng ROW_NUMBER() với Common Table Expressions (CTE)

Với hàm ROW_NUMBER() kết hợp CTE, bạn có thể lọc bản trùng theo tiêu chí của mình. Hàm ROW_NUMBER, khi dùng với các mệnh đề PARTITION BY và ORDER BY, gán số thứ tự duy nhất cho mỗi hàng. Cách này cho phép lọc ra các hàng không đáp ứng tiêu chí cần giữ.

Truy vấn sau xác định bản trùng và loại bỏ tất cả trừ lần xuất hiện đầu tiên.

-- 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;

Phương pháp này hoạt động tốt trên các phiên bản hiện đại của SQL Server, MySQL và PostgreSQL. Hữu ích cho tập dữ liệu lớn hoặc điều kiện phức tạp hơn, vì bạn có thể chỉ định chính xác bản trùng nào được giữ.

Xoá trùng bằng self-JOIN

Self-join cho phép so sánh một bảng với chính nó, hữu ích để xác định và xoá hàng trùng bằng cách so sánh bản ghi theo tiêu chí nhất định. Ví dụ sau dùng self-join để xoá hàng có ID lớn hơn, chỉ giữ lần xuất hiện đầu tiên của mỗi tên.

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

Cách trên hoạt động trên hầu hết hệ CSDL lớn, bao gồm SQL Server, MySQL và PostgreSQL. Xem khoá học Intermediate SQL để tìm hiểu thêm về dùng hàm tổng hợp và JOIN để lọc dữ liệu.

Phương pháp Xoá Trùng trong Cơ sở Dữ liệu

Ngoài việc loại bỏ trùng bằng truy vấn, bạn cũng có thể xoá vĩnh viễn chúng khỏi cơ sở dữ liệu. Cách tiếp cận này quan trọng để duy trì chất lượng dữ liệu. Dưới đây là các phương pháp dùng để xoá trùng khỏi cơ sở dữ liệu.

Sử dụng ROW_NUMBER() và DELETE

Hàm ROW_NUMBER() gán số thứ tự cho các hàng trong một phân vùng xác định. Khi dùng với câu lệnh DELETE, nó giúp xác định trùng lặp bằng cách xếp hạng các hàng dựa trên cột cụ thể và xoá những bản không mong muốn. Phương pháp này áp dụng cho MySQL hiện đại (từ 8.0), PostgreSQL và SQL Server.

Giả sử bạn muốn xoá các bản ghi khách hàng trùng dựa trên cột Name, chỉ giữ lại lần xuất hiện đầu tiên (ID nhỏ nhất):

-- 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);

Sử dụng DELETE với truy vấn con

Đôi khi, thao tác DELETE đơn giản với truy vấn con có thể xoá trùng khỏi cơ sở dữ liệu. Cách này phù hợp với các phiên bản cũ của MySQL hoặc PostgreSQL nơi ROW_NUMBER() có thể chưa khả dụng.

Truy vấn dưới đây xoá các hàng trong bảng customers nơi ID không phải là nhỏ nhất cho mỗi Name, chỉ giữ hàng có ID nhỏ nhất cho mỗi Name duy nhất.

-- 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
);

Sử dụng GROUP BY với mệnh đề HAVING

Khi cần kiểm tra giá trị trùng ở các cột cụ thể, mệnh đề GROUP BY kết hợp HAVING có thể dùng để xác định trùng. Cách này cho phép xoá các hàng cụ thể theo tiêu chí đã cho. Tương thích với SQL Server, MySQL và PostgreSQL.

Các truy vấn sau đầu tiên xác định tên xuất hiện nhiều hơn một lần, sau đó xoá các bản trùng và giữ hàng có ID nhỏ nhất cho mỗi 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
);

Sử dụng bảng tạm cho xử lý theo lô

Bảng tạm hiệu quả cho xử lý theo lô và xoá trùng trong tập dữ liệu lớn. Cách này hữu ích khi một truy vấn đơn lẻ có thể gây vấn đề hiệu năng. Truy vấn sau tạo bảng tạm để lưu ID nhỏ nhất cho mỗi Name và xoá các hàng trong customers nơi ID không nằm trong bảng 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;

Cú pháp dùng CREATE TEMPORARY TABLE chỉ được hỗ trợ trong MySQL và PostgreSQL.

Xoá Trùng trong SQL Server

SQL Server cung cấp nhiều cách xoá bản ghi trùng khỏi cơ sở dữ liệu. Các cách này gồm dùng DISTINCT với INTO, ROW_NUMBER() và bảng tạm.

Sử dụng DISTINCT với INTO

Bạn có thể dùng từ khoá DISTINCT trong câu lệnh SELECT để tạo bảng mới với các bản ghi duy nhất. Bạn có thể xoá bảng cũ sau khi xác minh bảng mới chứa đúng bản ghi cần. Ví dụ sau tạo bảng unique_customers với bản ghi duy nhất từ bảng 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';

Sử dụng ROW_NUMBER()

Bạn cũng có thể dùng hàm ROW_NUMBER() để xoá bản ghi trùng trong SQL Server. Giả sử bạn có bảng Customers với các hàng trùng dựa trên cột CustomerName, và bạn muốn xoá tất cả trừ lần xuất hiện đầu tiên cho mỗi nhóm trùng.

-- 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;

Sử dụng bảng tạm

Vì SQL Server không hỗ trợ CREATE TEMPORARY TABLE, bạn dùng SELECT INTO. Bảng tạm trong SQL Server dùng tiền tố # cho tên bảng.

-- 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;

Tôi gợi ý bạn thử lộ trình kỹ năng SQL Server Fundamentals để cải thiện kỹ năng nối bảng và phân tích dữ liệu. Lộ trình nghề nghiệp SQL Server Developer sẽ trang bị cho bạn kỹ năng viết, xử lý sự cố và tối ưu truy vấn trên SQL Server.

Tham khảo Nhanh: Các Phương pháp Khử Trùng trong SQL

Bảng dưới đây tóm tắt tất cả phương pháp khử trùng được đề cập trong bài, giúp bạn nhanh chóng chọn cách phù hợp với tình huống.

Thực Tiễn Tốt Nhất

Hàng trùng lặp là vấn đề phổ biến ảnh hưởng đến chất lượng dữ liệu và hiệu năng cơ sở dữ liệu. Cân nhắc các thực tiễn tốt dưới đây để ngăn bản ghi trùng được chèn vào cơ sở dữ liệu của bạn.

• Dùng Khoá Chính: Cột khoá chính đảm bảo mỗi bản ghi là duy nhất, ngăn giá trị trùng đi vào bảng.
• Áp dụng Ràng buộc Duy nhất: Thiết lập ràng buộc duy nhất cho bất kỳ cột nào để đảm bảo không có trùng lặp ở các cột không phải khoá chính, như email hoặc số điện thoại.
• Thiết kế và Chuẩn hoá CSDL Đúng: Thiết kế lược đồ hiệu quả và chuẩn hoá cơ sở dữ liệu giúp giảm dư thừa và dữ liệu trùng. Cách này đảm bảo mỗi bản ghi được lưu ở bảng phù hợp.
• Dùng Chỉ mục Duy nhất: Dùng chỉ mục duy nhất để đảm bảo một số tổ hợp cột là duy nhất mà không cần ràng buộc ở cấp bảng cho toàn bộ tập dữ liệu.
• Kiểm tra Dữ liệu Định kỳ: Thực hiện các đợt kiểm tra dữ liệu định kỳ bằng cách chạy truy vấn để xác định trùng tiềm ẩn theo quy tắc nghiệp vụ của bạn.

Kết luận

Xác định và loại bỏ các hàng trùng lặp rất quan trọng để duy trì hiệu quả CSDL và độ chính xác dữ liệu. Luôn sao lưu dữ liệu trước khi chỉnh sửa để tránh mất mát ngoài ý muốn.

Nếu bạn muốn trở thành nhà phân tích dữ liệu thành thạo, hãy xem lộ trình nghề nghiệp Associate Data Analyst in SQL để học các kỹ năng cần thiết. Khoá học Reporting in SQL cũng phù hợp nếu bạn muốn học cách xây dựng dashboard chuyên nghiệp bằng SQL. Cuối cùng, tôi khuyến nghị lấy SQL Associate Certification để chứng minh năng lực sử dụng SQL cho phân tích dữ liệu và nổi bật giữa các chuyên gia dữ liệu khác.