（011）每日SQL学习：SQL开窗函数

开窗函数：在开窗函数出现之前存在着很多用 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,5000

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

分区，并且计算当前行所属的组的聚合计算结果。比如对于FName等于 Tom的行，它所属的城市是BeiJing，同
属于BeiJing的人员一共有6个，所以对于这一列的显示结果为6。

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

在同一个SELECT语句中可以 ORDER BY子句：

开窗函数中可以在OVER关键字后的选项中使用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 ) 同龄人最高工资
FROM T_Person;
--原文写法

SELECT FName, FSalary,FAge,
MAX(FSalary) OVER(PARTITION BY FAge order by Fsalsry ) 同龄人最高工资
FROM T_Person;
--个人觉得显示有点问题，order by 之后最高工资就变成了每个人的工资显示。并不是同龄人的最高工资显示

 

PARTITION BY子句和ORDER BY 可以 共 同 使用，从 而 可以 实现 更 加复 杂 的 功能。

原文地址：http://www.mamicode.com/info-detail-1774214.html