Oracle Rank Over/Row_Number Over 使用方法以及区别

Oracle 提供了三个函数

1) Rank()

2) Row_Number()

3) Dense_Rank()

均需要配合开窗函数Over()使用.这三个函数功能类似,均有统计排名的意味;

以下使用Oracle自带的scott/tiger用户作为例子:

SELECT T.*,
       ROW_NUMBER() OVER(PARTITION BY T.DEPTNO ORDER BY TO_CHAR(T.HIREDATE, 'YYYY') DESC) COL1,
       RANK() OVER(PARTITION BY T.DEPTNO ORDER BY TO_CHAR(T.HIREDATE, 'YYYY') DESC) COL2,
       DENSE_RANK() OVER(PARTITION BY T.DEPTNO ORDER BY TO_CHAR(T.HIREDATE, 'YYYY') DESC) COL3,
       COUNT(1) OVER() COUNT
  FROM EMP T
 ORDER BY T.DEPTNO;

SQL查询每个员工分别在自己所在部门入职年份排名,查询结果如下:

对比COL1,COL2,COL3,可以发现这三个函数的区别.

补充:Oracle的分析函数partition by用法类似聚合函数group by;

给出一个示例:

SELECT DEPTNO 部门内码,
       ENAME 员工姓名,
       SAL 薪水,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) 部门连续求和, --各部门的薪水"连续"求和
       SUM(SAL) OVER(PARTITION BY DEPTNO) 部门总和, -- 部门统计的总和，同一部门总和不变
       100 * ROUND(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 4)||'%' "部门份额(%)",
       SUM(SAL) OVER(ORDER BY DEPTNO, ENAME) 连续求和, --所有部门的薪水"连续"求和
       SUM(SAL) OVER() 总和, -- 此处SUM(SAL) OVER () 等同于SUM(SAL)，所有员工的薪水总和
       100 * ROUND(SAL / SUM(SAL) OVER(), 4)||'%' "总份额(%)"
  FROM EMP ORDER BY DEPTNO;

查询结果:

使用Oracle,学会分析函数在某些场合真的是非常好用.