详解MySQL中的PERCENT_RANK函数

在 MySQL 中，PERCENT_RANK 函数用于计算一个值在其分组中的百分等级。

它的返回值范围是从 0 到 1，表示一个值在排序后的数据集中相对于其他值的位置。百分等级的计算公式为：
$$PERCENT\_RANK=\frac{\text{rank}-1}{\text{total\_rows}-1}$$

其中，rank 是当前行的排序位置，total_rows 是总行数。

1. 引入

下面通过一个具体例子来说明 PERCENT_RANK 的用法。

假设我们有一个包含学生分数的表 students_scores，表结构如下：

CREATE TABLE students_scores (student_id INT,student_name VARCHAR(50),score DECIMAL(5, 2)
);

我们向表中插入一些数据：

INSERT INTO students_scores (student_id, student_name, score) VALUES
(1, 'Alice', 85.0),
(2, 'Bob', 90.5),
(3, 'Charlie', 78.0),
(4, 'David', 92.0),
(5, 'Eve', 88.0);

现在，我们希望计算每个学生分数的百分等级。可以使用以下 SQL 查询：

SELECTstudent_id,student_name,score,PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank
FROMstudents_scores;

执行上述查询后，将得到以下结果：

student_id	student_name	score	percent_rank
4	David	92.0	0.0000
2	Bob	90.5	0.2500
5	Eve	88.0	0.5000
1	Alice	85.0	0.7500
3	Charlie	78.0	1.0000

在这个结果集中，percent_rank 列表示每个学生的分数在所有学生中的相对位置。例如，David 的分数是最高的，因此他的 percent_rank 是 0。Charlie 的分数是最低的，因此他的 percent_rank 是 1。其他学生的 percent_rank 介于 0 和 1 之间，反映了他们的分数在整个分数分布中的相对位置。

通过这个例子，我们可以看到 PERCENT_RANK 函数如何计算并返回数据集中的每个值的百分等级。

1. 基本使用

假设我们有一个表 employees，包含员工的销售数据：

CREATE TABLE employees (employee_id INT,employee_name VARCHAR(50),sales DECIMAL(10, 2)
);INSERT INTO employees (employee_id, employee_name, sales) VALUES
(1, 'John', 1500.00),
(2, 'Jane', 2000.00),
(3, 'Alice', 2500.00),
(4, 'Bob', 3000.00),
(5, 'Eve', 1000.00);

我们希望计算每个员工销售额的百分等级。可以使用以下查询：

SELECTemployee_id,employee_name,sales,PERCENT_RANK() OVER (ORDER BY sales DESC) AS percent_rank
FROMemployees;

查询结果如下：

employee_id	employee_name	sales	percent_rank
4	Bob	3000.00	0.0000
3	Alice	2500.00	0.2500
2	Jane	2000.00	0.5000
1	John	1500.00	0.7500
5	Eve	1000.00	1.0000

2：分组使用

假设我们有一个包含员工销售数据的表 department_sales，每个员工属于不同的部门：

CREATE TABLE department_sales (employee_id INT,employee_name VARCHAR(50),department VARCHAR(50),sales DECIMAL(10, 2)
);INSERT INTO department_sales (employee_id, employee_name, department, sales) VALUES
(1, 'John', 'Electronics', 1500.00),
(2, 'Jane', 'Electronics', 2000.00),
(3, 'Alice', 'Furniture', 2500.00),
(4, 'Bob', 'Furniture', 3000.00),
(5, 'Eve', 'Electronics', 1000.00),
(6, 'Charlie', 'Furniture', 2800.00);

我们希望计算每个部门中员工销售额的百分等级。可以使用以下查询：

SELECTemployee_id,employee_name,department,sales,PERCENT_RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS percent_rank
FROMdepartment_sales;

查询结果如下：

employee_id	employee_name	department	sales	percent_rank
2	Jane	Electronics	2000.00	0.0000
1	John	Electronics	1500.00	0.5000
5	Eve	Electronics	1000.00	1.0000
4	Bob	Furniture	3000.00	0.0000
6	Charlie	Furniture	2800.00	0.5000
3	Alice	Furniture	2500.00	1.0000

3：处理重复值

假设我们有一个包含学生成绩的表 student_grades，其中有些成绩是重复的：

CREATE TABLE student_grades (student_id INT,student_name VARCHAR(50),grade DECIMAL(5, 2)
);INSERT INTO student_grades (student_id, student_name, grade) VALUES
(1, 'Tom', 85.00),
(2, 'Jerry', 90.00),
(3, 'Anna', 85.00),
(4, 'Mike', 95.00),
(5, 'Sue', 90.00);

我们希望计算每个学生成绩的百分等级。可以使用以下查询：

SELECTstudent_id,student_name,grade,PERCENT_RANK() OVER (ORDER BY grade DESC) AS percent_rank
FROMstudent_grades;

查询结果如下：

student_id	student_name	grade	percent_rank
4	Mike	95.00	0.0000
2	Jerry	90.00	0.2500
5	Sue	90.00	0.2500
1	Tom	85.00	0.7500
3	Anna	85.00	0.7500

通过以上例子可以看到，PERCENT_RANK 函数在处理不同数据集和需求时都非常灵活和有用。它可以帮助我们更好地理解和分析数据中的分布和排名情况。

4. 使用优势

如果不使用 PERCENT_RANK 函数，我们可以通过子查询和一些数学计算来手动计算百分等级。这种方法相对繁琐，需要多次嵌套查询和排序。下面是一个手动计算百分等级的例子，使用与之前例子相同的 students_scores 表。

4.1 手动计算百分等级

假设我们有以下表数据：

CREATE TABLE students_scores (student_id INT,student_name VARCHAR(50),score DECIMAL(5, 2)
);INSERT INTO students_scores (student_id, student_name, score) VALUES
(1, 'Alice', 85.0),
(2, 'Bob', 90.5),
(3, 'Charlie', 78.0),
(4, 'David', 92.0),
(5, 'Eve', 88.0);

手动计算每个学生分数的百分等级可以通过以下查询实现：

SELECTstudent_id,student_name,score,(SELECT COUNT(*) FROM students_scores AS sub WHERE sub.score < main.score) / (SELECT COUNT(*) - 1 FROM students_scores) AS percent_rank
FROMstudents_scores AS main
ORDER BYscore DESC;

上述查询的结果与使用 PERCENT_RANK 函数的结果是相同的。

4.2 使用 PERCENT_RANK 的优势

1. 简洁性和易读性：使用 PERCENT_RANK 函数可以简化查询的编写，使得代码更为简洁和易读。手动计算百分等级需要嵌套查询和计算，增加了复杂性。

2. 性能优化：数据库引擎通常会对窗口函数进行优化，使其执行效率更高。手动计算可能无法充分利用这些优化，从而导致查询性能较低。

3. 维护性：使用内置函数减少了自定义计算逻辑，当需求发生变化时，代码的维护和修改也更加方便。

4. 减少错误：手动计算时容易出错，例如在计算总行数、排序以及分组等过程中，使用 PERCENT_RANK 函数可以减少这些人为错误。

4.3 使用 PERCENT_RANK

我们再来回顾一下如何使用 PERCENT_RANK 函数：

SELECTstudent_id,student_name,score,PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank
FROMstudents_scores;

这个查询简单明了，直接利用 PERCENT_RANK 函数计算百分等级，避免了复杂的嵌套查询和计算逻辑。

5. 总结

使用 PERCENT_RANK 函数在简化查询编写、提高性能和减少错误方面具有明显的优势。因此，在可以使用窗口函数的场景下，推荐优先使用 PERCENT_RANK 而不是手动计算百分等级。