MySQL高级函数 PERCENT_RANK()：百分比排名应用详解
大家好，今天我们要深入探讨 MySQL 的一个高级窗口函数：PERCENT_RANK()。这个函数在数据分析和报表生成中非常有用，它可以帮助我们计算某个值在一个有序数据集中的相对排名，也就是百分比排名。理解并掌握 PERCENT_RANK() 可以让我们更有效地分析数据分布，找出数据中的异常值，并进行更精细化的用户分群。
1. PERCENT_RANK() 函数的基本概念
PERCENT_RANK() 是一个窗口函数，这意味着它会作用于一个数据集的窗口（window）上，而不是整个表。窗口是由 OVER() 子句定义的。PERCENT_RANK() 函数计算的是当前行在其窗口中的相对排名，结果是一个介于 0 和 1 之间的值。
语法:
PERCENT_RANK() OVER (
[PARTITION BY column1, column2, ...]
ORDER BY column3 [ASC | DESC]
)PARTITION BY(可选): 将数据集分成多个分区，PERCENT_RANK()会在每个分区内独立计算。如果没有PARTITION BY，则整个数据集被视为一个分区。ORDER BY(必须): 指定在每个分区内对数据进行排序的列。PERCENT_RANK()的计算基于这个排序顺序。- 返回值: 一个介于 0 和 1 之间的
DOUBLE类型的值。
计算公式:
PERCENT_RANK() = (rank - 1) / (rows - 1)
其中:
rank是当前行在窗口中的排名 (从 1 开始)。rows是窗口中的总行数。
重要说明:
- 如果窗口中只有一行数据，
PERCENT_RANK()的结果将始终为 0。 PERCENT_RANK()函数会考虑排序顺序中的重复值。具有相同排序值的行会被赋予相同的排名。
2. 创建测试数据
为了更好地理解 PERCENT_RANK() 的用法，我们先创建一个示例表 employees 并插入一些数据。
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
department VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (department, salary) VALUES
('Sales', 60000.00),
('Sales', 75000.00),
('Sales', 80000.00),
('Marketing', 65000.00),
('Marketing', 70000.00),
('Marketing', 85000.00),
('IT', 90000.00),
('IT', 95000.00),
('IT', 100000.00),
('HR', 55000.00),
('HR', 60000.00),
('HR', 65000.00);
-- 插入一些重复值，便于演示
INSERT INTO employees (department, salary) VALUES
('Sales', 75000.00),
('IT', 90000.00);3. 基本应用：计算整体薪资的百分比排名
现在，我们来计算所有员工的薪资百分比排名。
SELECT
id,
department,
salary,
PERCENT_RANK() OVER (ORDER BY salary ASC) AS salary_percent_rank
FROM
employees;这条 SQL 语句会对 employees 表中的所有员工按照薪资升序排序，并计算每个员工的薪资百分比排名。OVER (ORDER BY salary ASC) 子句定义了整个表作为一个窗口，并指定了排序的依据。
查询结果 (部分):
| id | department | salary | salary_percent_rank |
|---|---|---|---|
| 10 | HR | 55000.00 | 0.0000 |
| 1 | Sales | 60000.00 | 0.0714 |
| 11 | HR | 60000.00 | 0.0714 |
| 4 | Marketing | 65000.00 | 0.2143 |
| 12 | HR | 65000.00 | 0.2143 |
| 5 | Marketing | 70000.00 | 0.2857 |
| 2 | Sales | 75000.00 | 0.3571 |
| 13 | Sales | 75000.00 | 0.3571 |
| … | … | … | … |
解释:
- 薪资为 55000 的员工的
salary_percent_rank为 0，表示他是薪资最低的员工。 - 薪资为 100000 的员工的
salary_percent_rank接近 1，表示他是薪资最高的员工。 - 具有相同薪资的员工，
PERCENT_RANK()会赋予相同的百分比排名。
4. 分区应用：计算每个部门内薪资的百分比排名
如果我们想知道每个员工在其部门内的薪资百分比排名，可以使用 PARTITION BY 子句。
SELECT
id,
department,
salary,
PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary ASC) AS department_salary_percent_rank
FROM
employees;这条 SQL 语句会将 employees 表按照 department 列进行分区，然后在每个分区内按照 salary 升序排序，并计算每个员工在其部门内的薪资百分比排名。
查询结果 (部分):
| id | department | salary | department_salary_percent_rank |
|---|---|---|---|
| 10 | HR | 55000.00 | 0.0000 |
| 1 | Sales | 60000.00 | 0.0000 |
| 11 | HR | 60000.00 | 0.5000 |
| 4 | Marketing | 65000.00 | 0.0000 |
| 12 | HR | 65000.00 | 1.0000 |
| 5 | Marketing | 70000.00 | 0.5000 |
| 2 | Sales | 75000.00 | 0.3333 |
| … | … | … | … |
解释:
- 在 HR 部门，薪资为 55000 的员工的
department_salary_percent_rank为 0，表示他是 HR 部门薪资最低的员工。 - 在 Sales 部门，薪资为 60000 的员工的
department_salary_percent_rank为 0，表示他是 Sales 部门薪资最低的员工。 - 即使两个员工的薪资相同 (例如，两个 Sales 员工的薪资都是 75000)，他们在各自部门内的百分比排名也会不同，因为他们所在部门的薪资分布不同。
5. 实际应用场景
PERCENT_RANK() 函数在实际应用中有很多用途，下面列举几个常见的场景：
- 用户分群: 根据用户的消费金额、活跃度等指标，计算用户的百分比排名，将用户分成不同的等级 (例如，Top 10%、Top 20%、…、Bottom 10%)。
- 风险评估: 评估贷款申请人的信用风险，根据申请人的各项指标 (例如，收入、负债、信用评分)，计算申请人的风险百分比排名，从而决定是否批准贷款。
- 性能监控: 监控服务器的 CPU 使用率、内存使用率等指标，计算指标的百分比排名，及时发现性能瓶颈。
- 异常检测: 检测异常交易、异常访问等行为，根据交易金额、访问频率等指标，计算指标的百分比排名，找出异常值。
- 报表生成: 生成各种报表，例如，销售额排名、利润排名、客户满意度排名等，帮助管理层了解业务状况。
6. 结合其他函数使用
PERCENT_RANK() 函数可以与其他函数结合使用，实现更复杂的数据分析功能。
示例 1: 找出每个部门薪资排名前 20% 的员工
SELECT
id,
department,
salary
FROM
(
SELECT
id,
department,
salary,
PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS department_salary_percent_rank
FROM
employees
) AS subquery
WHERE
department_salary_percent_rank <= 0.2;这条 SQL 语句首先使用子查询计算每个员工在其部门内的薪资百分比排名 (降序排列，因此 department_salary_percent_rank 越小，薪资越高)。然后，在外层查询中筛选出 department_salary_percent_rank 小于等于 0.2 的员工，即薪资排名前 20% 的员工。
示例 2: 计算每个部门的平均薪资和中位数薪资
SELECT
department,
AVG(salary) AS average_salary,
(
SELECT
salary
FROM
(
SELECT
department,
salary,
PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary ASC) AS department_salary_percent_rank
FROM
employees
WHERE e.department = employees.department
) AS subquery
WHERE
department_salary_percent_rank >= 0.5
ORDER BY
salary ASC
LIMIT 1
) AS median_salary
FROM
employees e
GROUP BY
department;这里计算中位数薪资稍微复杂一些，因为MySQL没有直接计算中位数的函数，这里使用PERCENT_RANK()来实现。该SQL语句使用子查询计算每个员工在其部门内的薪资百分比排名。然后，筛选出百分比排名大于等于 0.5 的员工(即中位数及以上的员工)。最后，取这些员工中薪资最低的那个，即为中位数薪资。
7. 注意事项
PERCENT_RANK()函数的性能可能受到数据量和排序复杂度的影响。对于大型数据集，可以考虑使用索引来提高查询效率。PERCENT_RANK()函数只能用于窗口函数中，不能单独使用。- 在计算百分比排名时，需要仔细考虑排序顺序。升序和降序会影响结果的解释。
PERCENT_RANK()与RANK()、DENSE_RANK()、ROW_NUMBER()等其他窗口函数不同，PERCENT_RANK()的结果是一个介于 0 和 1 之间的值，表示相对排名。而其他窗口函数的结果是整数，表示绝对排名。- 处理NULL值：在计算排名时，NULL值的处理方式取决于具体的数据库系统和设置。默认情况下，MySQL会将NULL值视为最小值。如果需要改变这种行为，可以使用
NULLS FIRST或NULLS LAST选项（如果数据库系统支持）。但是，MySQL 8.0及以上版本不支持NULLS FIRST和NULLS LAST，需要使用其他方式处理NULL值，例如使用CASE语句对NULL值进行转换。
8. 与其他排名函数的对比
PERCENT_RANK() 经常与其他排名函数一起使用，例如 RANK(), DENSE_RANK() 和 ROW_NUMBER()。理解它们的区别对于选择正确的排名函数至关重要。
| 函数 | 说明 | 是否允许并列排名 | 间隙 |
|---|---|---|---|
RANK() |
返回当前行在其分区中的排名。如果存在并列排名，则后续排名会跳过。 | 是 | 有 |
DENSE_RANK() |
返回当前行在其分区中的排名。如果存在并列排名，则后续排名不会跳过。 | 是 | 无 |
ROW_NUMBER() |
返回当前行在其分区中的唯一排名。即使存在并列排名，也会为每一行分配一个唯一的数字。 | 否 | 无 |
PERCENT_RANK() |
返回当前行在其分区中的相对排名，表示为百分比。 | 是 | 无 |
举例说明，假设我们有以下数据：
| id | value |
|---|---|
| 1 | 10 |
| 2 | 10 |
| 3 | 20 |
| 4 | 30 |
使用不同的排名函数得到的结果如下：
SELECT
id,
value,
RANK() OVER (ORDER BY value) AS rank_val,
DENSE_RANK() OVER (ORDER BY value) AS dense_rank_val,
ROW_NUMBER() OVER (ORDER BY value) AS row_number_val,
PERCENT_RANK() OVER (ORDER BY value) AS percent_rank_val
FROM
(
SELECT 1 AS id, 10 AS value UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 30
) AS data;| id | value | rank_val | dense_rank_val | row_number_val | percent_rank_val |
|---|---|---|---|---|---|
| 1 | 10 | 1 | 1 | 1 | 0.0000 |
| 2 | 10 | 1 | 1 | 2 | 0.0000 |
| 3 | 20 | 3 | 2 | 3 | 0.6667 |
| 4 | 30 | 4 | 3 | 4 | 1.0000 |
可以看出：
RANK()给 value 10 并列排名为1，下一个排名跳过了2，直接是3。DENSE_RANK()给 value 10 并列排名为1，下一个排名是2，没有跳过。ROW_NUMBER()简单粗暴，不管是否并列，依次排名。PERCENT_RANK()给 value 10 百分比排名为0，value 20 百分比排名为 0.6667，value 30 百分比排名为1.
9. 总结
PERCENT_RANK() 函数是一个强大的窗口函数，可以帮助我们计算数据集中每个值的相对排名。通过结合 PARTITION BY 子句和其他函数，我们可以实现更复杂的数据分析功能。掌握 PERCENT_RANK() 函数可以让我们更有效地分析数据分布，找出数据中的异常值，并进行更精细化的用户分群。记住，选择合适的排名函数取决于你的具体需求和数据特性。理解不同排名函数的差异，才能更好地利用它们来解决实际问题。