Giải thích ROW_NUMBER() trong MySQL (MySQL 8.0): Xếp hạng, Truy vấn Top-N và Loại bỏ trùng lặp

1. Giới thiệu

Phiên bản MySQL 8.0 đã giới thiệu nhiều tính năng mới, và một trong những tính năng đáng chú ý nhất là hỗ trợ cho các hàm cửa sổ. Trong bài viết này, chúng ta sẽ tập trung vào một trong những hàm được sử dụng thường xuyên nhất: ROW_NUMBER().

Hàm ROW_NUMBER() cung cấp các khả năng mạnh mẽ cho phân tích dữ liệu và báo cáo, giúp dễ dàng sắp xếp và xếp hạng dữ liệu dựa trên các điều kiện cụ thể. Bài viết này giải thích mọi thứ từ cách sử dụng cơ bản và các ví dụ thực tế đến các cách tiếp cận thay thế cho các phiên bản MySQL cũ hơn.

Đối tượng độc giả

• Người dùng từ cơ bản đến trung cấp có kiến thức SQL cơ bản
• Kỹ sư và nhà phân tích dữ liệu xử lý và phân tích dữ liệu bằng MySQL
• Bất kỳ ai đang xem xét nâng cấp lên phiên bản MySQL mới nhất

Lợi ích của ROW_NUMBER()

Hàm này cho phép bạn gán một số duy nhất cho mỗi hàng dựa trên các điều kiện cụ thể. Ví dụ, bạn có thể dễ dàng viết các truy vấn như “tạo xếp hạng theo thứ tự giảm dần của doanh số” hoặc “trích xuất và tổ chức dữ liệu trùng lặp” một cách ngắn gọn.

Trong các phiên bản cũ hơn, bạn thường phải viết các truy vấn phức tạp sử dụng biến do người dùng định nghĩa. Với ROW_NUMBER(), SQL của bạn trở nên đơn giản và dễ đọc hơn.

Trong bài viết này, chúng ta sẽ sử dụng các ví dụ truy vấn cụ thể và giải thích chúng theo cách thân thiện với người mới bắt đầu. Trong phần tiếp theo, chúng ta sẽ xem xét kỹ hơn về cú pháp cơ bản và hành vi của hàm này.

2. Hàm ROW_NUMBER() là gì?

Hàm ROW_NUMBER(), được thêm mới trong MySQL 8.0, là một loại hàm cửa sổ gán số thứ tự cho các hàng. Nó có thể đánh số các hàng theo thứ tự cụ thể và/hoặc trong mỗi nhóm, điều này cực kỳ hữu ích cho phân tích dữ liệu và báo cáo. Ở đây chúng ta sẽ giải thích chi tiết cú pháp cơ bản với các ví dụ thực tế.

Cú pháp cơ bản của ROW_NUMBER()

Đầu tiên, định dạng cơ bản của ROW_NUMBER() như sau.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Ý nghĩa của từng yếu tố

• ROW_NUMBER() : Gán số thứ tự cho mỗi hàng.
• OVER : Từ khóa dùng để định nghĩa cửa sổ cho hàm cửa sổ.
• PARTITION BY : Nhóm dữ liệu theo cột được chỉ định. Tùy chọn. Nếu bỏ qua, việc đánh số sẽ áp dụng cho tất cả các hàng.
• ORDER BY : Định nghĩa thứ tự dùng để gán số, tức là tiêu chí sắp xếp.

Ví dụ cơ bản

Ví dụ, giả sử bạn có một bảng tên là “sales” với dữ liệu sau.

Để gán số thứ tự trong mỗi bộ phận theo thứ tự giảm dần của doanh số, sử dụng truy vấn sau.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Kết quả

Từ kết quả này, bạn có thể thấy rằng xếp hạng theo doanh số trong mỗi bộ phận được hiển thị.

Cách sử dụng PARTITION BY

Trong ví dụ trên, dữ liệu được nhóm theo cột “department”. Điều này gán một chuỗi riêng biệt cho mỗi bộ phận.

Nếu bạn bỏ qua PARTITION BY, việc đánh số sẽ được gán cho tất cả các hàng như một chuỗi duy nhất.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Kết quả

Đặc điểm và lưu ý của ROW_NUMBER()

• Đánh số duy nhất : Ngay cả khi giá trị giống nhau, các số được gán vẫn là duy nhất.
• Xử lý NULL : Nếu ORDER BY bao gồm NULL, chúng sẽ xuất hiện đầu tiên trong thứ tự tăng dần và cuối cùng trong thứ tự giảm dần.
• Tác động đến hiệu suất : Với tập dữ liệu lớn, ORDER BY có thể tốn kém, vì vậy việc lập chỉ mục đúng cách rất quan trọng.

3. Các trường hợp sử dụng thực tế

Dưới đây là các tình huống thực tế sử dụng hàm ROW_NUMBER() của MySQL. Hàm này hữu ích trong nhiều trường hợp thực tế, chẳng hạn như xếp hạng dữ liệu và xử lý trùng lặp.

3-1. Xếp hạng trong mỗi nhóm

Ví dụ, hãy xem trường hợp bạn muốn “xếp hạng nhân viên theo doanh số trong mỗi phòng ban” bằng dữ liệu bán hàng. Sử dụng bộ dữ liệu sau làm ví dụ.

Ví dụ truy vấn: Xếp hạng doanh số theo phòng ban

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Kết quả:

Bằng cách này, mỗi phòng ban sẽ có một dãy số riêng theo thứ tự giảm dần của doanh số, giúp dễ dàng tạo ra các bảng xếp hạng.

3-2. Trích xuất N hàng đầu

Tiếp theo, hãy xem một trường hợp bạn muốn “trích xuất 3 nhân viên có doanh số cao nhất trong mỗi phòng ban.”

Ví dụ truy vấn: Trích xuất N hàng đầu

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Kết quả:

Ví dụ này chỉ lấy 3 hàng đầu theo doanh số trong mỗi phòng ban. Như bạn thấy, ROW_NUMBER() không chỉ phù hợp để xếp hạng mà còn để lọc các kết quả hàng đầu.

3-3. Tìm và loại bỏ dữ liệu trùng lặp

Cơ sở dữ liệu đôi khi chứa các bản ghi trùng lặp. Trong những trường hợp này, bạn cũng có thể xử lý chúng dễ dàng bằng ROW_NUMBER().

Ví dụ truy vấn: Phát hiện trùng lặp

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

Truy vấn này phát hiện các bản ghi trùng lặp khi có nhiều bản ghi cho cùng một tên nhân viên.

Ví dụ truy vấn: Xóa các bản ghi trùng lặp

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Tóm tắt

ROW_NUMBER() hữu ích trong nhiều tình huống, chẳng hạn như:

1. Xếp hạng trong mỗi nhóm
2. Trích xuất N hàng đầu
3. Phát hiện và xóa các bản ghi trùng lặp

Điều này làm cho việc xử lý và phân tích dữ liệu phức tạp trở nên đơn giản và hiệu quả hơn.

4. So sánh với các hàm cửa sổ khác

Trong MySQL 8.0, bên cạnh ROW_NUMBER(), còn có các hàm cửa sổ như RANK() và DENSE_RANK() có thể được sử dụng để xếp hạng và tính vị trí. Mặc dù chúng có vai trò tương tự, nhưng cách hoạt động và kết quả lại khác nhau. Ở đây chúng ta sẽ so sánh từng hàm và giải thích khi nào nên sử dụng chúng.

4-1. Hàm RANK() function

Hàm RANK() gán thứ hạng, cho cùng một thứ hạng cho các giá trị bằng nhau và bỏ qua số thứ hạng tiếp theo.

Basic syntax

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Example

Sử dụng dữ liệu sau, tính thứ hạng doanh số.

Ví dụ truy vấn: Sử dụng RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Kết quả:

Các điểm chính:

• A và B có cùng doanh số (800) đều được coi là hạng “1”.
• Hạng “2” bị bỏ qua, vì vậy C trở thành hạng “3”.

4-2. Hàm DENSE_RANK() function

Hàm DENSE_RANK() cũng gán cùng một hạng cho các giá trị bằng nhau, nhưng không bỏ qua số hạng tiếp theo.

Basic syntax

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Example

Sử dụng cùng dữ liệu như trên, thử hàm DENSE_RANK().

Ví dụ truy vấn: Sử dụng DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Kết quả:

Các điểm chính:

• A và B có cùng số lượng bán hàng (800) đều được coi là hạng “1”.
• Không giống RANK(), hạng tiếp theo bắt đầu từ “2”, do đó tính liên tục của hạng được bảo toàn.

4-3. ROW_NUMBER() khác biệt như thế nào

Hàm ROW_NUMBER() khác biệt so với hai hàm kia ở chỗ nó gán một số duy nhất ngay cả khi các giá trị giống nhau.

Ví dụ

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Kết quả:

Điểm chính:

• Ngay cả nếu các giá trị giống nhau, mỗi hàng nhận được một số duy nhất, do đó không có hạng trùng lặp.
• Điều này hữu ích khi bạn cần kiểm soát thứ tự nghiêm ngặt hoặc tính duy nhất cho từng hàng.

4-4. Tóm tắt trường hợp sử dụng nhanh

Tóm tắt

ROW_NUMBER(), RANK(), và DENSE_RANK() nên được sử dụng phù hợp tùy theo tình huống.

1. ROW_NUMBER() là tốt nhất khi bạn cần số duy nhất cho từng hàng.
2. RANK() hữu ích khi bạn muốn các giá trị bằng nhau chia sẻ hạng và bạn muốn nhấn mạnh khoảng cách hạng.
3. DENSE_RANK() phù hợp khi bạn muốn hạng liên tục mà không có khoảng trống.

5. Các lựa chọn thay thế cho các phiên bản MySQL dưới 8.0

Trong các phiên bản trước MySQL 8.0, ROW_NUMBER() và các hàm cửa sổ khác không được hỗ trợ. Tuy nhiên, bạn có thể đạt được hành vi tương tự bằng cách sử dụng biến do người dùng định nghĩa. Phần này giải thích các lựa chọn thay thế thực tế cho các phiên bản MySQL dưới 8.0.

5-1. Đánh số tuần tự sử dụng biến do người dùng định nghĩa

Trong MySQL 5.7 và các phiên bản trước đó, bạn có thể sử dụng biến do người dùng định nghĩa để gán số tuần tự cho từng hàng. Hãy xem xét ví dụ sau.

Ví dụ: Xếp hạng bán hàng theo bộ phận

Dữ liệu mẫu:

Truy vấn:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Kết quả:

5-2. Trích xuất Top N hàng

Để lấy Top N hàng, bạn có thể sử dụng biến do người dùng định nghĩa theo cách tương tự.

Truy vấn:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Kết quả:

Truy vấn này gán hạng theo bộ phận và sau đó chỉ trích xuất các hàng trong top 3.

5-3. Phát hiện và xóa trùng lặp

Bạn cũng có thể xử lý dữ liệu trùng lặp bằng cách sử dụng biến do người dùng định nghĩa.

Ví dụ truy vấn: Phát hiện trùng lặp

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Ví dụ truy vấn: Xóa trùng lặp

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Lưu ý khi sử dụng biến do người dùng định nghĩa

1. Phụ thuộc vào phiên

• Biến do người dùng định nghĩa chỉ hợp lệ trong phiên hiện tại. Chúng không thể được tái sử dụng qua các truy vấn hoặc phiên khác nhau.

1. Phụ thuộc vào thứ tự xử lý

• Biến do người dùng định nghĩa phụ thuộc vào thứ tự thực thi, do đó việc thiết lập ORDER BY đúng cách là rất quan trọng.

1. Khả năng đọc và bảo trì SQL

• Các truy vấn có thể trở nên phức tạp, do đó trong MySQL 8.0 và các phiên bản sau, sử dụng hàm cửa sổ được khuyến nghị.

Tóm tắt

Trong các phiên bản MySQL dưới 8.0, bạn có thể sử dụng các biến do người dùng định nghĩa để thực hiện đánh số thứ tự và xếp hạng thay thế cho các hàm cửa sổ. Tuy nhiên, vì các truy vấn có xu hướng trở nên phức tạp hơn, tốt nhất là nên xem xét nâng cấp lên phiên bản mới hơn bất cứ khi nào có thể.

6. Những lưu ý và Thực hành tốt nhất

Hàm ROW_NUMBER() của MySQL và các lựa chọn thay thế dựa trên biến rất tiện lợi, nhưng có những điểm quan trọng cần lưu ý để chạy chúng một cách chính xác và hiệu quả. Phần này giải thích các lưu ý thực tế và thực hành tốt nhất để tối ưu hóa hiệu suất.

6-1. Các cân nhắc về hiệu suất

1. Chi phí ORDER BY

ROW_NUMBER() luôn được sử dụng với ORDER BY. Vì nó yêu cầu sắp xếp, thời gian xử lý có thể tăng đáng kể đối với các tập dữ liệu lớn.

Giảm thiểu:

• Sử dụng chỉ mục: Thêm chỉ mục vào các cột được sử dụng trong ORDER BY để tăng tốc sắp xếp.
• Sử dụng LIMIT: Chỉ lấy số lượng hàng bạn thực sự cần để giảm lượng dữ liệu được xử lý.

Ví dụ:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Tăng sử dụng bộ nhớ và I/O đĩa

Các hàm cửa sổ được xử lý bằng cách sử dụng bảng tạm và bộ nhớ. Khi khối lượng dữ liệu tăng, tiêu thụ bộ nhớ và I/O đĩa có thể tăng.

Giảm thiểu:

• Chia nhỏ truy vấn: Phân chia xử lý thành các truy vấn nhỏ hơn và trích xuất dữ liệu từng bước để giảm tải.
• Sử dụng bảng tạm: Lưu dữ liệu trích xuất vào bảng tạm và chạy tổng hợp từ đó để phân phối khối lượng công việc.

6-2. Mẹo điều chỉnh truy vấn

1. Kiểm tra kế hoạch thực thi

Trong MySQL, bạn có thể sử dụng EXPLAIN để kiểm tra kế hoạch thực thi truy vấn. Điều này giúp bạn xác minh xem các chỉ mục có được sử dụng đúng cách không.

Ví dụ:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Kết quả ví dụ:

Nếu bạn thấy Using index, điều đó cho thấy chỉ mục đang được sử dụng một cách thích hợp.

2. Tối ưu hóa chỉ mục

Hãy chắc chắn thêm chỉ mục vào các cột được sử dụng trong ORDER BY và WHERE. Chú ý đặc biệt đến những điều sau.

• Chỉ mục cột đơn: Tốt cho các điều kiện sắp xếp đơn giản
• Chỉ mục tổng hợp: Hiệu quả khi nhiều cột liên quan đến các điều kiện

Ví dụ:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Sử dụng xử lý theo lô

Thay vì xử lý một tập dữ liệu lớn cùng một lúc, bạn có thể giảm tải bằng cách xử lý dữ liệu theo lô.

Ví dụ:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Duy trì tính nhất quán dữ liệu

1. Cập nhật và tính toán lại

Khi các hàng được chèn hoặc xóa, đánh số có thể thay đổi. Xây dựng cơ chế để tính toán lại số khi cần.

Ví dụ:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Sử dụng một view giúp bạn giữ cho xếp hạng cập nhật dựa trên dữ liệu mới nhất.

6-4. Ví dụ truy vấn thực hành tốt nhất

Dưới đây là ví dụ về thực hành tốt nhất xem xét hiệu suất và khả năng bảo trì.

Ví dụ: Trích xuất Top N hàng

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Cấu trúc này sử dụng biểu thức bảng chung (CTE) để cải thiện khả năng đọc và tái sử dụng.

Tóm tắt

Khi sử dụng ROW_NUMBER() hoặc các lựa chọn thay thế của nó, hãy lưu ý những điểm sau:

1. Cải thiện tốc độ thông qua tối ưu hóa chỉ mục.
2. Xác định nút thắt bằng cách kiểm tra kế hoạch thực thi.
3. Lập kế hoạch cho cập nhật dữ liệu và duy trì tính nhất quán.
4. Sử dụng xử lý hàng loạt và CTEs để phân phối tải.

Việc áp dụng các thực hành tốt nhất này sẽ cho phép xử lý hiệu quả cho phân tích và báo cáo dữ liệu quy mô lớn.

7. Kết luận

Trong bài viết này, chúng tôi tập trung vào hàm ROW_NUMBER() của MySQL, giải thích mọi thứ từ cách sử dụng cơ bản và các ví dụ thực tế đến các lựa chọn thay thế cho các phiên bản cũ hơn, cộng với các lưu ý và thực hành tốt nhất. Trong phần này, chúng tôi sẽ tóm tắt các điểm chính và tổng hợp các bài học thực tế.

7-1. Tại sao ROW_NUMBER() hữu ích

Hàm ROW_NUMBER() đặc biệt tiện lợi cho phân tích dữ liệu và báo cáo theo các cách sau:

1. Số thứ tự liên tiếp trong nhóm: Dễ dàng tạo bảng xếp hạng doanh số theo bộ phận hoặc xếp hạng dựa trên danh mục.
2. Trích xuất Top N hàng: Lọc và trích xuất dữ liệu hiệu quả dựa trên các điều kiện cụ thể.
3. Phát hiện và xóa trùng lặp: Hữu ích cho việc dọn dẹp và tổ chức dữ liệu.

Vì nó đơn giản hóa các truy vấn phức tạp, nó cải thiện đáng kể khả năng đọc và bảo trì SQL.

7-2. So sánh với các hàm cửa sổ khác

So với các hàm cửa sổ như RANK() và DENSE_RANK(), ROW_NUMBER() khác biệt ở chỗ nó gán số duy nhất ngay cả cho các giá trị giống hệt nhau.

Chọn hàm phù hợp:
Việc chọn hàm tốt nhất cho mục đích của bạn cho phép xử lý dữ liệu hiệu quả.

7-3. Xử lý các phiên bản MySQL cũ hơn

Đối với môi trường dưới MySQL 8.0, chúng tôi cũng giới thiệu các cách tiếp cận sử dụng biến do người dùng định nghĩa. Tuy nhiên, bạn nên xem xét các lưu ý sau:

• Giảm khả năng đọc do SQL phức tạp hơn
• Tối ưu hóa truy vấn có thể khó khăn hơn trong một số trường hợp
• Có thể cần xử lý bổ sung để duy trì tính nhất quán dữ liệu

Nếu có thể, hãy mạnh mẽ xem xét việc di chuyển lên MySQL 8.0 hoặc cao hơn và sử dụng các hàm cửa sổ.

7-4. Các điểm chính cho tối ưu hóa hiệu suất

1. Sử dụng chỉ mục: Thêm chỉ mục vào các cột được sử dụng trong ORDER BY để cải thiện tốc độ.
2. Kiểm tra kế hoạch thực thi: Xác thực hiệu suất trước với EXPLAIN.
3. Áp dụng xử lý hàng loạt: Xử lý các tập dữ liệu lớn theo các phần nhỏ hơn để phân phối tải.
4. Sử dụng view và CTEs: Cải thiện khả năng tái sử dụng và đơn giản hóa các truy vấn phức tạp.

Bằng cách áp dụng các kỹ thuật này, bạn có thể đạt được xử lý dữ liệu hiệu quả và ổn định.

7-5. Ghi chú cuối cùng

ROW_NUMBER() là một công cụ mạnh mẽ có thể cải thiện đáng kể hiệu quả của phân tích dữ liệu.
Trong bài viết này, chúng tôi đã bao quát mọi thứ từ cú pháp cơ bản và các ví dụ thực tế đến các lưu ý và lựa chọn thay thế.

Chúng tôi khuyến khích bạn tự chạy các truy vấn trong khi theo dõi bài viết này. Cải thiện kỹ năng SQL của bạn sẽ giúp bạn xử lý phân tích dữ liệu và báo cáo phức tạp hơn với sự tự tin.

Phụ lục: Tài liệu tham khảo

• Tài liệu chính thức: MySQL Window Functions
• Môi trường SQL trực tuyến: SQL Fiddle (một công cụ cho phép bạn chạy và kiểm tra SQL trực tuyến)