百度360必应搜狗淘宝本站头条
当前位置:网站首页 > IT技术 > 正文

一篇文章搞定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 种类型。

  1. 排名函数:rank(获取排名), row_number(获取行号,相当于排名)。
  2. 聚合函数:sum(求和),avg(求均值),min(求最小值),max(求最大值)。
  3. 分布函数:cume_dist(求累积占比)。
  4. 前后函数:lag(向前取值),lead(向后取值)。
  5. 头尾函数: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字(可选)...

今年最常见的前端面试题,你会做几道?

在面试或招聘前端开发人员时,期望、现实和需求之间总是存在着巨大差距。面试其实是一个交流想法的地方,挑战人们的思考方式,并客观地分析给定的问题。可以通过面试了解人们如何做出决策,了解一个人对技术和解决问...