开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。
开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计
算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，在 Oracle 中则被称为分析函数。
数据表（Oracle）：T_Person 表保存了人员信息，FName 字段为人员姓名，FCity 字段为人员所在的城市名，FAge 字段为人员年龄，FSalary 字段为人员工资
向 T_Person 表中插入一些演示数据：
select * from t_person:
要计算所有人员的总数，我们可以执行下面的 SQL 语句：SELECT COUNT(*) FROM T_Person
除了这种较简单的使用方式，有时需要从不在聚合函数中的行中访问这些聚合计算的值。比如我们想查询每个工资小于 5000 元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于 5000 元的员工个数：
虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的 SQL 语句展示了如果使用开窗函数来实现同样的效果：
可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个 OVER 关键字。
开窗函数格式： 函数名(列) OVER(选项)
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。
在上边的例子中，开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
PARTITION BY 子句：
开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独
立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。下面的 SQL 语句用于显示每一个人员的信息以及所属城市的人员数：
COUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。比如对于FName等于 Tom的行，它所属的城市是BeiJing，同
属于BeiJing的人员一共有6个，所以对于这一列的显示结果为6。
这就不需要先对fcity分组求和，然后再和t_person表连接查询了，省事儿。
在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干
扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数：
ORDER BY子句：
开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按
照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。ORDER BY子句的语法为：
RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义；边界规则的可取值见下表：
“RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围，这个子句又被称为定位框架。
例子程序一：查询从第一行到当前行的工资总和：
例子程序二：把例子程序一的row换成了range，是按照范围进行定位的
区别：
下边这的估计不常用：
例子程序三：
例子程序四：
例子程序五：算工资排名
不再用ROWNUM 了 省事了。这个over简写就会出错。
例子程序6：结合max求到目前行的最大值
例子程序6：over(partition by XX order by XX) partition by和order by 结合
员工信息+同龄人最高工资，按工资排序
高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()
除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数，
还可以在开窗函数中使用一些高级的函数，有些函数同时被DB2和Oracle同时支持，比如
RANK()、DENSE_RANK()、ROW_NUMBER()，而有些函数只被Oracle支持，比如
RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。
下面对这几个函数进行详细介绍。
RANK()和DENSE_RANK()函数都可以用于计算一行的排名，不过对于并列排名的处理方式
不同；ROW_NUMBER()函数计算一行在结果集中的行号，同样可以将其当成排名函数。这三个
函数的功能存在一定的差异，举例如下：工资从高到低排名：
rank(),dense_rank()语法：
row_number() 函数语法：
===================================================================
排序函数实际场景使用：计算排行榜，排名
微信活动，每天参与，有得分，活动结束后选出排名靠前的发奖。
每参与一次，就是一个订单，表结构：
比如要查询期号issue为期的排行榜，按得分倒叙排序，得分一样按订单创建先后，算排行，sql需要这么写：
使用了开窗函数后就可以简化：
如果想只要排名范围，可以在外边再包一层，这也是高效分页的一种方式：
欢迎关注个人公众号一起交流学习：
版权声明：
本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。
本文网址：https://www.mushiming.com/mjsbk/14210.html