部分分析函数的使用

select deptno,
  ename,
  sal,
row_number() over(partition by deptno order by sal desc) rn,
rank() over(partition by deptno order by sal desc) rank,
dense_rank() over(partition by deptno order by sal desc) dense_rank
 from emp
order by deptno,sal desc;

select deptno,
max(decode(dense_rank,1,sal)) sal1,
max(decode(dense_rank,2,sal)) sal2,
max(decode(dense_rank,3,sal)) sal3
from
( select deptno,sal,
 dense_rank() over(partition by deptno order by sal desc) dense_rank
from emp
)
group by deptno;

 

 

select port,activity
lead(activity) over(partition by port order by activity_date) nxt_activity,
activity_date,
lead(activity_date) over(partition by port order by activity_date) nxt_activity_date
from t;