Oracle分析函数over及开窗函数

核心提示： gds 292 1 gf399 1ddd 3991注意：1.在求第一名成绩的时候，不能用row_number()，Oracle分析函数over及开窗函数(2)，因为如果同班有两个并列第一，row_number()只返回一个结果2.rank()和dense_rank()的区别是：--rank

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

selecta,c,sum(c)over(partitionbya)fromt2

得到结果：

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(orderbysalary)按照salary排序进行累计，orderby是个默认的开窗函数
over(partitionbydeptno)按照部门分区

2.

over(orderbysalaryrangebetween5precedingand5following)

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

例如:对于以下列

aa

1

2

2

2

3

4

5

6

7

9

sum(aa)over(orderbyaarangebetween2precedingand2following)

得出的结果是

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

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

over(orderbysalaryrowsbetween2precedingand4following)

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

4：下面三条语句等效：

over(orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing)

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

over(orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing)

等效