分析函数学习(二)-->>排名函数分析

一： 排名分析函数有 row_number, dense_rank, rank, first, last, ntile 等. 其中row_number, dense_rank, rank , first, last 都需要 order by

      A。row_number, dense_rank, rank, 三者区别如下

           row_number    排名不重复连续

          dense_rank     排名重复连续

          rank                   排名重复不连续

   案例说明：

        SELECT t.ename , t.deptno, t.sal,
                        row_number()over(PARTITION BY t.deptno ORDER BY t.sal) AS rw,
                        rank()over(PARTITION BY t.deptno ORDER BY t.sal) AS rk,
                       dense_rank() over(PARTITION BY t.deptno ORDER BY t.sal) AS d_rk
          FROM test_emp t        

  结果：

   

B .  last /first 排名查询

    假设有这样的需求; 检索出每个部门的工资最多, 最少的姓名. 一般情况做法：先检索出工资最多的 在检索出工资最少的  最后合并

  但是利用last /first 能非常快捷的得到答案。

 SELECT t.deptno,
                 MIN(t.ename) KEEP(dense_rank LAST ORDER BY t.sal) AS max_sal_name,
                 MIN(t.ename) KEEP(dense_rank FIRST ORDER BY t.sal) AS min_sal_name
    FROM emp t
  GROUP BY t.deptno

结果：

   

其中：min 确定结果的唯一性 也可以用max.

           keep 说明Oracle只保留符合keep条件的记录.

          dense_rank 告诉Oracle排列的策略，first/last则告诉最终筛选的条件.

C.  ntile 层次查询

      有时我们可能会接到类似这样的需求： 查询出每个部门工资排名前1/5的员工信

      SELECT *
          FROM (
                  SELECT t.deptno,        t.ename,              t.sal,
                                  ntile(5) over(PARTITION BY t.deptno ORDER BY t.sal DESC) AS rn
                     FROM emp t)
         WHERE rn=1

  结果：