深入浅出谈开窗函数
在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。
为了更加清楚地理解，我们来建表并进行相关的查询
CREATE TABLE T_Person
(
FName VARCHAR(20),
FCity VARCHAR(20),
FAge INT,
FSalary INT
)
Oracle:
CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20), FAge INT,FSalary INT)
注：以下结果只在MSSQLServer中演示：
T_Person 表保存了人员信息，FName 字段为人员姓名，FCity 字段为人员所在的城市名，FAge 字段为人员年龄，FSalary 字段为人员工资。然后执行下面的SQL语句向 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);
开窗函数简介
与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在ISO SQL规定了这样的函数为开窗函数，在 Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数。
要计算所有人员的总数，我们可以执行下面的 SQL语句： SELECT COUNT(*) FROM T_Person 除了这种较简单的使用方式， 有时需要从不在聚合函数中的行中访问这些聚合计算的值。比如我们想查询每个工资小于 5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000元的员工个数，尝试编写下面的 SQL语句：
SELECT FCITY , FAGE , COUNT(*)
FROM T_Person
HERE FSALARY<5000
执行上面的SQL以后我们会得到下面的错误信息：
选择列表中的列 \’T_Person.FCity\’ 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。这是因为所有不包含在聚合函数中
的列必须声明在GROUP BY 子句中，可以进行如下修改：
SELECT FCITY, FAGE, COUNT(*)
FROM T_Person
WHERE FSALARY<5000
GROUP BY FCITY , FAGE
这个执行结果与我们想像的是完全不同的，这是因为GROUP BY子句对结果集进行了分组，所以聚合函数进行计算的对象不再是所有的结果集，而是每一个分组。 可以通过子查询来解决这个问题，SQL如下： SELECT FCITY , FAGE ,
(
SELECT COUNT(* ) FROM T_Person
WHERE FSALARY<5000
)
FROM T_Person
WHERE FSALARY<5000
虽然使用子查询能够解决这个问题，但是子查询的使用非常麻烦，使用开窗函数则可以大大简化实现，下面的SQL语句展示了如
果使用开窗函数来实现同样的效果：
SELECT FCITY , FAGE , COUNT(*) OVER()
FROM T_Person
WHERE FSALARY<5000
可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER 关键字。
开窗函数的调用格式为：
函数名(列) OVER(选项)
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用OVER 关键字来区分这两种用法。
在上边的例子中，开窗函数COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
over()函数写法：over(partition by expr2 order by expr3)
根据expr2对结果进行分区，在各分区内按照expr3进行排序；
over函数不能单独使用，需要与row_number()，rank()和dense_rank，lag()和lead(),sum()等配合使用。
分区partiton by 与 group by的区别
group by会将结果集按照指定字段进行聚合，结果集会缩减，在统计部门人数，平均工资时会用到；
partition by会对结果集按照指定字段分层排列，结果集不会缩减，如将公司所有人按照部门进行分区，会发现结果集中同一部门的人会连续排列。
根据配合使用的函数情况的不同，分层次介绍：
– row_number()，rank()，dense_rank()
– lag()与lead()
– sum()
1、与row_number()，rank()，dense_rank()的使用
这三个函数与over连用时order by 不能丢失，不写partition by时默认结果集为一整个分区。
row_number()与over(partition exp2 order by exp3)配合使用可以对分区根据exp1进行排序，此排序是从1开始，即使遇到相同的数据，也区分开来排序。
SELECT e.employee_id,
e.department_id,
row_number() over(PARTITION BY e.department_id ORDER BY e.salary),
e.salary
FROM employees e;
rank()与over(partition exp2 order by exp3)配合使用可以对分区根据exp3进行可并列排序，此排序是从1开始，遇到相同的数据，会排成同一名，排名可能不连续。
SELECT e.employee_id,
e.department_id,
rank() over(PARTITION BY e.department_id ORDER BY e.salary),
e.salary
FROM employees e;
dense_rank()表示每一条数据在所属分区的等级
SELECT e.employee_id,
e.department_id,
dense_rank() over(PARTITION BY e.department_id ORDER BY e.salary),
e.salary
FROM employees e;
2、与lag()与lead()的使用
这两个函数与over连用时order by 不能丢失，不写partition by时默认结果集为一整个分区。
lag(expr1) over(partiton by expr2 order by expr3)
在分区内获取前一行数据的expr1字段的值，第一行为空（第一行无前一行）
SELECT
e.employee_id,
e.department_id,
lag(e.department_id) over(PARTITION BY
e.department_id ORDER BY e.salary)
FROM employees e;
lead(expr1) over(partiton by expr2 order
by expr3)
在分区内获取后一行数据的expr1字段的值，最后一行为空（最后一行无后一行）
SELECT
e.employee_id,
e.department_id,
lead(e.department_id) over(PARTITION BY
e.department_id ORDER BY e.salary)
FROM employees e;
在此作用不太明显，但是若配合decode等来使用，会有不一样的效果，如可以将分区内重复的结果数据置为空。
代码如下：
SELECT
decode(department, NULL, t.department_id, NULL) department_id,
decode(department, NULL,
d.department_name, NULL) department_name,
decode(department, NULL, t.employee, NULL)
employee,
decode(department, NULL, t.avg_s, NULL)
avg_sal,
t.last_name,
t.salary,
t.job_id
FROM departments d,
(SELECT e.department_id,
e.last_name,
e.salary,
e.job_id,
avgs.avg_s,
avgs.employee,
lag(e.department_id)
over(PARTITION BY e.department_id ORDER BY e.last_name) department –用于判断的别名
FROM employees e,
(SELECT e2.department_id, –部门工资平均值与部门人数
trunc(AVG(e2.salary), 2) avg_s,
COUNT(*) employee
FROM employees e2
GROUP BY e2.department_id)
avgs
WHERE e.department_id =
avgs.department_id) t
WHERE d.department_id = t.department_id;
3、与sum()的使用
sum()与over连用时order by 可以不写，不写partition by时默认结果集为一整个分区；需要特别注意的是，此处的sum不是求整个分区的和，而是按照order by expr3字段进行分层后，求出分区中小于等于自身所在分层等级的和，即连续求和。
sum(expr1) over(partition by expr2 order by expr3)
不写order by字段，默认整个分区属于同一层
多个sum() over()连用时，分区只按照顺序出现的第一个order by进行排序；每个sum()计算的值，按自身的order by进行分层后计算，类似于使用一个子查询进行计算。
SELECT
e.employee_id,
e.department_id,
e.salary,
SUM(e.salary) over(PARTITION BY
e.department_id),
SUM(e.salary) over(PARTITION BY
e.department_id ORDER BY e.salary)
FROM employees e
WHERE e.department_id = 100;
此时按照的是第二个sum+over的order by e.salary字段对分区进行的排序。（第一个sum+over无order by 字段）
对于第一个sum+over，默认为所有成员在同一层，所以第四列的值全为分区员工工资总和。
SELECT
e.employee_id,
e.job_id,
e.salary,
SUM(e.salary) over(PARTITION BY
e.department_id ORDER BY e.salary),
SUM(e.salary)
over(PARTITION BY e.department_id ORDER BY e.job_id)
FROM employees e
WHERE e.department_id = 100;
此处结果集按照第一个sum+over的order by e.salary进行排序，第五列的结果是按照e.job_id进行分层后连续求和得到的，第一层job_id=FI_ACCOUNT,共五个，所以那五行的第五列结果都为第一层的和；job_id=FI_MGR属于按job_id分层的第二层，所以计算结果为第一层+第二层的连续求和。
答疑：
1. partition by用于给结果集进行分区。
2. partition by和group by有何区别？
partition by只是将原始数据进行名次排列(记录数不变)
group by是对原始数据进行聚合统计(记录数可能变少, 每组返回一条)
3. 使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。
可以这样： rank over(partition by course order by score desc nulls last)