分析函数介绍（如：OVER(PARTITION BY... ORDER BY...)）

这里只讲述oracle相关的功能，其实PostgreSQL也有类似的功能。

　　Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

　　常用类型：OVER(PARTITION BY... ORDER BY...)

　　简单解释：PARTITION 中文是分割的意思，ORDER 是排序的意思，所以就是先把一组数据按照制定的字段进行分割成各种组，然后组内按照某个字段排序。

 　　上示例：

　　1.只排序OVER(ORDER BY...)

　　(1)普通方式排序：

　　Sql:

select companyname,drugtype2,drugtypename 
from tn_check_notify t
order by drugtype2 

　　与无排序的对比（伪序号ROWNUM，看看数据原本的顺序）：

　　OVER(ORDER BY ...)的意思是以按照某个字段排序，所以和直接对表的order by效果是一样的：

　　解释：该结果与上面的order by对比，结果是一样的；

　　OVER(ORDER BY ...)功能，就是对新查询得到的数据进行重新编号，即rowNo的值；

　　但是要说明一点，这里是对整个列内容进行排序，并没有进行分组。

 　　2.分区并排序over（partition by .... ）

 　　（1）使用group by进行分组

　　解释：缺点很明显就是group by分组字段必须加入所有的非聚合函数字段。

　　问题补充：Oracle的group by 表达式不足：

　　第一：需要把所有的非聚合函数字段加入到group by 中

　　例如我们要查询一个班级学生的总成绩

select 
b.姓名，
sum(b.成绩)，
b.性别
from  
成绩表 b 
where 
1=1 
group by 
b.姓名，
b.性别

　　在这个sql中的group by中姓名，性别都必须在group by分组里，否则会出现不是group by表达式的错误。

　　第二，在group by 中不能用别名

　　比如

select 
b.姓名 as name，
sum(b.成绩)，
b.性别
from  
成绩表 b 
where 
1=1 
group by 
name，
b.性别

　　把姓名换成name 也是会出现不是group by 表达式的错误，把name 改成b.姓名就可以了。

　　（1）解决上面的问题，可以使用over（partition by ....）

select dense_rank() OVER(PARTITION BY companyname ORDER BY drugtype2) rowNo,
companyname,drugtype2,drugtypename
from tn_check_notify t

　　按照制定的字段，把相同值的排在一起，分为一个块，也就是分组，然后组内排序编号，这样你就可以取不同组内的任意第几个值，类似于TOP-N的分析。

　　在这里over之前使用的函数是ROW_NUMBER()，就是对数据的编号。

　　3.常用的组合总结：

　　常用的分析函数如下所列:
　　row_number() over(partition by ... order by ...)
　　rank() over(partition by ... order by ...)
　　dense_rank() over(partition by ... order by ...)
　　count() over(partition by ... order by ...)
　　max() over(partition by ... order by ...)
　　min() over(partition by ... order by ...)
　　sum() over(partition by ... order by ...)
　　avg() over(partition by ... order by ...)
　　first_value() over(partition by ... order by ...)
　　last_value() over(partition by ... order by ...)
　　lag() over(partition by ... order by ...)
　　lead() over(partition by ... order by ...)

　　在上面的例子里，使用ROW_NUMBER()可以对数据编号，但是有一个问题，例子中的MI_ID是不可以重复的，如果在可以重复的情况下，就有并列的情况，这样就无法取出并列的数据，只能取单一排序的数据。所以这里可以换成 rank() over()和dense_rank() over()来进行编号：（rank() over()和dense_rank() over()的区别如图）

　　4.sum() over()，first_value() over()和last_value() over()的使用

　　（1）sum() over()对所有行求和

　　sum(...) over( order by ... )，和 =  第一行 到 与当前行同序号行的最后一行的所有值求和

with aa as (
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 8 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual
) SELECT a,b,c,
sum(c) over(order by b) sum1,--有排序，求和当前行所在顺序号的C列所有值
sum(c) over() sum2--无排序，求和 C列所有值
from aa

　　补充示例：（order by）

WITH aa AS ( SELECT 1 a,1 b, 3 c FROM dual
UNION SELECT 2 a,2 b, 3 c FROM dual
UNION SELECT 3 a,3 b, 3 c FROM dual
UNION SELECT 4 a,4 b, 3 c FROM dual
UNION SELECT 5 a,5 b, 3 c FROM dual
UNION SELECT 6 a,5 b, 3 c FROM dual
UNION SELECT 7 a,2 b, 3 c FROM dual
UNION SELECT 8 a,2 b, 8 c FROM dual
UNION SELECT 9 a,3 b, 3 c FROM dual )
SELECT a,b,c,
SUM(c) over(order by a) sum1,--有排序，求和当前行所在顺序号的C列所有值
SUM(c) over(order by b) sum2,--有排序，求和当前行所在顺序号的C列所有值
SUM(c) over() sum3  FROM aa order by a       --无排序，求和 C列所有值

　　(2)与 partition by 结合

　　sum(...) over( partition by... )：同组内所行求和

　　sum(...) over( partition by... order by ... )：同第1点中的排序求和原理，只是范围限制在组内

with aa as ( 
SELECT 1 a,1 b, 3 c FROM dual union
SELECT 2 a,2 b, 3 c FROM dual union
SELECT 3 a,3 b, 3 c FROM dual union
SELECT 4 a,4 b, 3 c FROM dual union
SELECT 5 a,5 b, 3 c FROM dual union
SELECT 6 a,5 b, 3 c FROM dual union
SELECT 7 a,2 b, 3 c FROM dual union
SELECT 7 a,2 b, 8 c FROM dual union
SELECT 9 a,3 b, 3 c FROM dual )
SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
FROM aa

　　first_value() over()求分组第一条

　　last_value() over()求分组最后一条

　　其中用row_number() over()取编号第一条的也可以实现first_value() over()的效果

　　5.开窗的窗口范围：

　　over（order by .... range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。

　　over（order by .... rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

解析：