oracle 分析函数

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

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

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。

1. /*基础数据*/  
2. select t.* from test1 t; 

1. /*用lag,lead分析以后的数据*/  
2. select t.id, lag(t.name,1,0) over(order by id desc) max_v, t.name,    
3. lead(t.name,1,0) over(order by id desc) min_v  
4. from TEST1 t;  

1. /*满足查询结果的数据*/  
2. select i.* from(select t.id, lag(t.name,1,0) over(order by id desc) max_v, t.name,    
3. lead(t.name,1,0) over(order by id desc) min_v    
4. from TEST1 t) i where i.name='3aa';  

个人分析：

 分析函数与group by 的作用类似 都用分组计算的作用 但是 分析函数显示列变得可控 例如

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

提供一些例子 以便理解分析函数

 

1.各部门员工薪资在本本门的名次
select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;
select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;
select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;
2.
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp ord er by deptno;
3.
select deptno,ename,sal,lag(ename,2,'example') over(partition by deptno order by ename) from em p
order by deptno;
4.
select deptno, sal,sum(sal) over(partition by deptno) from emp;

--每行记录后都有总计值  select deptno, sum(sal) from emp group by deptno;

5. 求每个部门的平均工资以及每个人与所在部门的工资差额

select deptno,ename,sal ,
     round(avg(sal) over(partition by deptno)) as dept_avg_sal, 
     round(sal-avg(sal) over(partition by deptno)) as dept_sal_diff
from emp;