OVER(PARTITION BY)函数介绍

简介

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

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

1、over函数的写法：

over（partition by class order by sroce） 按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区。

2、开窗的窗口范围：

over（order by sroce range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。

over（order by sroce rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

3、与over()函数结合的函数的介绍

（1）、查询每个班的第一名的成绩：如下

结果为：

1 得到的结果是:
2 dss        1        95        1
3 ffd        1        95        1
4 gds        2        92        1
5 gf         3        99        1
6 ddd        3        99        1
           

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

结果为：

dss      1        95        1  
gfs      2        92        1
ddd      3        99        1 
           

可以看出，本来第一名是两个人的并列，结果只显示了一个。

（2）、rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

求班级成绩排名：

查询结果：

dss        1        95        1
ffd        1        95        1
fda        1        80        3
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        3
asdf       3        55        4
adf        3        45        5
           

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

查询结果：

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

4、sum()over（）的使用

根据班级进行分数求和

dss        1        95        190  --由于两个95都是第一名，所以累加时是两个第一名的相加
ffd        1        95        190 
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376
           

5、first_value() over()和last_value() over()的使用

分别求出第一个和最后一个成绩。

下面还有很多用法，就不一一列举了，简单介绍一下，和上面用法类似：

count() over(partition by … order by …)：求分组后的总数。

　　max() over(partition by … order by …)：求分组后的最大值。

　　min() over(partition by … order by …)：求分组后的最小值。

　　avg() over(partition by … order by …)：求分组后的平均值。

　　lag() over(partition by … order by …)：取出前n行数据。

lead() over(partition by … order by …)：取出后n行数据。

ratio_to_report() over(partition by … order by …)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母。

percent_rank() over(partition by … order by …)：

6、over partition by与group by的区别：

group by是对检索结果的保留行进行单纯分组，一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能，但同时也具有其他的高级功能。