开窗函数简介 

  与 聚 合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样

每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行

集组是窗口。在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( 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，第三个参数是 超出记录窗口时的默认值）
————————————————————————————————————————————-
注意：

1、开窗函数VS聚合函数：开窗函数对于每个组返回多行，而聚合函数对于每个组只返回一行

2、rank() VS dense_rank()：rank可以将并列第一名的都查找出来；rank()和dense_rank() 区别：rank()是跳跃排序，有两个第二名时接下来就是第四名，dense_rank()是连续排序 ，有两个第二名时仍然跟着第三名。
3、 lag() over(partition by … order by …)：取出前n行数据。　　
　　lead() over(partition by … order by …)：取出后n行数据。

4、over partition by和 group by的区别
group by是对检索结果的保留行进行单纯分组，一般和聚合函数一起使用如max、min等
partition by 虽然也具有分组功能，但同时也具有其他的高级功能

例子：sum() over (PARTITION BY …) 是一个分析函数。 他执行的效果跟普通的sum …group by …不一样，它计算组中表达式的累积和，而不是简单的和。