Trong quá trình phát triển phần mềm, việc dữ liệu bị trùng lặp là vấn đề thường gặp, chẳng hạn như bản ghi đăng nhập nhiều lần của người dùng hoặc các bản cập nhật trạng thái đơn hàng trùng lặp. Việc trích xuất hiệu quả các giá trị duy nhất hoặc bản ghi mới nhất phù hợp với yêu cầu kinh doanh là cực kỳ quan trọng đối với hiệu suất hệ thống và độ chính xác dữ liệu.

Bài viết này sẽ khám phá cách sử dụng các hàm cửa sổ, truy vấn nhóm và DISTINCT trong SQL để thực hiện loại bỏ dữ liệu trùng lặp và trích xuất giá trị độc nhất trong các tình huống phức tạp, tránh để dữ liệu trùng lặp ảnh hưởng đến phân tích kinh doanh.

Một: Các Hàm và Công Nghệ SQL Cốt Lõi

Hàm/Công nghệ

Mô tả

Ví dụ

DISTINCT

Loại bỏ các dòng trùng lặp, trả về các bản ghi duy nhất

SELECT DISTINCT(user_id) FROM logins

GROUP BY

Nhóm theo cột chỉ định, trả về kết quả tổng hợp cho mỗi nhóm

SELECT user_id, MAX(login_time) FROM logins GROUP BY user_id

ROW_NUMBER()

Hàm cửa sổ, đánh số cho các bản ghi trong mỗi nhóm, thường dùng để loại bỏ trùng lặp hoặc xếp hạng

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC)

RANK()

Tương tự ROW_NUMBER(), nhưng các bản ghi có cùng thứ tự sẽ có cùng thứ hạng

RANK() OVER (PARTITION BY user_id ORDER BY score DESC)

DENSE_RANK()

Thứ hạng liên tiếp, không bỏ qua số

DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC)

Hai: Các Tình Huống Thực Tế

Tình huống 1: Truy cập bản ghi đăng nhập mới nhất của mỗi người dùng

Mô tả yêu cầu: Hệ thống lưu trữ nhiều bản ghi đăng nhập của người dùng, chúng ta cần trích xuất lần đăng nhập gần nhất cho mỗi người dùng, loại bỏ dữ liệu trùng lặp.

Cấu trúc bảng user_sessions

session_id

account_id

session_time

101

1

2024-01-01 08:30:00

102

1

2024-01-02 10:00:00

103

2

2024-01-01 09:15:00

104

2

2024-01-03 14:45:00

105

3

2024-01-02 11:00:00

Phương pháp 1: Sử dụng GROUP BY kết hợp MAX()

SELECT account_id, MAX(session_time) AS latest_session  
FROM user_sessions  
GROUP BY account_id;

Kết quả truy vấn

account_id

latest_session

1

2024-01-02 10:00:00

2

2024-01-03 14:45:00

3

2024-01-02 11:00:00

Giải thích:

• Thông qua GROUP BY account_id để nhóm cho mỗi tài khoản, MAX(session_time) trích xuất thời gian đăng nhập mới nhất trong mỗi nhóm.
• Phương pháp này hiệu quả, phù hợp cho các tình huống loại bỏ trùng lặp đơn giản, nhưng không thể trả về bản ghi đầy đủ (như session_id).

Phương pháp 2: Sử dụng hàm cửa sổ ROW_NUMBER()

SELECT session_id, account_id, session_time  
FROM (  
    SELECT session_id, account_id, session_time,  
           ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY session_time DESC) AS ranking  
    FROM user_sessions  
) ranked_sessions  
WHERE ranking = 1;

Kết quả truy vấn

session_id

account_id

session_time

102

1

2024-01-02 10:00:00

104

2

2024-01-03 14:45:00

105

3

2024-01-02 11:00:00

Giải thích:

• ROW_NUMBER() sắp xếp các bản ghi đăng nhập theo thời gian giảm dần cho mỗi tài khoản và phân vùng theo tài khoản.
• Chỉ giữ lại các bản ghi có thứ hạng là 1, tức là bản ghi đăng nhập mới nhất.
• Ưu điểm: Giữ lại tính nguyên vẹn của bản ghi gốc (bao gồm cả session_id).

Tình huống 2: Truy xuất bản ghi đơn hàng có giá trị cao nhất của mỗi khách hàng

Mô tả yêu cầu: Trong hệ thống thương mại điện tử, mỗi khách hàng có thể có nhiều đơn hàng, chúng ta cần trích xuất bản ghi đơn hàng có giá trị cao nhất của mỗi khách hàng.

Cấu trúc bảng customer_orders

order_id

customer_id

amount

order_timestamp

1001

1

500

2024-01-05 09:00:00

1002

1

800

2024-01-06 14:00:00

1003

2

1200

2024-01-04 16:30:00

1004

2

900

2024-01-05 11:00:00

1005

3

700

2024-01-06 10:00:00

Phương pháp 1: Sử dụng RANK() để giữ lại bản ghi có giá trị cao nhất

SELECT order_id, customer_id, amount, order_timestamp  
FROM (  
    SELECT order_id, customer_id, amount, order_timestamp,  
           RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS position  
    FROM customer_orders  
) ranked_orders  
WHERE position = 1;

Kết quả truy vấn

order_id

customer_id

amount

order_timestamp

1002

1

800

2024-01-06 14:00:00

1003

2

1200

2024-01-04 16:30:00

1005

3

700

2024-01-06 10:00:00

Giải thích:

• RANK() cho phép xử lý các trường hợp có giá trị cao nhất như nhau, ví dụ nếu hai đơn hàng có cùng giá trị cao nhất, cả hai bản ghi đều sẽ được giữ lại.
• Khác biệt: RANK() và DENSE_RANK() không bỏ qua số thứ tự, trong khi ROW_NUMBER() chỉ giữ lại một bản ghi.

Tình huống 3: Loại bỏ bản ghi đơn hàng trùng lặp, giữ lại đơn hàng mới nhất

Mô tả yêu cầu: Hệ thống đơn hàng có thể có các bản ghi đơn hàng được gửi đi trùng lặp, làm thế nào để giữ lại đơn hàng mới nhất của mỗi khách hàng.

Cài đặt SQL

DELETE t1  
FROM customer_orders t1  
INNER JOIN customer_orders t2  
ON t1.customer_id = t2.customer_id  
AND t1.order_timestamp < t2.order_timestamp;

Giải thích:

• Thông qua nối bảng chính nó (INNER JOIN) để so sánh thời gian đơn hàng của cùng một khách hàng, giữ lại đơn hàng mới nhất và xóa các bản ghi trùng lặp cũ hơn.

Ba: Đề Xuất Tối Ưu Hóa cho Việc Loại Bỏ Trùng Lặp và Trích Xuất Giá Trị Độc Nhất

1. Sử dụng hàm cửa sổ để nâng cao hiệu suất: Các hàm cửa sổ như ROW_NUMBER() và RANK() có thể hoàn thành việc nhóm và sắp xếp trong một lần truy vấn, giảm thiểu các thao tác truy vấn nhiều lần, tăng hiệu quả loại bỏ trùng lặp.
2. Tối ưu hóa chỉ mục: Trong các tình huống truy vấn nhóm hoặc loại bỏ trùng lặp thường xuyên, việc tạo chỉ mục cho các trường nhóm (như customer_id) và các trường thời gian (như session_time) có thể cải thiện đáng kể hiệu suất truy vấn.

CREATE INDEX idx_customer_session ON user_sessions(customer_id, session_time DESC); 3. Sử dụng cẩn thận các hàm tổng hợp: Khi chỉ cần thống kê dữ liệu, có thể sử dụng GROUP BY và MAX(), nhưng khi cần giữ lại bản ghi đầy đủ, nên chọn hàm cửa sổ để kiểm soát chi tiết.

Bốn: Tổng Kết

• Chiến lược loại bỏ trùng lặp đa dạng: Tùy theo yêu cầu kinh doanh, chọn phương pháp loại bỏ trùng lặp phù hợp, GROUP BY phù hợp cho loại bỏ trùng lặp đơn giản, ROW_NUMBER() và RANK() phù hợp cho các tình huống phức tạp.
• Hàm cửa sổ linh hoạt và hiệu quả: Các hàm như ROW_NUMBER() có thể giữ lại bản ghi đầy đủ đồng thời lọc chính xác các giá trị độc nhất, giải quyết các yêu cầu loại bỏ trùng lặp phức tạp.
• Tối ưu hóa chỉ mục: Trong môi trường dữ liệu lớn, việc sử dụng hợp lý chỉ mục có thể nâng cao hiệu suất của các truy vấn loại bỏ trùng lặp.