rank开窗函数_开窗函数-flymin呼噜噜-博客园
开窗函数：在开窗函数出现之前存在着很多⽤ 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 ⼦句创建的分区是独
rank函数的用法⽴于结果集的，创建的分区只是供进⾏聚合计算的，⽽且不同的开窗函数所创建的分区也不互相影响。下⾯的 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 结合
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论