1.应用场景
窗口函数解决的问题包括: 1)排名问题 2)top N问题 应用工作中, 面试中. |
前言MySQL版本8已支持窗口函数这个功能, 如果低于该版本, 会出现SQL报错!一.窗口函数有什么用? 在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求: 排名问题:每个部门按业绩来排名面对这类需求,就需要使用sql的高级功能窗口函数了。 二.什么是窗口函数? 窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。 窗口函数的基本语法如下:
那么语法中的<窗口函数>都有哪些呢? <窗口函数>的位置,可以分为以下两种函数: 1) 专用窗口函数,包括后面要讲到的 rank, dense_rank, row_number 等专用窗口函数。因为窗口函数是对 where 或者 group by 子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。 三.如何使用? 接下来,就结合实例,给大家介绍几种窗口函数的用法。 1.专用窗口函数rank 例如下图,是班级表中的内容 文章图片 如果我们想在每个班级内按成绩排名,得到下面的结果。 文章图片 以班级“1”为例,这个班级的成绩“95”排在第1位,这个班级的“83”排在第4位。 上面这个结果确实按我们的要求在每个班级内,按成绩排名了。 得到上面结果的sql语句代码如下: select *,我们来解释下这个sql语句里的select子句。rank是排序的函数。 要求是“每个班级内按成绩排名”,这句话可以分为两部分: 1)每个班级内:按班级分组通过下图,我们就可以理解partiition by(分组)和order by(在组内排序)的作用了。 文章图片 窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。 那么,为什么还要用窗口函数呢? 这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。 而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。 文章图片 相信通过这个例子,你已经明白了这个窗口函数的使用: 现在我们说回来,为什么叫“窗口”函数呢? 这是因为partition by分组后的结果称为“窗口”,这里的窗口不是我们家里的门窗,而是表示 "范围" 的意思。 简单来说,窗口函数有以下功能: 1)同时具有分组和排序的功能2.其他专业窗口函数 专用窗口函数rank, dense_rank, row_number有什么区别呢? select *,得到结果: 文章图片 从上面的结果可以看出: rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。这三个函数的区别如下: 文章图片 最后,需要强调的一点是: 在上述的这三个专用窗口函数中,函数后面的括号不需要任何参数,保持()空着就可以。Note 写过一些代码的我们, 都知道函数, 后面都是有个括号的.3.聚合函数作为窗口函数 聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。 我们来看一下窗口函数是聚合函数时,会出来什么结果: select *,得到结果: 文章图片 有发现什么吗?我单独用sum举个例子: 文章图片 如上图,聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。 比如0004号,在使用sum窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。 不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算,现在再结合刚才得到的结果(下图),是不是理解起来容易多了? 文章图片 比如0005号后面的聚合窗口函数结果是:学号0001~0005五人成绩的总和、平均、计数及最大最小值。 如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可。 这样使用窗口函数有什么用呢? 聚合函数作为窗口函数,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响。四. 注意事项 partition子句可是省略,省略就是不指定分组,结果如下,只是按成绩由高到低进行了排序: 得到结果: 文章图片 但是,这就失去了窗口函数的功能,所以一般不要这么使用。 五. 总结 1.窗口函数语法 <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)<窗口函数>的位置,可以放以下两种函数: 1) 专用窗口函数,比如 rank, dense_rank, row_number 等2.窗口函数有以下功能: 1)同时具有分组(partition by)和 排序(order by)的功能3.注意事项 窗口函数 原则上 只能写在select子句中4.窗口函数使用场景 1)业务需求“在每组内排名”,比如: 排名问题:每个部门按业绩来排名 下一次会跟大家分享一些窗口函数的面试题,从而让各位在面试、工作中都能遇到这类问题,就想到哦,这用窗口函数就可以解决。 后续补充 .... |
TBD |
https://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==&mid=2649247566&idx=1&sn=f9c7018c299498673b38221db2ecd5cd // 通俗易懂的学会:SQL窗口函数 https://leetcode-cn.com/problems/rank-scores/solution/tu-jie-sqlmian-shi-ti-jing-dian-pai-ming-wen-ti-by/// leetcode 178. 分数排名 |
...
文章图片
推荐阅读
- 数据库|mysql窗口函数中的滑动窗口
- mysql|mysql 窗口函数 求和_mysql窗口函数
- Mysql|mysql窗口函数-累计求和sum over
- 工具教程|MySQL8.0.26安装超详细教程〔超详细〕
- mySql数据库|mysql安装教程8.0.26
- database|Mysql 8.0.26安装,听我的6分钟让你安装成功!
- 安装|mysql 8.0.28版本安装配置方法图文教程
- 安装Mysql 8.0
- 数据库|Mysql-8.0.26-winx64下载和安装