Oracle分析函数over及开窗函数

 　　分析函数over 及开窗函数

 

 

　　一：分析函数over

 　　Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是

　　对于每个组返回多行，而聚合函数对于每个组只返回一行。

　　下面通过几个例子来说明其应用。

　　1：统计某商店的营业额。

　　date    sale

　　1          20

　　2          15

　　3          14

　　4          18

　　5           30

 

　　规则：按天统计：每天都统计前面几天的总额

　　得到的结果：

　　DATE   SALE   SUM

　　----- -------- ------

　　1        20 20     --1天

　　2        15 35     --1天+2天

　　3        14 49     --1天+2天+3天

　　4        18            67 .

　　5        30            97 .

 

　　2:统计各班成绩第一名的同学信息

　　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

 

　通过：

--

　　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

 

　　注意：

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

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

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

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

　　3.分类统计 (并显示信息)

 

　　A   B   C

　　-- -- ----------------------

　　m   a  2

　　n   a  3

　　m  a   2

　　n   b   2

　　n   b   1

　　x   b   3

　　x   b   2

　　x   b   4

　　h   b   3

 

select a,c,sum(c)over(partition by a) from t2

得到结果：

 

　　A B C    SUM(C)OVER(PARTITIONBYA)

　　-- -- ------- ------------------------

　　h b 3     3

　　m a 2    4

　　m a 2    4

　　n a 3     6

　　n b 2     6

　　n b 1     6

　　x b 3     9

　　x b 2     9

　　x b 4     9

 

　　如果用sum，group by 则只能得到

　　A SUM(C)

　　-- ----------------------

　　h   3

　　m  4

　　n   6

　　x   9

　　无法得到B列值

　　=====

 

　　二：开窗函数

　　开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：

     1.

over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数

over(partition by deptno)按照部门分区

     2.

over(order by salary range between 5 preceding and 5 following)

　　每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5

　　例如:对于以下列

　　aa

　　1

　　2

　　2

　　2

　　3

　　4

　　5

　　6

　　7

　　9

　　得出的结果是

 

　　A  A   SUM

　　---------------------- -------------------------------------------------------

　　1   1  0

　　2   1  4

　　2   1  4

　　2   1  4

　　3   1  8

　　4   1  8

　　5   2  2

　　6   1  8

　　7   2  2

　　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 ;

　　3：其它：

over(order by salary rows between 2 preceding and 4 following)

　　每行对应的数据窗口是之前2行，之后4行　

over(order by salary rows between 2 preceding and 4 following)

　　每行对应的数据窗口是之前2行，之后4行

　　4：下面三条语句等效：

over(order by salary rows between unbounded preceding and unbounded following)

　　每行对应的数据窗口是从第一行到最后一行，等效：

over(order by salary range between unbounded preceding and unbounded following)

等效

 

三.取得每个部门的前两名

1.

select *
from
(select t.*,row_number() over(partition by deptno order by deptno,sal desc) rn from scott.emp t)
where rn<=2;

 2.

select * from
(select t.*,rank() over(partition by deptno order by deptno ,sal desc) rn from scott.emp t)
where rn<=2;

两个语句结果一样，row_number()按顺序排序，rank()结果一样则序号也一样．

 

 

 

 

这里还有一个使用示例:

[转]Oracle中over函数的使用示例

http://www.cnblogs.com/liguiqing/archive/2007/11/20/966003.html