oracle之分析函数over及开窗函数

oracle之分析函数over及开窗函数

一：分析函数over

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

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

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是连续排序，有两个第二名时仍然跟着第三名

二：开窗函数

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

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

SQL>select sum(aa)over（order by aa range between 2 preceding and 2 following）from A1;

得出的结果是

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 ;

3：其它：

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）

等效

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

--

--

--

常用的分析函数如下所列:

1、row_number() over(partition by ... order by ...)

2、rank() over(partition by ... order by ...)

3、dense_rank() over(partition by ... order by ...)

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