MySQL CUME_DIST() 函数使用指南

MySQL CUME_DIST() 函数返回当前行的累积分布，即从第一行到与当前行值相同的最后一行的行数在分区内的总行数中的占比。

MySQL CUME_DIST() 函数常用于显示一个记录集中最高或者最低百分比数量的记录。比如，全国收入的前 10% 的人、此次考试最后 5% 的学生等。

CUME_DIST() 语法

这里是 MySQL CUME_DIST() 函数的语法：

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

参数

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

返回值

MySQL CUME_DIST() 函数返回一个大于 0 并且小于等于 1 的值，它是当前行的累积分布。 它的计算公式为：

(当前行之前的行数 + 与当前行值相同的行数) / 分区内的总行数

CUME_DIST() 示例

演示数据准备

使用下面的 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)

使用 MySQL CUME_DIST() 计算每个成绩的累积分布

下面的语句，使用 MySQL CUME_DIST() 函数按科目分组计算每个学生的成绩在每组中的累积分布：

SELECT *,
  CUME_DIST() OVER (
    PARTITION BY subject
    ORDER BY grade
  ) "cume_dist"
FROM student_grade;

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

注意，上面 SQL 语句中的窗口函数：

CUME_DIST() OVER (
  PARTITION BY subject
  ORDER BY grade
)

在 OVER 子句中，

  • PARTITION BY subject 将所有行按照科目进行分区
  • ORDER BY grade 将每个分区内的行按照成绩升序排列
  • CUME_DIST() 计算每行的累积分布。

注意，在每个科目内，成绩相同的行具有相同的累积分布。