文章目录
前言
本文介绍几个MySQL常用的窗口函数
下面以这个简单的数据表为例,对常见的几种窗口函数进行说明
先说一下窗口函数的一般语法:
select
窗口函数(参数) over(partition by <用于分组的列> order by <用于排序的列>) as '别名'
from 表名
常用的窗口函数分为排序函数:rank()、dense_rank()、row_number()
和聚合函数:sum()、avg()、count()、min()、max()
,其中排序函数的括号后面不需要输入参数(即列名),聚合函数需要输入参数。下面对这几个窗口函数进行举例说明
一、排序函数
1.Rank()
功能:求出每个员工在它所属部门中的工资排名
select *,
rank() over(partition by DepartmentId order by Salary desc) as '排名'
from Employee;
得到结果如下:
可以看到,rank()
的效果和group by
有点像,也是先将数据按照某列进行聚合(分组),再进行相应的计算。不同的是,group by 聚合后,每一类只返回一列,而rank()
则全部返回,即不减少数据的行数。
另一方面,我们可以看到这个排名其实是不连续的,能不能求连续的排名呢(即Randy应该排在第3,而不是第4,其实这个可以通过dense_rank()
来实现
2.dense_Rank()
功能:求出每个员工在它所属部门中的工资排名(连续)
select *,
dense_rank() over(partition by DepartmentId order by Salary desc) as '排名'
from Employee;
可以看到dense_rank()
得到的排名是连续的。而且我们发现前两种函数得到的排名都有可能出现重复值,就当工资相同的时候,排名相同,能否有一种方法,使得排名是不重复的,即便是工资相同的情况下也给它一个不同的排名。这可以通过row_num()
函数实现
3.row_number()
功能:求出每个员工在它所属部门中的工资排名(不重复)
select *,
row_number() over(partition by DepartmentId order by Salary desc) as '排名'
from Employee;
可以看到row_num()
对于相同的工资,得到的排名还是不一样的。
二、聚合函数
1.sum()
功能:同一部门的工资累计(好奇为什么不叫cumsum)
select *,
sum(Salary) over(partition by DepartmentId order by Id) as '求和'
from Employee;
sum()
函数实现了对工资的累加
2.avg()
功能:求工资的累计均值,可以看下哪个工资的加入使得平均值变化较大
select *,
avg(Salary) over(partition by DepartmentId order by Id) as '均值'
from Employee;
3.count()、min()、max()、
功能:累计计算、累计最小值、累计最大值
select *,
count(Salary) over(partition by DepartmentId order by Id) as '计数',
min(Salary) over(partition by DepartmentId order by Id) as '最小值',
max(Salary) over(partition by DepartmentId order by Id) as '最大值'
from Employee;
总结:
1.窗口函数的语法:
select
窗口函数(参数) over(partition by <用于分组的列> order by <用于排序的列> desc/asc) as '别名'
from 表名
窗口函数(参数)可以分为:
排序函数:rank()
、dense_rank()
、row_number()
聚合函数:sum(<用于计算的列名>)
、avg(<用于计算的列名>)
、count(<用于计算的列名>)
、min(<用于计算的列名>)
、max(<用于计算的列名>)
partition by <用于分组的列>
这部分可以省略,省略就代表不进行分组,即把全部样本当成一个组
2.窗口函数的特点:
-窗口函数具有分组(partition by)和排序(order by)的功能
-窗口函数相对于group by 而言,不会减少原表的行数