开窗函数(分析函数)
 
2.7.1 特点：开窗函数也就是在满足某种条件的记录集合上执行的特殊函数；
 
对于每条记录都要在此窗口内执行函数；
 
有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；
 
有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口；
 
开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。
 
2.7.2 开窗函数和普通聚合函数的区别聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条
 
聚合函数也可以用于开窗函数中。
 
2.7.3 语法开窗函数名([]) over([partition by ] [order by [desc]] [])partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
 
order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
 
frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
 
关于over的参数over参数为空，没有指定分区、排序、滑动窗口时，开窗函数将整个表作为一个区，默认计算的是所有值；
 
over指定了分区，未指定排序和滑动窗口，开窗函数默认计算分区内的所有值；
 
over指定了分区、排序，未指定滑动窗口，开窗函数默认计算的时第一行到当前行的值；
 
2.7.4 滑动窗口范围指定的两种方式方式一：基于行
 
对于滑动窗口的范围指定，通常使用 between frame_start and frame_end 语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：current row 边界是当前行，一般和其他范围关键字一起使用
 
unbounded preceding 边界是分区中的第一行
 
unbounded following 边界是分区中的最后一行
 
expr preceding 边界是当前行减去expr的值
 
expr following 边界是当前行加上expr的值
 
移动平均可用来消除差额较大的情况，平滑数据方式二：基于范围
 
和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：interval 7 day preceding。
 
2.7.5 开窗函数分类动态窗口函数：first_value() / last_value()/nth_value()/聚合函数用于开窗
 
如没有指定排序和滑动窗口范围，默认计算的是分区内的所有记录。 指定分区和排序后，如没有指定滑动窗口范围，默认计算的是分区内的第一行到当前行。静态窗口函数：row_number() / rank() / dense_rank()/percent_rank() / cume_dist()/lag() / lead()/ntile()
 
不管是否指定滑动窗口范围，窗口都是固定的，所以指定的滑动窗口范围无效。
 
2.7.6 按照函数功能不同，MySQL支持的开窗函数分为如下几类：序号函数：row_number() / rank() / dense_rank()
 
函数名显示内容显示样式row_number()显示分区中不重复不间断的序号1，2，3，4...dense_rank()显示分区中重复不间断的序号1，1，2，3，3...rank()显示分区中重复间断的序号1，1，3，4...分布函数：percent_rank() / cume_dist()percent_rank()：和之前的rank()函数相关，每行计算：(rank - 1) / (rows - 1)
 
其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数。rank() over(partition by user_no order by amount desc) rank1,
 
percent_rank() over(partition by user_no order by amount desc) percentcume_dist()： 分组内小于等于当前rank值的行数/分组内总行数
 
示例：查询大于等于当前订单金额的订单比例rank() over(partition by user_no order by amount desc) rank1,
 
cume_dist() over(partition by user_no order by amount desc) cume前后函数：lag() / lead()
 
分区中位于当前行前n行(lag)/后n行(lead)的记录值
 
示例：查询上一个订单距离当前订单的间隔天数lag(create_date,1) over(partition by user_no order by create_date) last_date头尾函数：first_value() / last_value()
 
分区中的第一个/最后一个指定参数的值
 
示例：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额first_value(amount) over(partition by user_no order by create_date) first_amount,
 
last_value(amount) over(partition by user_no order by create_date) last_amount其他函数：nth_value() / nfile()nth_value(expr,n)：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名
 
示例：每个用户订单中显示金额排名第二和第三的订单金额nth_value(amount,2) over(partition by user_no order by amount) second_amount,
 
nth_value(amount,3) over(partition by user_no order by amount) third_amount
 
2. nfile(n)：将分区中的有序数据分为n个桶，记录桶号
 
示例：将每个用户的订单按照订单金额分成3组
 
此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用nfile(n)对数据进行分组，由于记录数不一定被n整除，所以数据不一定完全平均，然后将不同桶号的数据再分配。

 
开窗函数：
 
它可以理解为记录集合，开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。
 
对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；
 
有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。
 
开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。
 
开窗函数和普通聚合函数的区别聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条。
 
聚合函数也可以用于开窗函数中。
 
按照函数功能不同，MySQL支持的开窗函数分为如下几类：
 
序号函数：row_number() / rank() / dense_rank()
 
分布函数：percent_rank() / cume_dist()
 
前后函数：lag() / lead()
 
头尾函数：first_value() / last_value()
 
其他函数：nth_value() / nfile()
 
开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由
 
over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，开窗函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：
 
partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
 
order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
 
frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。
 
current row 边界是当前行，一般和其他范围关键字一起使用
 
unbounded preceding 边界是分区中的第一行
 
unbounded following 边界是分区中的最后一行
 
expr preceding 边界是当前行减去expr的值
 
expr following 边界是当前行加上expr的值
 
rows between 1 preceding and 1 following 窗口范围是分区中的当前行、前一行、后一行一共三行记录。
 
rows between 1 preceding and current row 窗口范围是分区中的前一行、当前行一共两行记录。
 
rows between current row and 1 following 窗口范围是分区中的当前行、后一行一共两行记录。
 
rows unbounded preceding 窗口范围是分区中的第一行到当前行。
 
rows between unbounded preceding and current row 窗口范围是分区中的第一行到当前行。
 
rows between current row and unbounded following 窗口范围是分区中的当前行到最后一行。
 
rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行，等同于不写。
 
————————————————
 
版权声明：本文为CSDN博主「阿柯@CDA」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
 
原文链接：https://blog.csdn.net/kejiayuan0806/java/article/details/103297893

 
引用
 
在开窗函数出现之前存在着非常多用 SQL 语句非常难解决的问题，非常多都要通过复杂的相关子查询或者存储过程来完毕。为了解决这些问题，在2003年ISO SQL标准增加了开窗函数，开窗函数的使用使得这些经典的难题能够被轻松的解决。眼下在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，只是非常遗憾的是 MYSQL 临时还未对开窗函数给予支持。
 
开窗函数说明
 
开窗函数与聚合函数一样，也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。
 
语法
 
主要是over( PARTITION BY (根据某条件分组,形成一个小组)….ORDER BY(再组内进行排序) …. )
 
常用函数
 
(最常用的应该是1.2.3 的排序)
 
1、row_number() over(partition by … order by …)
 
增加一列,类似与增加伪列
 
2、rank() over(partition by … order by …)
 
3、dense_rank() over(partition by … order by …)
 
rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。
 
dense_rank(): 连续排序，如果有两个第一级时，接下来仍然是第二级。
 
4、count() over(partition by … order by …)
 
5、max() over(partition by … order by …)
 
6、min() over(partition by … order by …)
 
7、sum() over(partition by … order by …)
 
8、avg() over(partition by … order by …)
 
9、first_value() over(partition by … order by …)
 
10、last_value() over(partition by … order by …)
 
与函数的功能一致,只是是开窗函数
 
11、lag() over(partition by … order by …)
 
12、lead() over(partition by … order by …)
 
lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联)；
 
lag ，lead 分别是向前，向后；
 
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值)
 
MySQL为什么不支持
 

 
一个学习性任务：每个人有不同次数的成绩，统计出每个人的最高成绩。
 
这个问题应该还是相对简单，其实就用聚合函数就好了。
 
select id,name,max(score) from Student group by id,name order by name
 
上边这种情况只适用id 和name是一一对应的，否则查询出来的数据是不正确的。
 
例如 ： 1 张三 100
 
2 张三 90
 
查询出来的结果
 
两条信息都会输出。
 
避免这种情况，可以使用开窗函数。
 
个人理解就是，开窗函数和聚合函数功能是相反的。
 
聚合函数，将多行数据合并成一行数据；而开窗函数则是将一行数据拆分成多行。
 
开窗函数可以满足上述问题，同事也可以满足其他问题。例如：求每个班最高成绩学生的信息。
 
分析：每个人学号一定是不同的，名字可能有重名，最大复杂的情况是，每个班最高成绩可能不止一个。
 
如果继续使用开始的方式，那么是不能满足要求的。
 
使用开窗函数就能很好的解决这个问题。
 
--每个班级的成绩第一的学生
 
--学生表中信息如下
 
a 1 80
 
b 1 78
 
c 1 95
 
d 2 74
 
e 2 92
 
f 3 99
 
g 3 99
 
h 3 45
 
i 3 55
 
j 3 78
 
查询结果如下：
 
c 1 95 1
 
e 2 92 1
 
f 3 99 1
 
g 3 99 1
 
SQL查询语句如下：
 
select *
 
from
 
(
 
select name,class,s,rank()over(partition by class order by s desc) mm
 
from t2
 
) as t
 
where t.mm=1
 
心得：
 
rank()跳跃排序，有两个第二名时后边跟着的是第四名
 
dense_rank() 连续排序，有两个第二名时仍然跟着第三名
 
over()开窗函数：在使用聚合函数后，会将多行变成一行，
 
而开窗函数是将一行变成多行；
 
并且在使用聚合函数后，如果要显示其他的列必须将列加入到group by中，
 
而使用开窗函数后，可以不使用group by，直接将所有信息显示出来。
 
开窗函数适用于在每一行的最后一列添加聚合函数的结果。
 
常用开窗函数：
 
1.为每条数据显示聚合信息.(聚合函数() over())
 
2.为每条数据提供分组的聚合函数结果(聚合函数() over(partition by 字段) as 别名) --按照字段分组，分组后进行计算
 
3.与排名函数一起使用(row number() over(order by 字段) as 别名)
 
常用分析函数：(最常用的应该是1.2.3 的排序)
 
1、row_number() over(partition by ... order by ...)
 
2、rank() over(partition by ... order by ...)
 
3、dense_rank() over(partition by ... order by ...)
 
4、count() over(partition by ... order by ...)
 
5、max() over(partition by ... order by ...)
 
6、min() over(partition by ... order by ...)
 
7、sum() over(partition by ... order by ...)
 
8、avg() over(partition by ... order by ...)
 
9、first_value() over(partition by ... order by ...)
 
10、last_value() over(partition by ... order by ...)
 
11、lag() over(partition by ... order by ...)
 
12、lead() over(partition by ... order by ...)
 
lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联)；
 
lag ，lead 分别是向前，向后；
 
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值)
 
开窗函数 First_Value 和 Last_Value
 
在Sql server 2012里面,开窗函数丰富了许多,其中带出了2个新的函数 First_Value 和 Last Value .现在来介绍一下这2个函数的应用场景. 首先分析一下First_Va ...
 
Oracle开窗函数 over()(转)
 
copy文链接:http://blog.csdn.net/yjjm1990/article/details/7524167#,http://www.2cto.com/database/201402/2 ...
 
oracle的分析函数over 及开窗函数
 
转:http://www.2cto.com/database/201310/249722.html oracle的分析函数over 及开窗函数   一:分析函数over   Oracle从8.1.6开 ...
 
SQL使用开窗函数与CTE查询每月销售额的前几名
 
WITH tagTab AS( SELECT YearMonth, pm=RANK() OVER(PARTITION BY YearMonth ORDER BY amount DESC) FROM S ...
 
利用OVER开窗函数分页
 
在SQL Server中,利用SQL进行分页的方法也有很多,今天要总结的是SQL Server 2005中引入的OVER开窗口函数,然后利用开窗函数进行分页. 示例代码如下: -- 设置数据库上下文 ...
 
sqlserver开窗函数
 
从 http://jimshu.blog.51cto.com/3171847/1376637/ 转 开窗函数是在 ISO 标准中定义的.SQL Server 提供排名开窗函数和聚合开窗函数. 在开窗函 ...
 
SQL ServerOVER 子句,over开窗函数,SQL SERVER 开窗函数
 
https://technet.microsoft.com/zh-cn/library/ms189461(v=sql.105).aspx http://www.cnblogs.com/85538649 ...
 
sql over开窗函数，
 
sql over开窗函数, 1.使用over子句与rows_number()以及聚合函数进行使用,可以进行编号以及各种操作.而且利用over子句的分组效率比group by子句的效率更高. 2.在订单 ...
 
随机推荐
 
SAS零散知识总结
 
1,变量名命名规范:以字母或者下划线开始,可包含字母.下划线.数字,且不超过32个字符: 2,INFILE用于读取外部数据文件,一般于FILENAME(和LIBNAME用户一致,但路径要精确到文件名( ...
 
Android源码笔记——Camera系统架构
 
Camera的架构与Android系统的整体架构保持一致,如下图所示,本文主要从以下四个方面对其进行说明. Framework:Camera.java Android Runtime:android_ ...
 
【BZOJ】【2132】圈地计划
 
网络流/最小割 Orz Hzwer 这类大概是最小割建模中的经典应用吧…… 黑白染色,然后反转黑色的技巧感觉很巧妙!这个转化太神奇了…… /****************************** ...
 
Java NIO原理图文分析及代码实现
 
原文: http://weixiaolu.iteye.com/blog/1479656 目录: 一．java NIO 和阻塞I/O的区别      1. 阻塞I/O通信模型      2. java ...
 
关于SRAM,DRAM,SDRAM,以及NORFLASH,NANDFLASH
 
韦东山的视频里面说S3C2440有4KB的内存,这个其实是不正确的,这4KB的RAM严格说不应该叫内存,严格来说芯片外面的64MB的SDRAM才能叫做内存,里面的那4KB只是当nandflash启动的 ...
 
mybatis的配置与使用
 
mybatis的配置与使用 一.全局配置文件配置 properties标签 Properties标签可以用来加载配置文件.例如,我们可以将数据库的连接信息放入到一个配置文件(db.properties ...
 
microsoft.jet.oledb.4.0 未注册
 
64位系统已经不支持4.0 解决办法 1.下载 ACE2010的驱动,64位的. http://www.microsoft.com/downloads/zh-cn/details.aspx?famil ...
 
JavaScript设计模式 - 订阅发布模式(观察者模式)
 
var Event = (function() { var global = this, Event, _default = 'default'; Event = function() { var _ ...
 
centos安装autossh
 
$ sudo yum install wget gcc make$ wget http://www.harding.motd.ca/autossh/autossh-1.4e.tgz$ tar -xf ...