PostgreSQL cume_dist() 函数

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

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

cume_dist() 语法

这里是 PostgerSQL cume_dist() 函数的语法：

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

参数

partition_column_list

参与分区的列的列表。

order_column_list

参与排序的列的列表。

反回值

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

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

cume_dist() 示例

演示数据准备

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

CREATE TABLE student_grade (
  id SERIAL 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)

使用 PostgreSQL cume_dist() 计算每个成绩的累积分布

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

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

 id | name | class | subject | grade |      cume_dist
----+------+-------+---------+-------+---------------------
 10 | Lucy | B     | English |     6 | 0.16666666666666666
 11 | Jody | B     | English |     7 |  0.6666666666666666
  6 | Jim  | A     | English |     7 |  0.6666666666666666
  4 | Tim  | A     | 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
  7 | Lucy | B     | Math    |     8 |  0.6666666666666666
  3 | Jim  | A     | Math    |     8 |  0.6666666666666666
  9 | Susy | B     | Math    |     9 |                   1
  1 | Tim  | A     | Math    |     9 |                   1
(8 rows)

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

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

在 OVER 子句中，

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

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