mysql窗口函数是什么（mysql的窗口）



01 什么是窗口函数

一 窗口函数的作用

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名

topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

二 什么是窗口函数

窗口函数，也叫OLAP(Online Anallytical Processing,联机分析处理）,可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

语法中的窗口函数有：

1）专用窗口函数,如 rank, dense_rank, row_number 等。

2）聚合函数，如sum,avg,count,max,min等

因为窗口函数是对where 或者group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

02 如何使用窗口函数

1 专用窗口函数rank

班级表-每个班级内按成绩排名

这样操作后都是按班级分组，按成绩排序，并且多了一个ranking列。

窗口函数具备了我们之前学过的group by子句分组的功能和order by 子句排序的功能，那么，为什么还要用窗口函数呢，这是因为，group by分组后改变了表的行数，一行只有一个类别，二partition by 和rank函数不会减少表中的行数。例如下面统计每个班级的人数。

group by 分组汇总改变行数

partition by 分组汇总行数不变

现在说回来，为什么叫“窗口”函数呢？这是因为partition by 分组后的结果称为“窗口”，

这里的窗口不是我们家的门窗，而是表示“范围”的意思。

简单来说，窗口函数的功能：

1）同时具有分组和排序的功能

2）不减少原表的行数

3）语法是

03 其他专用窗口函数

专用窗口函数rank, dense_rank, row_number的区别

这三个函数的区别如下：

上述这三个专用窗口函数，函数后面的括号不需要任何参数，保持（）空着就可以。

04 案例：面试经典排名问题

1）班级表按成绩来排名，如果两个分数相同，那么排名要是并列的。正常排名是1,2,3,4，

但是现在前3名是并列的名次，排名结果是：1,1,1,2。

本题考点：考察如何使用窗口函数 2.专用窗口函数排名的区别：rank,dense_rank,

row_number。

2）涉及到排名的问题，都可以使用窗口函数来解决。记住rank,dense_rank,

row_number排名的区别。

编写一个sql查询来实现分数排名。如果两个分数相同，则两个分数（rank）相同。请注意，

平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有间隔。

05 案例：面试经典topN问题

工作中会经常遇到这样的业务问题：如何找到每个类别下用户最喜欢的产品是哪个？

如何找到每个类别下用户点击最多的5个商品。

这类问题其实就是常见的：分组取每组最大值，最小值，每组最大的N条（top N）记录。

1）分组取每组最大值（按课程号分组取成绩最大值所在行的数据）

用关联子查询实现

2）分组取每组最小值（按课程号分组取成绩最小值所在行的数据）

3）每组最大的N条记录（以下是成绩表，查找每个学生成绩最高的2个科目）

这里的as a 是不能省略的，要注意。 用子查询的原因是，如果不用子查询，where字句里的

ranking是不能识别的。

经典topN问题：每组最大的N条记录。这类问题涉及到既要分组，又要排序的情况，要能想到用窗口函数来实现。

这类题的万能模板

关联子查询的运用仅在最大最小的情况下可以用，应该是这类问题特例的运用，涉及到大于2的记录，就必须用窗口函数了。

04 聚合函数作为窗口函数

聚合函数和专用窗口函数用法相同，但函数后面括号里不能为空，需要指定聚合的列名。

例子

可以看出，聚合函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值，最小值等）。同时可以看出每一行数据，对整体统计数据的影响。

05 案例：如何在每个组里比较（查找单科成绩高于该科目平均成绩的学生名单）

查找每个组里大于平均值得数据，可以有2种方法：

1）使用上面讲到的窗口函数来实现

2） 使用关联子查询。

05 窗口函数的移动平均

用了rows和preceding 这两个关键字。

这样使用有什么用呢？

在公司业绩名单排名中，可以通过移动平均，直观的查看到与相邻名次业绩的平均,求和等统计数据。

06 总结

一 注意事项

patition子句可以省略，省略就是不指定分组，结果如下，只是按成绩由高到低进行了排序

二 总结

窗口函数语法

语法中的窗口函数有：

1）专用窗口函数,如 rank, dense_rank, row_number 等。

2）聚合函数，如sum,avg,count,max,min等

窗口函数有以下功能：

1）同时具有分组（partiton by）和排序（order by） 的功能

2）不减少原表的行数，所有经常用来在每组内排名

3 注意事项

窗口函数原则上只能写在select 子句中

4 窗口函数使用场景

1）经典top N问题

找出每个部门排名前N的员工进行奖励

2）经典排名问题

业务需求“在每组内排名”，比如：每个部门按业绩来排名

3）在每个组里比较的问题

比如查找每个组里大于平均值得数据，可以有2种方法：

方法1，使用前面窗口函数案例来实现

方法2，使用关联子查询

版权声明：

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！

转载请注明出处，原文链接：https://www.xkablog.com/haskellbc/29583.html