MySQL 函数：PERCENT_RANK() 百分比排名详解
大家好，今天我们要深入探讨一个在数据分析和报告中非常有用的 MySQL 函数：PERCENT_RANK()。它能够计算行在分组中的百分比排名，帮助我们更好地理解数据分布和个体在群体中的相对位置。
1. 什么是百分比排名？
百分比排名（Percent Rank）是一个介于 0 和 1 之间的数值，表示在一个数据集或分组中，有多少百分比的值小于或等于当前值。 简单来说，它衡量了一个值在排序数据集中的相对位置。
例如，如果一个学生的考试成绩的百分比排名是 0.85，这意味着该学生的成绩高于或等于 85% 的其他学生的成绩。
2. PERCENT_RANK() 函数的语法
PERCENT_RANK() 函数的语法如下：
PERCENT_RANK() OVER (
[PARTITION BY column1, column2, ...]
ORDER BY column3 [ASC | DESC]
)让我们分解一下这个语法：
PERCENT_RANK(): 这是函数本身。OVER(): 这是一个窗口函数子句，用于定义函数计算的范围和顺序。PARTITION BY column1, column2, ...: 可选子句，用于将结果集划分为多个分区（分组）。PERCENT_RANK()函数将分别应用于每个分区。 如果省略此子句，则函数将应用于整个结果集。ORDER BY column3 [ASC | DESC]: 必选子句，用于指定在每个分区内对行进行排序的依据。column3是要排序的列。ASC表示升序（默认），DESC表示降序。
3. PERCENT_RANK() 函数的工作原理
PERCENT_RANK() 函数的计算公式如下：
(rank - 1) / (rows_in_partition - 1)其中：
rank是当前行在分区中的排名。排名从 1 开始。rows_in_partition是分区中的总行数。
需要注意的是：
- 如果分区中只有一行，则
PERCENT_RANK()返回 0。 - 如果
ORDER BY子句中指定的列的值相等，则这些行将获得相同的排名。
4. PERCENT_RANK() 函数的示例
为了更好地理解 PERCENT_RANK() 函数，我们来看几个示例。
示例 1：计算所有学生的成绩百分比排名
假设我们有一个名为 students 的表，包含以下数据：
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(255),
score INT
);
INSERT INTO students (id, name, score) VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 78),
(4, 'David', 92),
(5, 'Eve', 88);我们想计算每个学生的成绩的百分比排名。可以使用以下查询：
SELECT
id,
name,
score,
PERCENT_RANK() OVER (ORDER BY score ASC) AS percent_rank
FROM
students;该查询的结果如下：
| id | name | score | percent_rank |
|---|---|---|---|
| 3 | Charlie | 78 | 0.0000 |
| 1 | Alice | 85 | 0.2500 |
| 5 | Eve | 88 | 0.5000 |
| 2 | Bob | 92 | 0.7500 |
| 4 | David | 92 | 0.7500 |
从结果中可以看出：
- Charlie 的成绩是 78，百分比排名是 0.0000，这意味着他的成绩低于所有其他学生。
- Alice 的成绩是 85，百分比排名是 0.2500，这意味着她的成绩高于或等于 25% 的其他学生的成绩。
- Bob 和 David 的成绩都是 92，百分比排名都是 0.7500，这意味着他们的成绩高于或等于 75% 的其他学生的成绩。
示例 2：按班级计算学生的成绩百分比排名
假设我们在 students 表中添加一个 class_id 列，表示学生所属的班级：
ALTER TABLE students ADD COLUMN class_id INT;
UPDATE students SET class_id = 1 WHERE id IN (1, 2, 3);
UPDATE students SET class_id = 2 WHERE id IN (4, 5);现在，students 表包含以下数据：
| id | name | score | class_id |
|---|---|---|---|
| 1 | Alice | 85 | 1 |
| 2 | Bob | 92 | 1 |
| 3 | Charlie | 78 | 1 |
| 4 | David | 92 | 2 |
| 5 | Eve | 88 | 2 |
我们想按班级计算每个学生的成绩的百分比排名。可以使用以下查询：
SELECT
id,
name,
score,
class_id,
PERCENT_RANK() OVER (PARTITION BY class_id ORDER BY score ASC) AS percent_rank
FROM
students;该查询的结果如下：
| id | name | score | class_id | percent_rank |
|---|---|---|---|---|
| 3 | Charlie | 78 | 1 | 0.0000 |
| 1 | Alice | 85 | 1 | 0.5000 |
| 2 | Bob | 92 | 1 | 1.0000 |
| 5 | Eve | 88 | 2 | 0.0000 |
| 4 | David | 92 | 2 | 1.0000 |
从结果中可以看出：
- 在 1 班中，Charlie 的成绩百分比排名是 0.0000，Alice 的成绩百分比排名是 0.5000，Bob 的成绩百分比排名是 1.0000。
- 在 2 班中，Eve 的成绩百分比排名是 0.0000，David 的成绩百分比排名是 1.0000。
示例 3：处理 NULL 值
如果数据中包含 NULL 值，PERCENT_RANK() 函数会如何处理？ 默认情况下，NULL 值被认为是最小的值。
让我们在 students 表中添加一个包含 NULL 值的行：
INSERT INTO students (id, name, score, class_id) VALUES
(6, 'Frank', NULL, 1);现在，students 表包含以下数据：
| id | name | score | class_id |
|---|---|---|---|
| 1 | Alice | 85 | 1 |
| 2 | Bob | 92 | 1 |
| 3 | Charlie | 78 | 1 |
| 4 | David | 92 | 2 |
| 5 | Eve | 88 | 2 |
| 6 | Frank | NULL | 1 |
如果我们运行与示例 2 相同的查询：
SELECT
id,
name,
score,
class_id,
PERCENT_RANK() OVER (PARTITION BY class_id ORDER BY score ASC) AS percent_rank
FROM
students;该查询的结果如下：
| id | name | score | class_id | percent_rank |
|---|---|---|---|---|
| 6 | Frank | NULL | 1 | 0.0000 |
| 3 | Charlie | 78 | 1 | 0.2500 |
| 1 | Alice | 85 | 1 | 0.5000 |
| 2 | Bob | 92 | 1 | 0.7500 |
| 5 | Eve | 88 | 2 | 0.0000 |
| 4 | David | 92 | 2 | 1.0000 |
可以看到，Frank 的成绩是 NULL，百分比排名是 0.0000，因为 NULL 值被认为是最小的值。
如果你想将 NULL 值视为最大的值，可以使用 NULLS LAST 选项：
SELECT
id,
name,
score,
class_id,
PERCENT_RANK() OVER (PARTITION BY class_id ORDER BY score ASC NULLS LAST) AS percent_rank
FROM
students;该查询的结果如下：
| id | name | score | class_id | percent_rank |
|---|---|---|---|---|
| 3 | Charlie | 78 | 1 | 0.0000 |
| 1 | Alice | 85 | 1 | 0.3333 |
| 2 | Bob | 92 | 1 | 0.6667 |
| 6 | Frank | NULL | 1 | 1.0000 |
| 5 | Eve | 88 | 2 | 0.0000 |
| 4 | David | 92 | 2 | 1.0000 |
现在，Frank 的成绩百分比排名是 1.0000，因为 NULL 值被认为是最大的值。
5. PERCENT_RANK() 与 RANK() 和 DENSE_RANK() 的区别
PERCENT_RANK() 与其他排名函数（如 RANK() 和 DENSE_RANK()）密切相关，但它们之间存在一些关键区别。
RANK(): 返回当前行在其分区中的排名。 如果有多行具有相同的值，则它们将获得相同的排名，并且下一个排名将被跳过。 例如，如果两行排名为 2，则下一行的排名将为 4。DENSE_RANK(): 与RANK()类似，但它不会跳过排名。 如果有多行具有相同的值，则它们将获得相同的排名，并且下一行的排名将是连续的。 例如，如果两行排名为 2，则下一行的排名将为 3。PERCENT_RANK(): 返回当前行在其分区中的百分比排名。 结果是一个介于 0 和 1 之间的值。
下面是一个示例，说明了这些函数之间的区别：
CREATE TABLE sales (
id INT PRIMARY KEY,
region VARCHAR(255),
amount DECIMAL(10, 2)
);
INSERT INTO sales (id, region, amount) VALUES
(1, 'North', 100.00),
(2, 'North', 150.00),
(3, 'North', 150.00),
(4, 'North', 200.00),
(5, 'South', 120.00),
(6, 'South', 180.00),
(7, 'South', 220.00),
(8, 'South', 220.00);
SELECT
id,
region,
amount,
RANK() OVER (PARTITION BY region ORDER BY amount ASC) AS rank,
DENSE_RANK() OVER (PARTITION BY region ORDER BY amount ASC) AS dense_rank,
PERCENT_RANK() OVER (PARTITION BY region ORDER BY amount ASC) AS percent_rank
FROM
sales;该查询的结果如下：
| id | region | amount | rank | dense_rank | percent_rank |
|---|---|---|---|---|---|
| 1 | North | 100.00 | 1 | 1 | 0.0000 |
| 2 | North | 150.00 | 2 | 2 | 0.3333 |
| 3 | North | 150.00 | 2 | 2 | 0.3333 |
| 4 | North | 200.00 | 4 | 3 | 1.0000 |
| 5 | South | 120.00 | 1 | 1 | 0.0000 |
| 6 | South | 180.00 | 2 | 2 | 0.3333 |
| 7 | South | 220.00 | 3 | 3 | 0.6667 |
| 8 | South | 220.00 | 3 | 3 | 0.6667 |
从结果中可以看出：
- 在 North 地区，有两个销售额为 150.00 的行。
RANK()函数将它们都排名为 2，并将下一行的排名跳过到 4。DENSE_RANK()函数将它们都排名为 2，并将下一行的排名设置为 3。PERCENT_RANK()函数将它们都排名为 0.3333。 - 在 South 地区，有两个销售额为 220.00 的行。
RANK()函数将它们都排名为 3。DENSE_RANK()函数将它们都排名为 3。PERCENT_RANK()函数将它们都排名为 0.6667。
6. PERCENT_RANK() 函数的应用场景
PERCENT_RANK() 函数在许多数据分析和报告场景中都非常有用。 一些常见的应用场景包括：
- 学生成绩分析: 确定学生在班级或年级中的相对位置。
- 销售业绩分析: 确定销售人员在团队中的相对表现。
- 产品受欢迎程度分析: 确定产品在市场中的相对受欢迎程度。
- 网站流量分析: 确定网页在网站中的相对流量。
- 风险评估: 确定个体或事件在风险等级中的相对位置。
7. 性能考虑
在使用 PERCENT_RANK() 函数时，需要考虑性能问题。 由于 PERCENT_RANK() 是一个窗口函数，它可能需要对大量数据进行排序和分组，这可能会影响查询的性能。
为了提高性能，可以考虑以下几点：
- 索引: 确保在
ORDER BY和PARTITION BY子句中使用的列上创建索引。 - 数据量: 尽量减少需要计算百分比排名的数据量。
- 硬件: 使用高性能的硬件，例如更快的 CPU 和更多的内存。
8. 其他窗口函数
除了 PERCENT_RANK() 之外，MySQL 还提供了许多其他有用的窗口函数，例如：
RANK(): 返回当前行在其分区中的排名。DENSE_RANK(): 返回当前行在其分区中的密集排名。ROW_NUMBER(): 返回当前行在其分区中的行号。NTILE(n): 将分区中的行划分为 n 个桶，并为每个桶分配一个编号。LAG(column, n, default): 返回当前行之前 n 行的指定列的值。LEAD(column, n, default): 返回当前行之后 n 行的指定列的值。FIRST_VALUE(column): 返回分区中第一行的指定列的值。LAST_VALUE(column): 返回分区中最后一行的指定列的值。SUM() OVER()，AVG() OVER()，MIN() OVER()，MAX() OVER()，COUNT() OVER()：窗口聚合函数。
这些窗口函数可以与 PERCENT_RANK() 结合使用，以执行更复杂的数据分析和报告任务。
9. PERCENT_RANK() 的局限性
尽管 PERCENT_RANK() 是一个强大的工具，但它也有一些局限性：
- 需要排序:
PERCENT_RANK()需要对数据进行排序，这可能需要大量的时间和资源，尤其是在处理大型数据集时。 - 对重复值的敏感性: 当数据中存在重复值时，
PERCENT_RANK()可能会产生不直观的结果。 具有相同值的行将获得相同的百分比排名，这可能会掩盖一些重要的信息。 - 无法处理动态分组:
PERCENT_RANK()只能用于静态分组。 如果需要根据用户输入或其他动态条件进行分组，则可能需要使用其他方法。
10. 总结一下
PERCENT_RANK() 函数是一个强大的工具，用于计算行在分组中的百分比排名。它可以帮助我们更好地理解数据分布和个体在群体中的相对位置。 掌握PERCENT_RANK()的语法、工作原理、应用场景和局限性，可以更有效地利用它进行数据分析和报告。同时也要注意性能和重复值的影响。