高效的SQL语句之分析函数

高效的SQL语句之分析函数--row_number() /rank()/dense_rank

Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法 ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序 而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） RANK() 类似，不过RANK 排序的时候跟派名次一样，可以并列2个第一名之后 是第3名 LAG 表示 分组排序后 ，组内后面一条记录减前面一条记录的差，第一条可返回 NULL BTW: EXPERT ONE ON ONE 上讲的最详细,还有很多相关特性，文档看起来比较费劲 row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序） rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。 相比之下row_number是没有重复值的 lag（arg1,arg2,arg3): arg1是从其他行返回的表达式 arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。 arg3是在arg2表示的数目超出了分组的范围时返回的值。 有些时候我们希望得到指定数据中的前n列，示例如下： 得到每个部门薪水最高的三个雇员： 先创建示例表 create   table  emp as select   *   from  scott.emp; alter   table  emp add   constraint  emp_pk primary   key (empno); create   table  dept as select   *   from  scott.dept; alter   table  dept add   constraint  dept_pk primary   key (deptno); 先看一下row_number() /rank()/dense_rank()三个函数之间的区别   select  emp.deptno,emp.sal,emp.empno,row_number()  over  (partition  by  deptno  order   by  sal  desc ) row_number,   -- 1,2,3   rank()  over  (partition  by  deptno  order   by  sal  desc ) rank,  -- 1,1,3   dense_rank()  over  (partition  by  deptno  order   by  sal  desc ) dense_rank  from  emp  -- 1,1,2 结果如下: 10      5000.00      7839      1      1      1 10      2450.00      7782      2      2      2 10      1300.00      7934      3      3      3 20      3000.00      7788      1      1      1 20      3000.00      7902      2      1      1 20      2975.00      7566      3      3      2 20      1100.00      7876      4      4      3 20      800.00      7369      5      5      4 30      2850.00      7698      1      1      1 30      1600.00      7499      2      2      2 取每个部门的薪水前三位雇员： select  t.deptno,t.rank,t.sal  from  (   select  emp. * ,row_number()  over  (partition  by  deptno  order   by  sal  desc ) row_number,   -- 1,2,3   rank()  over  (partition  by  deptno  order   by  sal  desc ) rank,  -- 1,1,3   dense_rank()  over  (partition  by  deptno  order   by  sal  desc ) dense_rank  from  emp  -- 1,1,2  ) t where  t.rank <= 3 结果如下： 10      1      5000.00 10      2      2450.00 10      3      1300.00 20      1      3000.00 20      1      3000.00 20      3      2975.00 30      1      2850.00 30      2      1600.00 30      3      1500.00 如果想输出成deptno  sal1   sal2   sal3这种类型的格式 步骤一（decode）： select  t.deptno,decode(row_number, 1 ,sal) sal1,decode(row_number, 2 ,sal) sal2,decode(row_number, 3 ,sal) sal3  from  (   select  emp. * ,row_number()  over  (partition  by  deptno  order   by  sal  desc ) row_number,   -- 1,2,3   rank()  over  (partition  by  deptno  order   by  sal  desc ) rank,  -- 1,1,3   dense_rank()  over  (partition  by  deptno  order   by  sal  desc ) dense_rank  from  emp  -- 1,1,2  ) t where  t.rank <= 3 结果如下： 10      5000          10                    2450      10                              1300 20      3000          20                    3000      20                                2975 30      2850          30                   1600      30                              1500 步骤二（使用聚合函数去除null,得到最终结果）： select  t.deptno, max (decode(row_number, 1 ,sal)) sal1, max (decode(row_number, 2 ,sal)) sal2, max (decode(row_number, 3 ,sal)) sal3  from  (   select  emp. * ,row_number()  over  (partition  by  deptno  order   by  sal  desc ) row_number,   -- 1,2,3   rank()  over  (partition  by  deptno  order   by  sal  desc ) rank,  -- 1,1,3   dense_rank()  over  (partition  by  deptno  order   by  sal  desc ) dense_rank  from  emp  -- 1,1,2  ) t where  t.rank <= 3 group   by  t.deptno  结果如下： 10      5000      2450      1300 20      3000      3000      2975 30      2850      1600      1500

 原文地址 http://blog.youkuaiyun.com/huanghui22/archive/2007/05/03/1595166.aspx