一篇文章搞定MySQL中的窗口函数(mysql常用的窗口函数)
wptr33 2025-07-28 00:38 30 浏览
我是孙斌,北理数学系毕业,分享数据分析相关知识,点击右上角“关注”,学习更多数据分析知识。
在MySQL中,分组group by一般和聚合函数连用,如group by+sum,这样能够得到每个组的总和,但是每个组内部的信息就被压缩掉了,例如,想知道每个组内的排名情况,以前这个问题不太好处理,有了窗口函数,这个问题就变得很简单了。
窗口函数有什么用
从 MySQL 8.0 开始,MySQL 加入了窗口函数,窗口函数有什么用呢?
主要用于解决分组排名问题,最典型的就是 TopN 问题,比如获取每个地区销售额前三名的地区。
有同学可能会有疑问,如果是分组,用 group by 不就行了?的确没错,可是现在要的是分组后每个组内的排名啊。
如果只用 group by,则只能得到每个分组的第一个值,无法展现出每个组内部的情况,或者只能得到每个组的总和或者均值等。
如何更高效地解决这种 TopN 问题呢?这就需要用到窗口函数了。
简单来说,就是对数据进行分组,每个分组内部其实还有很多记录,每个组相当于一个窗口,然后通过一些函数对每个窗口内的数据进行分析处理。
首先,看一个小例子。有以下销售数据表。
上表中,第一列是 id,第二列是销售人员姓名,第三列示订单日期,第四列是销售额,最后一列是地区。 现在我们希望将每个地区的销售人员按照销售额降序排列,并给出排名。 利用窗口函数可以这样写 SQL 代码。
select region, salesman, amount,
rank() over (partition by region order by amount desc) as rk
from orders;
运行结果:
运行结果中最后一列是每个销售人员的排名,按照销售额降序排名。
上面的代码,核心部分为:
rank() over (partition by 字段1 order by 字段2)
这就是窗口函数的基本格式,由于这里要获取每个组的排名,所以用到了 rank()函数,partition 表示分块、分组,根据哪个字段进行分组,order by 表示排序。
当然,这里的 rank()函数可以替换为其他函数,根据需要实现的功能来定。
常用的窗口函数
常用的窗口函数还有 row_number, sum/avg/min/max, cume_dist, lag, first_value, last_value, ntile 等,这么多窗口函数,大致分为以下 5 种类型。
- 排名函数:rank(获取排名), row_number(获取行号,相当于排名)。
- 聚合函数:sum(求和),avg(求均值),min(求最小值),max(求最大值)。
- 分布函数:cume_dist(求累积占比)。
- 前后函数:lag(向前取值),lead(向后取值)。
- 头尾函数:first_value(第一个),last_value(最后一个)。
除了这些函数,还有其他一些函数,如 ntile,能够将有序数据分为 n 个组。
下面,我们通过一些具体的案例来说明这些函数的用法。
1、排名函数
前面的例子已经演示了用 rank 函数获取分组排名,用 row_number 函数也能实现同样的效果,代码如下。
select region, salesman, amount,
row_number() over (partition by region order by amount desc) as rk
from orders;
运行结果:
这里本质上获取的是行号,如果两个数值相同,用 rank 函数获取的排名就会相同,而 row_number 则不会。
2、聚合函数
例如,我们希望分部门按照订单日期累加销售额,并将结果作为新的一列,这就要用到 sum 函数,SQL 代码如下。
select region, order_date, amount,
sum(amount) over (partition by region order by order_date rows between 2 preceding and current row) as accum
from orders;
运行结果:
最后一列就是累加销售额。
3、分布函数
例如,想分地区按照日期统计每天的销售额累积占比,这就要用到 cume_dist 函数,SQL 代码如下。
select region, order_date, amount,
cume_dist() over (partition by region order by order_date) as ration
from orders;
运行结果:
这里得到的就是销售额的累积占比。
4、前后函数
例如,想分地区按照销售额排序,查询前一名销售员和当前人员的销售额的差值。
解决这个问题可以分为两步。
第一步: 先找出每个销售人员的销售额的前一个数值,这就要用到 lag 函数取当前值的前一个值。
select region, salesman, order_date, amount,
lag(amount, 1) over (partition by region order by order_date) as amount_pre
from orders;
运行结果:
最后一列得到的就是当前值的前一个值。
第二步: 作差,即用当前人员的销售额减去对应的前一个数值。
select region, salesman, order_date, amount, amount_pre, amount - amount_pre as dif
from(
select region, salesman, order_date, amount,
lag(amount, 1) over (partition by region order by order_date) as amount_pre
from orders
) as t;
运行结果:
最后一列就是销售额的差值。
5、头尾函数
例如,我们希望先按照订单日期进行排序,然后分地区查询第 1 个销售人员的销售额。
这就要用到 first_value 函数去获取每组里的第一个值,SQL 代码如下。
select region, order_date, amount,
first_value(amount) over (partition by region order by order_date) as first_amount
from orders;
运行结果:
最后一列就是第 1 个销售人员的销售额。
除了上述用法之外,还有其他一些用法,例如,我们希望按照地区将销售额分为 3 个等级。
这就要用到ntile函数,这个函数能够将有序数据分为 n 个等级。
select region, salesman, order_date, amount,
ntile(3) over (partition by region order by amount) as rk
from orders;
运行结果:
这样就将每个地区的销售额分为 3 个等级。
以上就是 MySQL 中的窗口函数。
觉得对您有帮助,点赞+关注哦!
相关推荐
- oracle数据导入导出_oracle数据导入导出工具
-
关于oracle的数据导入导出,这个功能的使用场景,一般是换服务环境,把原先的oracle数据导入到另外一台oracle数据库,或者导出备份使用。只不过oracle的导入导出命令不好记忆,稍稍有点复杂...
- 继续学习Python中的while true/break语句
-
上次讲到if语句的用法,大家在微信公众号问了小编很多问题,那么小编在这几种解决一下,1.else和elif是子模块,不能单独使用2.一个if语句中可以包括很多个elif语句,但结尾只能有一个...
- python continue和break的区别_python中break语句和continue语句的区别
-
python中循环语句经常会使用continue和break,那么这2者的区别是?continue是跳出本次循环,进行下一次循环;break是跳出整个循环;例如:...
- 简单学Python——关键字6——break和continue
-
Python退出循环,有break语句和continue语句两种实现方式。break语句和continue语句的区别:break语句作用是终止循环。continue语句作用是跳出本轮循环,继续下一次循...
- 2-1,0基础学Python之 break退出循环、 continue继续循环 多重循
-
用for循环或者while循环时,如果要在循环体内直接退出循环,可以使用break语句。比如计算1至100的整数和,我们用while来实现:sum=0x=1whileTrue...
- Python 中 break 和 continue 傻傻分不清
-
大家好啊,我是大田。...
- python中的流程控制语句:continue、break 和 return使用方法
-
Python中,continue、break和return是控制流程的关键语句,用于在循环或函数中提前退出或跳过某些操作。它们的用途和区别如下:1.continue(跳过当前循环的剩余部分,进...
- L017:continue和break - 教程文案
-
continue和break在Python中,continue和break是用于控制循环(如for和while)执行流程的关键字,它们的作用如下:1.continue:跳过当前迭代,...
- 作为前端开发者,你都经历过怎样的面试?
-
已经裸辞1个月了,最近开始投简历找工作,遇到各种各样的面试,今天分享一下。其实在职的时候也做过面试官,面试官时,感觉自己问的问题很难区分候选人的能力,最好的办法就是看看候选人的github上的代码仓库...
- 面试被问 const 是否不可变?这样回答才显功底
-
作为前端开发者,我在学习ES6特性时,总被const的"善变"搞得一头雾水——为什么用const声明的数组还能push元素?为什么基本类型赋值就会报错?直到翻遍MDN文档、对着内存图反...
- 2023金九银十必看前端面试题!2w字精品!
-
导文2023金九银十必看前端面试题!金九银十黄金期来了想要跳槽的小伙伴快来看啊CSS1.请解释CSS的盒模型是什么,并描述其组成部分。...
- 前端面试总结_前端面试题整理
-
记得当时大二的时候,看到实验室的学长学姐忙于各种春招,有些收获了大厂offer,有些还在苦苦面试,其实那时候的心里还蛮忐忑的,不知道自己大三的时候会是什么样的一个水平,所以从19年的寒假放完,大二下学...
- 由浅入深,66条JavaScript面试知识点(七)
-
作者:JakeZhang转发链接:https://juejin.im/post/5ef8377f6fb9a07e693a6061目录...
- 2024前端面试真题之—VUE篇_前端面试题vue2020及答案
-
添加图片注释,不超过140字(可选)...
- 今年最常见的前端面试题,你会做几道?
-
在面试或招聘前端开发人员时,期望、现实和需求之间总是存在着巨大差距。面试其实是一个交流想法的地方,挑战人们的思考方式,并客观地分析给定的问题。可以通过面试了解人们如何做出决策,了解一个人对技术和解决问...
- 一周热门
- 最近发表
-
- oracle数据导入导出_oracle数据导入导出工具
- 继续学习Python中的while true/break语句
- python continue和break的区别_python中break语句和continue语句的区别
- 简单学Python——关键字6——break和continue
- 2-1,0基础学Python之 break退出循环、 continue继续循环 多重循
- Python 中 break 和 continue 傻傻分不清
- python中的流程控制语句:continue、break 和 return使用方法
- L017:continue和break - 教程文案
- 作为前端开发者,你都经历过怎样的面试?
- 面试被问 const 是否不可变?这样回答才显功底
- 标签列表
-
- git pull (33)
- git fetch (35)
- mysql insert (35)
- mysql distinct (37)
- concat_ws (36)
- java continue (36)
- jenkins官网 (37)
- mysql 子查询 (37)
- python元组 (33)
- mybatis 分页 (35)
- vba split (37)
- redis watch (34)
- python list sort (37)
- nvarchar2 (34)
- mysql not null (36)
- hmset (35)
- python telnet (35)
- python readlines() 方法 (36)
- munmap (35)
- docker network create (35)
- redis 集合 (37)
- python sftp (37)
- setpriority (34)
- c语言 switch (34)
- git commit (34)