oracle 开窗函数文档,Oracle开窗函数

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

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

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

(2)分类统计 (并显示信息)

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列值

＝＝＝＝＝

(3)select * from test

数据:

A B C

1 1 1

1 2 2

1 3 3

2 2 5

3 4 6

将B栏位值相同的对应的C 栏位值加总

select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum  from test

A B C C_SUM

1 1 1 1

1 2 2 7

2 2 5 7

1 3 3 3

3 4 6 6

如果不需要已某个栏位的值分割,那就要用 null

eg: 就是将C的栏位值summary 放在每行后面

select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum  from test

A B C C_SUM

1 1 1 17

1 2 2 17

1 3 3 17

2 2 5 17

3 4 6 17

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

(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

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    ；

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