首页 MySQL PERCENT_RANK() 函数

MySQL PERCENT_RANK() 函数

MySQL PERCENT_RANK() 函数返回当前行所在的分区内的相对排名,也就是 (rank() - 1) / (分区总行数 - 1)

PERCENT_RANK() 语法

这里是 MySQL PERCENT_RANK() 函数的语法:

PERCENT_RANK()
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

partition_column_list
参与分区的列的列表。
order_column_list
参与排序的列的列表。

返回值

MySQL PERCENT_RANK() 函数返回一个介于 0 和 1 (包含) 之间的数,表示当前行所在的分区内的相对排名,其计算公式为是 (rank() - 1) / (分区总行数 - 1)

PERCENT_RANK() 示例

演示数据准备

使用下面的 CREATE TABLE 语句创建一个表 student_grade 以存储学生的班级和成绩:

CREATE TABLE student_grade (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  class CHAR(1) NOT NULL,
  subject VARCHAR(20) NOT NULL,
  grade INT NOT NULL
);

这里创建了一个 student_grade 表,它有 5 个列:

  • id - 行 ID,主键。
  • name - 学生的姓名。
  • class - 学生所在的班级。
  • subject - 科目的名称。
  • grade - 该科目的成绩。

使用下面的 INSERT 语句向 student_grade 表中插入一些行:

INSERT INTO student_grade
  (name, class, subject, grade)
VALUES
  ('Tim', 'A', 'Math', 9),
  ('Tom', 'A', 'Math', 7),
  ('Jim', 'A', 'Math', 8),
  ('Tim', 'A', 'English', 7),
  ('Tom', 'A', 'English', 8),
  ('Jim', 'A', 'English', 7),
  ('Lucy', 'B', 'Math', 8),
  ('Jody', 'B', 'Math', 6),
  ('Susy', 'B', 'Math', 9),
  ('Lucy', 'B', 'English', 6),
  ('Jody', 'B', 'English', 7),
  ('Susy', 'B', 'English', 8);

使用下面的 SELECT 语句检索表中的数据:

SELECT * FROM student_grade;
+----+------+-------+---------+-------+
| id | name | class | subject | grade |
+----+------+-------+---------+-------+
|  1 | Tim  | A     | Math    |     9 |
|  2 | Tom  | A     | Math    |     7 |
|  3 | Jim  | A     | Math    |     8 |
|  4 | Tim  | A     | English |     7 |
|  5 | Tom  | A     | English |     8 |
|  6 | Jim  | A     | English |     7 |
|  7 | Lucy | B     | Math    |     8 |
|  8 | Jody | B     | Math    |     6 |
|  9 | Susy | B     | Math    |     9 |
| 10 | Lucy | B     | English |     6 |
| 11 | Jody | B     | English |     7 |
| 12 | Susy | B     | English |     8 |
+----+------+-------+---------+-------+
12 rows in set (0.00 sec)

按照科目查看每个学生的成绩的相对排名

要查看在每个科目中每个学生按照成绩从高到低的相对排名,请使用如下语句:

SELECT *,
  rank() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "rank",
  PERCENT_RANK() OVER (
    PARTITION BY subject
    ORDER BY grade DESC
  ) "percent_rank"
FROM student_grade;
+----+------+-------+---------+-------+------+--------------+
| id | name | class | subject | grade | rank | PERCENT_RANK |
+----+------+-------+---------+-------+------+--------------+
|  5 | Tom  | A     | English |     8 |    1 |            0 |
| 12 | Susy | B     | English |     8 |    1 |            0 |
|  4 | Tim  | A     | English |     7 |    3 |          0.4 |
|  6 | Jim  | A     | English |     7 |    3 |          0.4 |
| 11 | Jody | B     | English |     7 |    3 |          0.4 |
| 10 | Lucy | B     | English |     6 |    6 |            1 |
|  1 | Tim  | A     | Math    |     9 |    1 |            0 |
|  9 | Susy | B     | Math    |     9 |    1 |            0 |
|  3 | Jim  | A     | Math    |     8 |    3 |          0.4 |
|  7 | Lucy | B     | Math    |     8 |    3 |          0.4 |
|  2 | Tom  | A     | Math    |     7 |    5 |          0.8 |
|  8 | Jody | B     | Math    |     6 |    6 |            1 |
+----+------+-------+---------+-------+------+--------------+
12 rows in set (0.00 sec)

注意,上面 SQL 语句中的窗口函数:

PERCENT_RANK() OVER (
  PARTITION BY subject
  ORDER BY grade DESC
)

OVER 子句中,

  • PARTITION BY subject 将按照学科进行分区
  • ORDER BY grade DESC 将每个分区内的行按照成绩逆序排列。
  • PERCENT_RANK() 返回每行在其关联的分区内的相对排名。

您从上面也可以看出 PERCENT_RANK()RANK() 的不同。

按照班级查看每个学生的总成绩的相对排名

要查看在每个班级中每个学生按照总成绩从高到底的相对排名,请使用下面的语句:

SELECT t.*,
  PERCENT_RANK() OVER (
    PARTITION BY class
    ORDER BY t.sum_grade DESC
  ) "percent_rank"
FROM (
    SELECT class,
      name,
      sum(grade) sum_grade
    FROM student_grade
    GROUP BY class, name
  ) t;
+-------+------+-----------+--------------+
| class | name | sum_grade | PERCENT_RANK |
+-------+------+-----------+--------------+
| A     | Tim  |        16 |            0 |
| A     | Tom  |        15 |          0.5 |
| A     | Jim  |        15 |          0.5 |
| B     | Susy |        17 |            0 |
| B     | Lucy |        14 |          0.5 |
| B     | Jody |        13 |            1 |
+-------+------+-----------+--------------+
6 rows in set (0.01 sec)

在上面的语句中,注意这个子查询:

SELECT class,
  name,
  sum(grade) sum_grade
FROM student_grade
GROUP BY class, name

这个子查询使用 GROUP BY 子句和 sum() 按照班级和学生汇总出每个学生的总成绩。

+-------+------+-----------+
| class | name | sum_grade |
+-------+------+-----------+
| A     | Tim  |        16 |
| A     | Tom  |        15 |
| A     | Jim  |        15 |
| B     | Lucy |        14 |
| B     | Jody |        13 |
| B     | Susy |        17 |
+-------+------+-----------+
6 rows in set (0.01 sec)       

主语句在从这个子查询中按照班级进行分区,然后在每个分区内按照总成绩逆序排序,并使用 PERCENT_RANK() 返回每行在其关联的分区内的相对排名。

特别声明:本站部分内容收集于互联网是出于更直观传递信息的目的。该内容版权归原作者所有,并不代表本站赞同其观点和对其真实性负责。如该内容涉及任何第三方合法权利,请及时与824310991@qq.com联系,我们会及时反馈并处理完毕。