Ngoại lai và ràng buộc

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

• Phân biệt ngoại lai lỗi và ngoại lai thật.
• Phát hiện ngoại lai bằng IQR và khoảng hợp lệ.
• Gắn cờ thay vì xoá khi chưa chắc.
• Thêm ràng buộc để ngăn lỗi tái diễn.
• Ghi log mọi thay đổi dữ liệu.

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

Ngoại lai là giá trị cách xa phần còn lại. Có ba loại, xử lý khác nhau:

Không có công thức tự động phân biệt ba loại. Phải xem bản ghi gốc và hiểu nghiệp vụ.

Khoảng hợp lệ trước, thống kê sau

Cách nhanh nhất là định nghĩa miền giá trị hợp lệ theo nghiệp vụ:

SELECT *
FROM orders
WHERE quantity <= 0
   OR quantity > 1000
   OR unit_price < 0
   OR order_date > CURRENT_DATE;

Ràng buộc này bắt được lỗi rõ ràng mà không cần thống kê. Với dữ liệu bán lẻ, quantity âm hoặc ngày trong tương lai gần như chắc chắn là lỗi.

Phát hiện bằng IQR

Với dữ liệu liên tục, dùng khoảng tứ phân vị:

IQR = Q3 − Q1
Ngưỡng dưới = Q1 − 1.5 × IQR
Ngưỡng trên = Q3 + 1.5 × IQR

WITH tu_phan_vi AS (
  SELECT
    percentile_cont(0.25) WITHIN GROUP (ORDER BY unit_price) AS q1,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY unit_price) AS q3
  FROM order_items
)
SELECT oi.*
FROM order_items oi, tu_phan_vi t
WHERE oi.unit_price < t.q1 - 1.5 * (t.q3 - t.q1)
   OR oi.unit_price > t.q3 + 1.5 * (t.q3 - t.q1);

IQR bền vững hơn z-score khi dữ liệu lệch, phù hợp với doanh thu và giá — hai cột thường có đuôi dài.

Với dữ liệu gần chuẩn, z-score (|z| > 3) cũng dùng được, nhưng đừng áp dụng cho dữ liệu lệch mạnh: chính các ngoại lai làm lệch trung bình và độ lệch chuẩn, khiến chúng "che" nhau.

Gắn cờ thay vì xoá

Trừ khi chắc chắn là lỗi, đừng xoá. Thêm cột đánh dấu:

ALTER TABLE order_items
  ADD COLUMN is_outlier boolean NOT NULL DEFAULT false,
  ADD COLUMN outlier_reason text;

UPDATE order_items
SET is_outlier = true,
    outlier_reason = 'quantity > 1000'
WHERE quantity > 1000;

Lợi ích:

• Báo cáo có thể loại hoặc giữ ngoại lai tuỳ mục đích.
• Có thể kiểm tra lại sau khi hiểu nguyên nhân.
• Không mất dữ liệu gốc.

Nếu buộc phải sửa, lưu giá trị cũ:

UPDATE order_items
SET quantity_original = quantity,
    quantity = 1000
WHERE id = 12345 AND quantity > 1000;

Ràng buộc để ngăn tái diễn

Sau khi làm sạch, thêm ràng buộc ở tầng cơ sở dữ liệu:

ALTER TABLE order_items
  ADD CONSTRAINT quantity_duong CHECK (quantity > 0),
  ADD CONSTRAINT gia_khong_am CHECK (unit_price >= 0);

ALTER TABLE orders
  ADD CONSTRAINT ngay_khong_tuong_lai CHECK (order_date <= CURRENT_DATE);

Lưu ý: CHECK với CURRENT_DATE không được PostgreSQL cho phép vì hàm không bất biến. Thay bằng kiểm tra ở tầng ứng dụng hoặc ràng buộc theo ngày cố định khi cần.

Ràng buộc tốt hơn việc dọn dẹp định kỳ: lỗi bị chặn ngay tại nguồn.

Ghi log thay đổi

Mọi thao tác sửa dữ liệu nên để lại dấu vết:

CREATE TABLE data_fix_log (
  id          BIGSERIAL PRIMARY KEY,
  table_name  text NOT NULL,
  row_id      bigint NOT NULL,
  column_name text NOT NULL,
  old_value   text,
  new_value   text,
  reason      text,
  fixed_by    text,
  fixed_at    timestamptz NOT NULL DEFAULT now()
);

Không có log, không ai trả lời được "vì sao dòng này khác bản gốc" sáu tháng sau.

Kiểm tra sau khi xử lý

[ ] Số dòng trước = số dòng sau (nếu chỉ gắn cờ)
[ ] Không còn giá trị âm ở cột số lượng/giá
[ ] Phân vị P99 và max đã hợp lý
[ ] Mọi thay đổi có bản ghi trong data_fix_log
[ ] Ràng buộc mới đã được thêm
[ ] Báo cáo cũ không đổi ngoài dự kiến

Dòng cuối quan trọng: xử lý ngoại lai có thể làm thay đổi số liệu lịch sử. Cần thông báo cho người dùng báo cáo.

Thực hành

1. Viết truy vấn tìm quantity <= 0 hoặc > 1000.
2. Tính ngưỡng IQR cho unit_price và đếm ngoại lai.
3. Thêm cột is_outlier và gắn cờ cho một nhóm.
4. Thêm hai ràng buộc CHECK hợp lệ.
5. Viết một bản ghi log mẫu cho một lần sửa.

Lỗi thường gặp

Tóm tắt

• Ba loại ngoại lai: lỗi, thật hiếm, sai đơn vị — xử lý khác nhau.
• Kiểm tra miền hợp lệ nghiệp vụ trước, thống kê sau.
• IQR bền vững hơn z-score với dữ liệu lệch.
• Gắn cờ thay vì xoá; lưu giá trị cũ khi sửa.
• Thêm ràng buộc và ghi log để ngăn tái diễn.