Chuẩn hoá và khử trùng

Bạn sẽ học được

• Chuẩn hoá chữ, khoảng trắng và định dạng trước khi so khớp.
• Tìm trùng lặp theo khóa nghiệp vụ.
• Chọn bản ghi giữ lại khi trùng.
• Khử trùng an toàn với ROW_NUMBER.
• Ghi lại các dòng đã gộp để truy vết.

Khái niệm cốt lõi

Hai bước, đúng thứ tự:

1. Chuẩn hoá — đưa giá trị về một dạng thống nhất: chữ thường, bỏ khoảng trắng thừa, bỏ dấu tiếng Việt nếu cần, chuẩn hoá số điện thoại.
2. Khử trùng — gộp các dòng đại diện cho cùng một thực thể.

Làm ngược (khử trùng trước, chuẩn hoá sau) sẽ bỏ sót những cặp trùng chỉ khác nhau ở chữ hoa hay khoảng trắng.

Chuẩn hoá chuỗi

SELECT
  email,
  lower(trim(email)) AS email_chuan,
  regexp_replace(trim(name), '\s+', ' ', 'g') AS ten_chuan
FROM customers;

Một số phép thường dùng:

Với tiếng Việt, unaccent không đổi đ thành d trong mọi cấu hình, nên cần thêm replace. Kiểm tra bằng một truy vấn nhỏ trước khi áp dụng hàng loạt.

Chuẩn hoá trước khi so sánh

So sánh không chuẩn hoá là nguồn gốc của join sai:

-- Sai: bỏ sót 'Ha Noi' và 'Hà Nội '
SELECT * FROM customers c JOIN regions r ON c.city = r.city;

-- Đúng: chuẩn hoá hai phía
SELECT *
FROM customers c
JOIN regions r
  ON lower(unaccent(trim(c.city))) = lower(unaccent(trim(r.city)));

Chuẩn hoá trong truy vấn dễ sai và chậm. Tốt hơn: tạo cột chuẩn hoá lưu sẵn (city_norm), có chỉ mục, và dùng cột đó khi join.

Tìm trùng lặp

Bước 1 — đếm nhóm trùng:

SELECT
  lower(trim(email)) AS email_chuan,
  COUNT(*) AS so_ban,
  MIN(created_at) AS tao_som_nhat,
  MAX(created_at) AS tao_muon_nhat
FROM customers
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 3 DESC;

Bước 2 — xem chi tiết một nhóm:

SELECT *
FROM customers
WHERE lower(trim(email)) = 'an.nguyen@example.com'
ORDER BY created_at;

Luôn xem chi tiết trước khi gộp. Có trường hợp hai bản ghi là hai người khác nhau dùng chung email gia đình.

Chọn bản ghi giữ lại

Quy tắc phổ biến, xếp theo thứ tự:

1. Bản ghi đầy đủ trường nhất.
2. Bản ghi tạo sớm nhất (đại diện lịch sử).
3. Bản ghi cập nhật gần nhất (thông tin mới).
4. Ưu tiên theo nguồn dữ liệu đáng tin hơn.

Chốt quy tắc với người sở hữu dữ liệu và ghi vào tài liệu. Không để quy tắc "ngầm hiểu".

Khử trùng bằng ROW_NUMBER

Cách an toàn, có thể chạy lại nhiều lần:

WITH xep_hang AS (
  SELECT
    id,
    lower(trim(email)) AS email_chuan,
    ROW_NUMBER() OVER (
      PARTITION BY lower(trim(email))
      ORDER BY
        (name IS NULL) ASC,      -- đầy đủ trước
        created_at ASC,          -- tạo sớm trước
        id ASC                   -- chốt hoà
    ) AS hang
  FROM customers
)
SELECT id, email_chuan, hang
FROM xep_hang
WHERE hang > 1;

ORDER BY phải có tiêu chí chốt hoà (id) để kết quả tất định giữa các lần chạy. Thiếu nó, hai lần chạy có thể giữ hai bản ghi khác nhau.

Gộp và truy vết

Trước khi xoá, phải chuyển tham chiếu và lưu lại:

-- 1. Bảng ánh xạ bản ghi trùng sang bản ghi giữ lại
CREATE TABLE customer_merge_map (
  duplicate_id BIGINT PRIMARY KEY,
  kept_id      BIGINT NOT NULL,
  merged_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 2. Ghi ánh xạ
INSERT INTO customer_merge_map (duplicate_id, kept_id)
SELECT id, kept_id FROM danh_sach_trung;

-- 3. Cập nhật bảng con
UPDATE orders o
SET customer_id = m.kept_id
FROM customer_merge_map m
WHERE o.customer_id = m.duplicate_id;

-- 4. Xoá bản ghi trùng sau khi kiểm tra
DELETE FROM customers c
USING customer_merge_map m
WHERE c.id = m.duplicate_id;

Bảng ánh xạ cho phép trả lời "bản ghi này đã đi đâu" sau khi gộp và hỗ trợ hoàn tác.

Thực hành

1. Chuẩn hoá tên thành phố và đếm số biến thể trước/sau.
2. Tìm email trùng sau khi chuẩn hoá chữ thường và khoảng trắng.
3. Viết truy vấn ROW_NUMBER chọn bản ghi giữ lại.
4. Tạo bảng ánh xạ gộp cho một nhóm trùng.
5. Kiểm tra một email trùng có thật sự là cùng người.

Lỗi thường gặp

Tóm tắt

• Chuẩn hoá trước, khử trùng sau.
• So sánh và join phải dùng dạng đã chuẩn hoá, tốt nhất là cột lưu sẵn.
• Tìm trùng theo khóa nghiệp vụ rồi xem chi tiết từng nhóm.
• Dùng ROW_NUMBER với tiêu chí chốt hoà để kết quả tất định.
• Cập nhật tham chiếu và lưu ánh xạ trước khi xoá.