分析函数

 

/*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...)*/--例子：select empno,sal,deptno,       row_number()over(partition by deptno order by sal desc) as rn,       rank()over(partition by deptno order by sal desc) as rk,       dense_rank()over(partition by deptno order by sal desc) as drk,       count(sal)over(partition by deptno order by sal desc) as ct,       max(sal)over(partition by deptno order by sal desc) as mx,       min(sal)over(partition by deptno order by sal desc) as mn,       sum(sal)over(partition by deptno order by sal desc) as sm,       avg(sal)over(partition by deptno order by sal desc) as ag,       first_value(sal)over(partition by deptno order by sal desc) as fv,       last_value(sal)over(partition by deptno order by sal desc) as lv,       lag(sal)over(partition by deptno order by sal desc) as lg,       lead(sal)over(partition by deptno order by sal desc) as ldfrom emp;