Làm cách nào để tính thứ hạng trong MySQL?

Hàm RANK() phân bổ một thứ hạng (số nguyên) cho mỗi hàng trong một nhóm của tập dữ liệu, với các khoảng trống và các giá trị thứ hạng có thể trùng lặp

Điều này trái ngược với hàm DENSE_RANK() và ROW_NUMBER()

Xin lưu ý rằng RANK() đã có sẵn kể từ phiên bản MySQL 8. 0. Nếu bạn gặp lỗi cú pháp khi chạy hàm RANK() trên trang này, hãy kiểm tra phiên bản MySQL của bạn (SELECT VERSION()), rất có thể bạn đang chạy phiên bản MySQL cũ hơn

Cú pháp cơ bản

SELECT column1,
	RANK() OVER (PARTITION BY.. ORDER BY...) as rank
FROM table1;

Chức năng RANK() của MySQL có thể được xem là "xếp hạng trong một nhóm". Vì vậy, cách tốt nhất để hiểu hàm RANK() của MySQL là hỏi bạn ba câu hỏi

1. Xếp hạng ai?
2. Trong nhóm nào?
3. Xếp hạng bằng gì?

Thực hành số 1

Giả sử bạn dạy học sinh trong hai lớp. Sau kỳ thi cuối kỳ, bạn cần xếp hạng học sinh trong mỗi lớp theo điểm của họ trong kỳ thi. Vì vậy, câu trả lời cho 3 câu hỏi là

1. Xếp hạng học sinh (SELECT StudentName)
2. Trong mỗi lớp (PHẦN PHẦN THEO Tên lớp)
3. Xếp hạng theo điểm (ĐẶT HÀNG THEO StudentMark DESC)

Hãy tạo bảng exam_result để chỉ cho bạn cách xếp hạng học sinh trong tình huống này

-- Create table
CREATE TABLE exam_result
(
   StudentName varchar(20) NOT NULL,
   ClassName varchar(20) NOT NULL,
   StudentMark tinyint NOT NULL
);

Chèn dữ liệu vào bảng exam_result

-- Add sample data
insert into exam_result values('Anne','Class A',92),
('Gary','Class A',85),
('Tom','Class A',85),
('John','Class A',76),
('Jerry','Class B',95),
('Nick','Class B',91),
('Joe','Class B',88),
('Ted','Class B',88),
('Bob','Class B',82);

Đây là truy vấn RANK() để xếp hạng học sinh theo kết quả thi trong mỗi lớp

SELECT StudentName,
	ClassName,
	StudentMark,
	RANK() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;

Xin lưu ý rằng khi một học sinh có cùng điểm với một học sinh khác, cả hai đều nhận được cùng một giá trị thứ hạng, nhưng không giống như hàm DENSE_RANK(), hàm RANK() KHÔNG gán thứ hạng liên tiếp

Trong Lớp A, cả Gary và Tom đều được gán cùng một giá trị xếp hạng 2 vì họ có cùng điểm, nhưng John nhận được giá trị xếp hạng 4 trong đó 3 bị bỏ qua

9 hồ sơ trả về

Thực hành số 2

Truy vấn dưới đây không sử dụng mệnh đề PARTITION BY coi tất cả các lớp là một nhóm theo nghĩa đen, do đó, nó xếp hạng tất cả học sinh dựa trên điểm số của họ bất kể họ thuộc lớp nào

SELECT StudentName,
	ClassName,
	StudentMark,
	RANK() OVER (ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;

9 hồ sơ trả về

Thực hành số 3

Truy vấn bên dưới xếp hạng các sản phẩm theo số lượng đơn vị trong kho trong mỗi danh mục sản phẩm

select p.ProductName,
	c.CategoryName,
    p.UnitsInStock,
    RANK() OVER (PARTITION BY c.CategoryName ORDER BY p.UnitsInStock) as stock_rank
from products as p 
join categories as c on p.CategoryID=c.CategoryID
order by c.CategoryName, p.UnitsInStock, stock_rank;

77 hồ sơ trả về

Thực hành số 4

Truy vấn này xếp hạng khách hàng theo tổng số tiền bán hàng trong mỗi ngày đặt hàng. Lưu ý rằng mệnh đề ORDER BY trong ngoặc của RANK() OVER là một cột được tính toán bằng cách sử dụng hàm SUM và GROUP BY để tổng hợp

Hàm RANK trong MySQL

Hàm RANK của MySQL được sử dụng để trả về các số liên tiếp bắt đầu từ 1 dựa trên thứ tự của các hàng được áp đặt bởi mệnh đề ORDER BY. Khi bạn có hai bản ghi có cùng dữ liệu, thì nó sẽ xếp hạng giống nhau cho cả hai hàng

Sau đây là cú pháp sử dụng hàm RANK trong MySQL. Như bạn có thể thấy, giống như hàm Row_Number, ở đây cả Phân vùng theo và Sắp xếp theo Mệnh đề đều là tùy chọn

Mệnh đề PARTITION BY được sử dụng để phân vùng tập kết quả thành nhiều nhóm. Vì nó là tùy chọn, nếu bạn không chỉ định mệnh đề PARTITION BY thì hàm RANK sẽ coi toàn bộ tập kết quả là một phân vùng duy nhất. Mệnh đề ORDER BY cũng là tùy chọn và mệnh đề này được sử dụng để xác định trình tự trong đó mỗi hàng sẽ chỉ định RANK i. e. số

Ghi chú. Hàm RANK trong MySQL trả về một số duy nhất tăng dần cho mỗi hàng bắt đầu từ 1 và giống nhau cho mỗi phân vùng. Khi có trùng lặp hoặc liên kết, thứ hạng giống nhau sẽ được chỉ định cho tất cả các hàng trùng lặp hoặc liên kết với hàng, nhưng hàng tiếp theo sau các hàng trùng lặp hoặc liên kết sẽ có thứ hạng mà nó sẽ được chỉ định nếu không có trùng lặp. Vì vậy, chức năng RANK bỏ qua xếp hạng nếu có trùng lặp hoặc ràng buộc

Ví dụ để hiểu hàm Rank trong MySQL

Chúng tôi sẽ sử dụng bảng Nhân viên sau đây để hiểu chức năng RANK của MySQL

Vui lòng sử dụng Tập lệnh SQL bên dưới để tạo cơ sở dữ liệu và bảng Nhân viên cũng như điền vào bảng Nhân viên với dữ liệu mẫu

CREATE DATABASE EmployeeDB;
USE EmployeeDB;

CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(50), 
    Department VARCHAR(10),
    Salary INT
);

Insert Into Employees Values (1, 'James', 'IT', 80000);
Insert Into Employees Values (2, 'Taylor', 'IT', 80000);
Insert Into Employees Values (3, 'Pamela', 'HR', 50000);
Insert Into Employees Values (4, 'Sara', 'HR', 40000);
Insert Into Employees Values (5, 'David', 'IT', 35000);
Insert Into Employees Values (6, 'Smith', 'HR', 65000);
Insert Into Employees Values (7, 'Ben', 'HR', 65000);
Insert Into Employees Values (8, 'Stokes', 'IT', 45000);
Insert Into Employees Values (9, 'David', 'IT', 35000);
Insert Into Employees Values (10, 'Smith', 'HR', 65000);
Insert Into Employees Values (11, 'John', 'IT', 68000);

Hàm RANK của MySQL không có Mệnh đề THAM GIA

Trước tiên chúng ta hãy xem một ví dụ về chức năng RANK của MySQL mà không sử dụng Mệnh đề THAM GIA. Khi chúng ta không chỉ định Mệnh đề THAM GIA, thì hàm RANK sẽ coi toàn bộ tập kết quả là một phân vùng duy nhất và đánh số liên tiếp bắt đầu từ 1 ngoại trừ khi có sự ràng buộc

Sau đây là một ví dụ về chức năng RANK của MySQL mà không sử dụng mệnh đề PARTITION BY. Ở đây chúng tôi sử dụng Thứ tự theo Khoản trên cột Tên. Vì vậy, nó sẽ đưa ra thứ hạng dựa trên cột Tên

SELECT EmployeeId, Department, Salary, EmployeeName,
RANK() OVER (ORDER BY EmployeeName ASC ) AS 'Rank' 
FROM Employees;

Khi bạn thực hiện truy vấn SQL ở trên, bạn sẽ nhận được đầu ra sau. Như bạn có thể thấy trong kết quả bên dưới, sẽ không có phân vùng và do đó tất cả các hàng được gán với các số nguyên liên tiếp bắt đầu từ 1 ngoại trừ khi có sự ràng buộc i. e. đối với tên Nhân viên David và Smith, nó mang lại cùng thứ hạng cho cả hai hàng

Chức năng Xếp hạng trong MySQL bỏ qua (các) xếp hạng khi có sự ràng buộc. Như bạn có thể thấy trong kết quả ở trên, Hạng 3 và 9 bị bỏ qua vì có 2 hàng ở hạng 2 cũng như 2 hàng ở hạng 8. Hàng thứ tư có thứ hạng 4 và hàng thứ 10 có thứ hạng 10

Chức năng RANK của MySQL với mệnh đề PARTITION BY

Bây giờ, chúng ta hãy xem một ví dụ về hàm RANK của MySQL sử dụng mệnh đề PARTITION BY. Khi chúng ta chỉ định mệnh đề PARTITION BY, thì tập kết quả được phân vùng dựa trên cột mà chúng ta chỉ định trong mệnh đề Partition BY

Vui lòng xem mã ví dụ sau. Như bạn có thể thấy, chúng tôi đã chỉ định Bộ phận trong mệnh đề Phân vùng và Mức lương trong mệnh đề Theo thứ tự. Như trong bảng Nhân viên của chúng tôi, chúng tôi có hai bộ phận (CNTT và Nhân sự). Vì vậy, Phân vùng theo mệnh đề sẽ chia tập kết quả thành hai phân vùng. Một phân vùng dành cho nhân viên bộ phận IT và phân vùng còn lại dành cho nhân viên phòng nhân sự. Sau đó, trên mỗi phân vùng, dữ liệu được sắp xếp dựa trên cột Lương. Hàm MySQL RANK sau đó đưa ra một số thứ tự số nguyên bắt đầu từ 1 cho mỗi bản ghi trong mỗi phân vùng trừ khi có sự ràng buộc. Trong trường hợp hòa, nó cho cùng một thứ hạng và sau đó bỏ qua thứ hạng

Bây giờ, khi bạn thực thi đoạn mã trên, bạn sẽ nhận được đầu ra sau

Ví dụ thời gian thực về chức năng RANK trong MySQL

Nếu bạn đang tham dự bất kỳ cuộc phỏng vấn nào, thì một câu hỏi nổi tiếng sẽ được hỏi trong hầu hết các cuộc phỏng vấn tôi. e. tìm mức lương cao thứ n. Các chức năng RANK có thể được sử dụng để tìm mức lương cao thứ n. Hãy để chúng tôi hiểu điều này với một ví dụ

Giả sử có 2 nhân viên có mức lương ĐẦU TIÊN cao nhất thì có thể xảy ra 2 kịch bản kinh doanh như sau

1. Nếu yêu cầu kinh doanh của bạn không tạo ra bất kỳ kết quả nào cho mức lương cao NHẤT THỨ HAI thì bạn phải sử dụng hàm RANK của MySQL
2. Nếu yêu cầu kinh doanh của bạn là trả về mức lương tiếp theo sau các hàng được liên kết là Mức lương cao THỨ HAI, thì bạn phải sử dụng hàm DENSE_RANK của MySQL và chúng ta sẽ thảo luận về hàm DENSE_RANK trong bài viết tiếp theo của chúng tôi

Trong bảng Nhân viên của chúng ta, chúng ta có 2 nhân viên có mức lương cao NHẤT ĐẦU TIÊN (80000), hàm Rank() sẽ không trả về bất kỳ dữ liệu nào cho mức lương cao NHẤT THỨ HAI. Vui lòng thực thi Tập lệnh SQL bên dưới và xem đầu ra

WITH EmployeeCTE  AS
(
    SELECT Salary,
    RANK() OVER (ORDER BY Salary DESC) AS Rank_Salry
    FROM Employees
)
SELECT Salary FROM EmployeeCTE WHERE Rank_Salry = 2 LIMIT 1;

Ví dụ sau sẽ trả về mức lương cao thứ ba

-- Create table
CREATE TABLE exam_result
(
   StudentName varchar(20) NOT NULL,
   ClassName varchar(20) NOT NULL,
   StudentMark tinyint NOT NULL
);

đầu ra. 68000

Trong bài viết tiếp theo, tôi sẽ thảo luận về hàm DENSE_RANK trong MySQL với các ví dụ. Ở đây, trong bài viết này, tôi cố gắng giải thích nhu cầu và cách sử dụng Hàm RANK trong MySQL bằng các ví dụ. Tôi hy vọng bạn thích bài viết Hàm Xếp hạng MySQL này

Làm cách nào để sử dụng thứ hạng () trong MySQL?

Xếp hạng được tính như thế nào trong SQL?

Làm cách nào để sử dụng thứ hạng trên phân vùng trong MySQL?

Cú pháp cho thứ hạng là gì?