开窗函数(分析函数)

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整除，所以数据不一定完全平均，然后将不同桶号的数据再分配。

开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。

开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计

算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，在 Oracle 中则被称为分析函数。

数据表(Oracle)：T_Person 表保存了人员信息，FName 字段为人员姓名，FCity 字段为人员所在的城市名，FAge 字段为人员年龄，FSalary 字段为人员工资

CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20),FAge INT,FSalary INT)

向 T_Person 表中插入一些演示数据：

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Tom','BeiJing',20,3000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Tim','ChengDu',21,4000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Jim','BeiJing',22,3500);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Lily','London',21,2000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('John','NewYork',22,1000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('YaoMing','BeiJing',20,3000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Swing','London',22,2000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Guo','NewYork',20,2800);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('YuQian','BeiJing',24,8000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Ketty','London',25,8500);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Kitty','ChengDu',25,3000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Merry','BeiJing',23,3500);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Smith','ChengDu',30,3000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Bill','BeiJing',25,2000);

INSERT INTO T_Person(FName,FCity,FAge,FSalary)

VALUES('Jerry','NewYork',24,3300);

select * from t_person:

要计算所有人员的总数，我们可以执行下面的 SQL 语句：SELECT COUNT(*) FROM T_Person

除了这种较简单的使用方式，有时需要从不在聚合函数中的行中访问这些聚合计算的值。比如我们想查询每个工资小于 5000 元的员工信息(城市以及年龄)，并且在每行中都显示所有工资小于 5000 元的员工个数：

select fname,

fcity,

fsalary,

(select count(*) from t_person where fsalary < 5000) 工资少于5000员工总数

from t_person

where fsalary < 5000

虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的 SQL 语句展示了如果使用开窗函数来实现同样的效果：

select fname, fcity, fsalary, count(*) over() 工资小于5000员工数

from t_person

where fsalary < 5000

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个 OVER 关键字。

开窗函数格式： 函数名(列) OVER(选项)

OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。

在上边的例子中，开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

PARTITION BY 子句：

开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独

立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。下面的 SQL 语句用于显示每一个人员的信息以及所属城市的人员数：

select fname,fcity,fage,fsalary,count(*) over(partition by fcity) 所在城市人数 from t_person

COUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。比如对于FName等于 Tom的行，它所属的城市是BeiJing，同

属于BeiJing的人员一共有6个，所以对于这一列的显示结果为6。

这就不需要先对fcity分组求和，然后再和t_person表连接查询了，省事儿。

在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干

扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数：

--显示每一个人员的信息、所属城市的人员数以及同龄人的人数：

select fname,

fcity,

fage,

fsalary,

count(*) over(partition by fcity) 所属城市的人个数,

count(*) over(partition by fage) 同龄人个数

from t_person

ORDER BY子句：

开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用ORDER BY子句可以对结果集按

照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。ORDER BY子句的语法为：

ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义；边界规则的可取值见下表：

“RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围，这个子句又被称为定位框架。

例子程序一：查询从第一行到当前行的工资总和：

select fname,

fcity,

fage,

fsalary,

sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) 到当前行工资求和

from t_person

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN

UNBOUNDED PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第

一行(UNBOUNDED PRECEDING)到当前行(CURRENT ROW)的和，这样的计算结果就是按照

工资进行排序的工资值的累积和。

“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是开窗函数中最常使用的定位框架，为了简化使用，如果使用的是这种定位框架，则可以省略定位框架声明部分，

也就是说上边的sql可以简化成：

select fname,

fcity,

fage,

fsalary,

sum(fsalary) over(order by fsalary) 到当前行工资求和

from t_person

例子程序二：把例子程序一的row换成了range，是按照范围进行定位的

select fname,

fcity,

fage,

fsalary,

sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到当前行工资求和

from t_person

区别：

这个SQL语句与例1中的SQL语句唯一不同的就是“ROWS”被替换成了“RANGE”。“ROWS”

是按照行数进行范围定位的，而“RANGE”则是按照值范围进行定位的，这两个不同的定位方式

主要用来处理并列排序的情况。比如 Lily、Swing、Bill这三个人的工资都是2000元，如果按照

“ROWS”进行范围定位，则计算从第一条到当前行的累积和，而如果 如果按照 “RANGE”进行

范围定位，则仍然计算从第一条到当前行的累积和，不过由于等于2000元的工资有三个人，所

以计算的累积和为从第一条到2000元工资的人员结，所以对 Lily、Swing、Bill这三个人进行开

窗函数聚合计算的时候得到的都是7000( “ 1000+2000+2000+2000 ”)。

下边这的估计不常用：

例子程序三：

SELECT FName,

FSalary,

SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) 前二后二和

FROM T_Person;

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2

PRECEDING AND 2 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行前两行(2

PRECEDING)到当前行后两行(2 FOLLOWING)的工资和，注意对于第一条和第二条而言它们

的“前两行”是不存在或者不完整的，因此计算的时候也是要按照前两行是不存在或者不完整进

行计算，同样对于最后两行数据而言它们的“后两行”也不存在或者不完整的，同样要进行类似

的处理。

例子程序四：

SELECT FName, FSalary,

SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) 后面一到三之和

FROM T_Person;

这里的开窗函数“SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1

FOLLOWING AND 3 FOLLOWING)”表示按照FSalary进行排序，然后计算从当前行后一行(1

FOLLOWING)到后三行(3 FOLLOWING)的工资和。注意最后一行没有后续行，其计算结果为

空值NULL而非0。

例子程序五：算工资排名

SELECT FName, FSalary,

COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND

CURRENT ROW)

FROM T_Person;

这里的开窗函数“COUNT(*) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED

PRECEDING AND CURRENT ROW)”表示按照FSalary进行排序，然后计算从第一行

(UNBOUNDED PRECEDING)到当前行(CURRENT ROW)的人员的个数，这个可以看作是计算

人员的工资水平排名。

不再用ROWNUM 了  省事了。这个over简写就会出错。

例子程序6：结合max求到目前行的最大值

SELECT FName, FSalary,FAge,

MAX(FSalary) OVER(ORDER BY FAge) 此行之前最大值

FROM T_Person;

这里的开窗函数“MAX(FSalary) OVER(ORDER BY FAge)”是“MAX(FSalary)

OVER(ORDER BY FAge RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)”

的简化写法，它表示按照FSalary进行排序，然后计算从第一行(UNBOUNDED PRECEDING)

到当前行(CURRENT ROW)的人员的最大工资值。

例子程序6：over(partition by XX  order by XX)  partition by和order by 结合

员工信息+同龄人最高工资，按工资排序

SELECT FName, FSalary,FAge,

MAX(FSalary) OVER(PARTITION BY FAge order by fsalary) 同龄人最高工资

FROM T_Person;

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()函数计算一行在结果集中的行号，同样可以将其当成排名函数。这三个

函数的功能存在一定的差异，举例如下：工资从高到低排名：

SELECT FName, FSalary,FAge,

RANK() OVER(ORDER BY fsalary desc) f_RANK,

DENSE_RANK() OVER(ORDER BY fsalary desc) f_DENSE_RANK,

ROW_NUMBER() OVER(ORDER BY fsalary desc) f_ROW_NUMBER

FROM T_Person;

rank(),dense_rank()语法：

RANK()

dense_rank()

【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )

dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。

【参数】dense_rank与rank()用法相当，

【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过

rank()是跳跃排序，有两个第二名时接下来就是第四名(同样是在各个分组内)

dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。

row_number() 函数语法：

ROW_NUMBER()

【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)

【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的)

row_number() 返回的主要是“行”的信息，并没有排名

【参数】

【说明】Oracle分析函数

主要功能：用于取前几名，或者最后几名等

===================================================================

排序函数实际场景使用：计算排行榜，排名

微信活动，每天参与，有得分，活动结束后选出排名靠前的发奖。

每参与一次，就是一个订单，表结构：

比如要查询期号issue为20170410期的排行榜，按得分倒叙排序，得分一样按订单创建先后，算排行，sql需要这么写：

select ROWNUM rank, t.*

from (select *

from t_zhcw_order

where issue = '20170410'

order by integral desc, create_date asc) t

使用了开窗函数后就可以简化：

select t.*,

row_number() over(order by t.integral desc, t.create_date asc) 排名

from t_zhcw_order t

where issue = '20170410'

如果想只要排名范围，可以在外边再包一层，这也是高效分页的一种方式：

select tt.* from (

select t.id,

t.integral,

t.cell,

t.create_date,

row_number() over(order by t.integral desc, t.create_date asc) rankNum

from t_zhcw_order t

where t.issue = 20170331

)tt where tt.rankNum<=50