MySQL窗口函数——分组排序函数：number_rank（），rank（），dense_rank（）

目录

​​🎲概念​​

​​🎯语法结构​​

​​🎬序号函数​​

​​🎨准备数据​​

​​🎸ROW_NUMBER() 直接排序​​

​​💄rank（）相同的并列排序​​

​​🍤dense_rannk（）连续并列的排序​​

​​👕经典面试题​​

​​👚取出排名前三的数据（Top N）​​

​​🏆扩展—全局操作不指定分组字段​​

​​☕️ 每文一语​​

---

🎲概念

MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点.
非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。

窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

当你实际操作之后，你会发现窗口函数真的是非常nice！

那么窗口函数到底有哪些呢？这些窗口函数的功能到底有多么的强大呢？让我们带着这样的疑问去学习吧！

另外还有开窗聚合函数: SUM,AVG,MIN,MAX

🎯语法结构

window_function ( expr ) OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
  frame_clause 
)

🎬序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。

这三种排序方式，我们需要根据自己的业务需求进行选择

row_number()|rank()|dense_rank() over ( 
  partition by ... 
  order by ... 
)

下面我们通过实际的案例操作来理解这三个方法，到底有什么异同

🎨准备数据

create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
);

insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

🎸ROW_NUMBER() 直接排序

select 
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn 
from employee;

row_number() ：将那些数值相同也按照顺序排序了，但是有时候我们需要将这些相同的放在一起排名，这个时候我们这个方法就显得不够好了。

over：里面第一个参数是，按照什么分组；第二个是按照什么排序，升序还是降序

💄rank（）相同的并列排序

select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn 
from employee;

 只需要将上述的函数名，改成rank即可，在这里可以很好的避免之前的情况发生，将一样的数值放在了一起，也就是并列排名，在现实中具有很多的推广。

那么有时候，我们虽然想并列排名，但是又不想中间空排名次序，也就是连续的排名，那么此时我们又该如何去做呢？

🍤dense_rannk（）连续并列的排序

-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
select 
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn 
from employee;

 在现实的场景当中，我们可以根据自己的场景去选择我们的排序函数，当然前提是你必须要基础它的语法：

number_rank（）|rank（）| dense_rank() over（partition by 按照什么字段分组 order by 按照什么字段排序 desc）as 重命名字段

👕经典面试题

👚取出排名前三的数据（Top N）

相信大家在面试数据库的时候，经常被HR问到，MySQL实现取出用户的前三个值，那么有些小伙伴直接脱口而出，先排序，然后limit 3 不就可以了吗？可能面试官会笑笑，你考虑过数值一样的情况了吗？

这个时候我们就可以使用窗口函数里面的dense_rank()函数，也不需要你去写各种存储函数去实现，直接一行代码实现top N

--求出每个部门薪资排在前三名的员工- 分组求TOPN
select 
* 
from 
(
    select 
     dname,
     ename,
     salary,
     dense_rank() over(partition by dname order by salary desc)  as rn
    from employee
)t
where t.rn <= 3

🏆扩展—全局操作不指定分组字段

也就是不指定partition by 后面的字段名，也就是可以直接省略

-- 对所有员工进行全局排序（不分组）
-- 不加partition by表示全局排序
select 
     dname,
     ename,
     salary,
     dense_rank() over( order by salary desc)  as rn
from employee;

☕️ 每文一语

希望你每天都有爱💓 💗 💕