oracle 分析函数

总是听说oracle的分析函数很强大，一直都没有搞会，今天花了点时间简单研究了一下。

参考：http://www.2cto.com/database/201310/249722.html

           http://blog.csdn.net/yjjm1990/article/details/7524167

一、基本介绍

Oracle从8.1.6开始提供分析函数，个人感觉是group by的升级版。

与聚合函数区别：分析函数可以返回多行聚合的结果，同时可以排序。

常用的格式：Rank over (partition by ...   order by ...)    

组成：pertition by 称为开窗函数；over() 称为分析函数

二、典型的应用

案例一（排名类）

问题：统计各班成绩第一名的同学信息

数据：

NAME  CLASS   S                           
----- ----- -----
fda    1     80                       
ffd    1     78                       
dss    1     95                       
cfe    2     74                       
gds    2     92                       
gf     3     99                       
ddd    3     99                       
adf    3     45                       
asdf   3     55                       
3dd    3     78

SQL：

select * from                                                                         
(select name,class,s,rank()over(partition by class order by s desc) mm from t2  
)where mm=1

结果：

NAME   CLASS    S    MM                                                                                          
-----  ----- ------ -----   
dss     1      95     1                        
gds     2      92     1                        
gf      3      99     1                        
ddd     3      99     1  

这里对class进行分组，同时根据S排序，最终取到S最大的一条数据，并且也显示了name的值。

注意点：

1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果

2.rank()和dense_rank()的区别是： 

--rank()是跳跃排序，有两个第二名时接下来就是第四名

--dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

案例二（聚合类）

数据：

 aa  
----
 1  
 2  
 2  
 2  
 3  
 4  
 5  
 6  
 7  
 9

SQL：

sum(aa)over（order by aa range between 2 preceding and 2 following）

结果：

  AA     SUM  
 ----   ------   
  1       10                                                        
  2       14                                                        
  2       14                                                        
  2       14                                                        
  3       18                                                        
  4       18                                                        
  5       22                                                        
  6       18                                                                  
  7       22                                                                  
  9       9 

解析：

对于aa=5，sum为5-1<=aa<=5+2的和

对于aa=2，sum=1+2+2+2+3+4=14

对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9

注意：

over（order by salary rows between 2 preceding and 4 following）：每行对应的数据窗口是之前2行，之后4行

over（order by salary rows between unbounded preceding and unbounded following）：每行对应的数据窗口是从第一行到最后一行（rows写成range，等效于over(partition by null)）

三、扩展

其他分析函数：

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 ...)