Oralce开窗函数OVER()的一些应用

好久没用oracle了,发现很多东西已经忘记.正好今天改写个语句,顺便回忆了一下,乘热整理以备遗忘.

 

over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数  
over（partition by deptno）按照部门分区  
over（order by salary range between 50 preceding and 150 following）  每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150 
over（order by salary rows between 50 preceding and 150 following）  每行对应的数据窗口是之前50行，之后150行  
over（order by salary rows between unbounded preceding and unbounded following）  每行对应的数据窗口是从第一行到最后一行，等效：  
over（order by salary range between unbounded preceding and unbounded following）   
主要参考资料：《expert one-on-one》 Tom Kyte 《Oracle9i SQL Reference》第6章

 

首先我们用语句构建一个临时表,用于下面的演示:

with test as
(
select rownum as n,mod(rownum,2) as grp from dual
connect by rownum<=10
)
select * from test

结果如下:

N GRP
1 1
2 0
3 1
4 0
5 1
6 0
7 1
8 0
9 1
10 0

使用聚合函数

1.AVG  

功能描述：用于计算一个组和数据窗口内表达式的平均值。

SAMPLE：下面的例子中计算奇数和偶数列的平均值

with test as
(
select rownum as n,mod(rownum,2) as grp from dual
connect by rownum<=10
) 
--后面的例子中将省略去上面部分
select avg(n) over(partition by grp) as avg_n,n
from test

 

结果:

 

 

--加入order by 后的效果,自己观察
select avg(n) over (partition by grp order by n) as avg_n,n
from test

 

 

2.CORR  

功能描述：返回一对表达式的相关系数

3.COVAR_SAMP  

功能描述：返回一对表达式的样本协方差

4.COUNT  

功能描述：对一组内发生的事情进行累积计数

5.CUME_DIST  
功能描述：计算一行在组中的相对位置

 

以上都是常见的聚合函数,当然还包括: max,min等等.
区别就是只使用聚合函数将仅显示各个分组的统计结果,和over()联合使用后会同时显示各个样本数据和统计结果.
所以不在一一进行测试,几乎所有的聚合函数都可以这样使用.

排行函数

1.row_number,rank,DENSE_RANK  

功能描述:对一组数据按某些属性进行排序,它们之前的区别举例如下:

3个人考试:A 100分 B 100分 C90分

对于row_number来说不允许排名相同结果如下:

A 第1名 B 第2名 C 第3名

rank则允许名次相同:

A 第1名 B 第1名 C 第3名

DENSE_RANK允许名称相同的情况,同时要求排名必须连续:

A 第1名 B 第1名 C 第2名

 

SAMPLE:

按奇偶数进行分组并进行排序

select row_number() over (partition by grp order by n ) as rn,n
from test

 

结果:

 

三者的区别演示:

select rank() over (order by grp) as rk
,DENSE_RANK() over (order by grp) as drk
,row_number() over (order by grp) as rn
,n
from test 

 

结果:

 

 

 

2.Lead，Lag

功能描述:对一组记录排序后,取当前行之后或之前的某一条的值
SAMPLE:按奇偶分组,排序后取后面第2条和前面第2条记录值

select lag(n,2) over(partition by grp order by n) as next_n
,lead(n,2) over (partition by grp order by n) as prev_n
,n,grp
from test 

结果如下:

 

3.Last_value,First_value

功能描述:对一组记录排序后,取所在分组的第一条或最后一条记录
SAMPLE:按奇偶分组,排序后取最后一条和第一条的值

select last_value(n) over(partition by grp order by n) as last_N
      ,first_value(n) over (partition by grp order by n) as first_n
      ,n,grp
from test

注意:last 实际是截止到当前行的最后一条记录,这样如果想取最后一条怎么办?当然是用first_value(n) over (partition by grp order by n desc) 

正数最后一名就是倒数第一名.