oracle分析函数row_number() over()使用

一、over函数

row_number() over ([partition by col1] order by col2) ) as 别名

表示根据col1分组，在分组内部根据 col2排序

而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）,[partition by col1] 可省略。

select deptno,
       ename,
       sal,
       sum(sal) over(order by ename),
       sum(sal) over() 总和,
       100 * round(sal / sum(sal) over(), 4) "份额%"
  from emp;

二、row_number(),rank(),dense_rank(),lag(arg1,arg2,arg3)函数
select deptno,sal,row_number() over(partition by deptno order by sal) from emp order by deptno;
select deptno,sal,rank() over(partition by deptno order by sal) from emp order by deptno;
select deptno,sal,dense_rank() over(partition by deptno order by sal) from emp order by deptno;
select deptno,ename,sal,sal-lag(sal,1,null) over(partition by deptno order by sal) from emp order by deptno;