MySQL中窗口函数之Rank Over|极客笔记

MySQL中窗口函数之Rank Over

1. 什么是窗口函数

在MySQL中，窗口函数是一种特殊的函数，它可以在查询结果的某个窗口范围内计算和聚合数据。窗口函数在查询结果中的每一行都可以使用，而不仅仅是单行聚合函数的结果。

窗口函数通常用于计算排名、累计求和、移动平均值等需要考虑窗口范围的计算和分析。

2. Rank Over函数的用法

Rank Over函数是一种常见的窗口函数，用于计算排名。它可以按照指定的排序规则，对查询结果中的每一行进行排名，并返回排名的结果。

2.1 语法

RANK() OVER (PARTITION BY column1, column2,... ORDER BY column1, column2,...)

2.2 参数说明

RANK：表示计算排名的函数。
PARTITION BY：可选参数，用于按照指定的列进行分组。
ORDER BY：用于指定排序规则的列，决定排名的依据。

3. 使用示例

3.1 示例数据准备

在开始示例之前，我们先准备一份示例数据，以便进行后续的演示。

3.1.1 创建示例表

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  score INT
);

3.1.2 插入示例数据

INSERT INTO students (id, name, score) VALUES (1, 'Tom', 80);
INSERT INTO students (id, name, score) VALUES (2, 'John', 90);
INSERT INTO students (id, name, score) VALUES (3, 'Amy', 70);
INSERT INTO students (id, name, score) VALUES (4, 'Emma', 85);
INSERT INTO students (id, name, score) VALUES (5, 'Mike', 75);

3.2 示例一：计算学生成绩排名

下面的示例中，我们将使用Rank Over函数来计算学生成绩的排名。

3.2.1 查询示例数据

SELECT * FROM students;

3.2.2 运行结果

id	name	score
1	Tom	80
2	John	90
3	Amy	70
4	Emma	85
5	Mike	75

3.2.3 计算学生成绩的排名

SELECT 
  id, 
  name, 
  score, 
  RANK() OVER (ORDER BY score DESC) AS ranking 
FROM students;

3.2.4 运行结果

id	name	score	ranking
2	John	90	1
4	Emma	85	2
1	Tom	80	3
5	Mike	75	4
3	Amy	70	5

3.3 示例二：按班级计算学生成绩排名

下面的示例中，我们将使用Rank Over函数按班级计算学生成绩的排名。

3.3.1 添加班级字段

ALTER TABLE students ADD class VARCHAR(255);

3.3.2 更新示例数据

UPDATE students SET class = 'A' WHERE id IN (1, 2);
UPDATE students SET class = 'B' WHERE id IN (3, 4, 5);

3.3.3 计算每个班级学生成绩的排名

SELECT 
  id, 
  name, 
  score, 
  class, 
  RANK() OVER (PARTITION BY class ORDER BY score DESC) AS ranking 
FROM students;

3.3.4 运行结果

id	name	score	class	ranking
2	John	90	A	1
1	Tom	80	A	2
4	Emma	85	B	1
5	Mike	75	B	2
3	Amy	70	B	3

3.4 示例三：计算累计学生数

下面的示例中，我们将使用Rank Over函数来计算每个学生的排名（按分数从高到低），并计算累计学生数。

3.4.1 计算每个学生的排名和累计学生数

SELECT 
  id, 
  name, 
  score, 
  RANK() OVER (ORDER BY score DESC) AS ranking,
  COUNT(*) OVER (ORDER BY score DESC) AS cumulative_count
FROM students;

3.4.2 运行结果

id	name	score	ranking	cumulative_count
2	John	90	1	5
4	Emma	85	2	4
1	Tom	80	3	3
5	Mike	75	4	2
3	Amy	70	5	1

3.5 示例四：计算学生相对排名

下面的示例中，我们将使用Rank Over函数来计算每个学生的相对排名。相对排名是指相同分数的学生拥有相同的排名，而不是按照分数的大小来排名。

3.5.1 计算每个学生的相对排名

SELECT 
  id, 
  name, 
  score, 
  RANK() OVER (ORDER BY score DESC) AS ranking,
  DENSE_RANK() OVER (ORDER BY score DESC) AS relative_rank
FROM students;