MySQL8.0窗口函数之排名函数（rank、dense_rank）的使用

转载

窗口函数简介

• MySQL从8.0开始支持开窗函数，这个功能在大多商业数据库中早已支持，也叫分析函数。
• 开窗函数与分组聚合比较像，分组聚合是通过制定字段将数据分成多份，每一份执行聚合函数，每份数据返回一条结果。
• 开窗函数也是通过指定字段将数据分成多份，也就是多个窗口，对每个窗口的每一行执行函数，每个窗口返回等行数的结果。
• 窗口函数分为静态窗口和滑动窗口，静态窗口的大小是固定的，滑动窗口的大小可以根据设置进行变化，在当前窗口下生成子窗口。

语法简介

语法：函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)

函数解读：函数分为两个部分，第一部分是函数名称，开窗函数的数量较少，只有11个窗口函数+聚合函数（所有聚合函数都可以用作开窗函数），根据函数性质，有的要写参数，有的不需要写参数；

第二部分是over语句，over()是必须要写的，里面有三个参数，都是非必须参数，根据需求选写：

1. 第一个参数是 partition by +分组字段，将数据根据此字段分成多份，如果不加partition by参数，那会把整个数据当做一个窗口。
2. 第二个参数是 order by +排序字段，每个窗口的数据要不要进行排序。
3. 第三个参数 rows/range between 起始位置 and 结束位置，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口。其中起始位置和结束位置可写：

current row 边界是当前行，
unbounded preceding 边界是分区中的第一行，
unbounded following 边界是分区中的最后一行，
expr preceding 边界是当前行减去expr的值，
expr following 边界是当前行加上expr的值。
rows是基于行数，range是基于值的大小。

静态窗口函数之排名函数 rank()、dense_rank()

有成绩表sc,字段分别是学生编号s_id，课程编号c_id，成绩score

1、题目描述：按成绩从高到低进行排名

select * ,rank() over(order by score desc) 名次 from sc;

代码解读：rank()是排名函数，不需要参数；over语句里面没有partition by参数，也就是整个数据视为一个窗口；因为rank函数没有参数，但需要指定按照那个字段进行排名，所以使用rank函数必须用order by参数，排序字段就是排名字段，针对分数做降序，就会按照成绩从高到低进行排名；rank函数属于静态窗口，第三个参数写不写没有任何用处。

结果：发现80分的同学都是并列第六，而并列后的下一位同学的名次是11名

但有时候，当出现名次并列时，下一个人的名次是连续的，只需要将rank()函数换成dense_rank(),其他都不需要更改。

select * ,dense_rank() over(order by score desc) 名次 from sc;

2、题目：求每门课程的成绩排名（由高到低）

select * ,rank() over(partition by c_id order by score desc) 名次 from sc;

代码解读：因为要求的是每门课程的排名，也就是课程1和课程2之间相互不影响，需要按照c_id将成绩表分成多份，每个课程是一个窗口，窗口内进行排序并返回排名，最后将多个窗口的结果再拼接再一起。

3、题目：查询每位学生的成绩总分并排名

select s_id,sum(score) 总成绩,rank() over(order by sum(score) desc) 排名 from sc group by s_id;

代码解读：开窗函数的执行顺序是在group by之后的，所以是先针对s_id分组后聚合，得出每个学生的总成绩，之后再执行窗口函数。over语句没有partition by语句，没有进行分窗， 针对总成绩进行排序，根据每个学生的总成绩进行排名。