数据库系统应用，最典型的应用场景就是各种报表生成。作为开发人员，最理想的情况是“一句SQL解决一张报表”。但是，面对需求的“云谲波诡”，我们常常会“绞尽脑汁”。这个时候，丰富的经验和知识积累往往是我们解决问题的关键。
在Oracle自拓展SQL功能中，分析函数（Analytical Function）是非常强大的工具。区别于传统SQL函数，分析函数具有功能强大、拓展性强和使用方便的特点。实践中，一些使用标准SQL很难甚至不可能实现的需求，我们借助分析函数就可以“一招定乾坤”。
本篇我们介绍几个Oracle典型的排序分析函数，来帮助我们解决实际问题。
1、从rownum谈起
我们还是选择Oracle 11gR2进行测试。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
数据基础表emp，如下。
SQL> select empno, ename, sal, hiredate, deptno from emp;
EMPNO ENAME SAL HIREDATE DEPTNO
----- ---------- --------- ----------- ------
7369 SMITH 800.00 17-十二月-8 20
7499 ALLEN 1600.00 20-二月-81 30
7521 WARD 1250.00 22-二月-81 30
（篇幅原因，有省略……）
7934 MILLER 1300.00 23-一月-82 10
14 rows selected
我们排序的时候，经常会使用到rownum。一种常用的思路，是先用order by排列好，之后用rownum标号作为排序。但是，rownum往往不会像我们希望的如此工作。
SQL> select empno, ename, sal, deptno, rownum from emp order by sal;
EMPNO ENAME SAL DEPTNO ROWNUM
----- ---------- --------- ------ ----------
7369 SMITH 800.00 20 1
7900 JAMES 950.00 30 12
7876 ADAMS 1100.00 20 11
7521 WARD 1250.00 30 3
7654 MARTIN 1250.00 30 5
7934 MILLER 1300.00 10 14
7844 TURNER 1500.00 30 10
7499 ALLEN 1600.00 30 2
7782 CLARK 2450.00 10 7
7698 BLAKE 2850.00 30 6
7566 JONES 2975.00 20 4
7788 SCOTT 3000.00 20 8
7902 FORD 3000.00 20 13
7839 KING 5000.00 10 9
14 rows selected
最后的数据集合，的确是按照我们希望的sal排序动作结果。但是rownum并没有按照我们希望的出现排序“序号”作用。
这个问题的根源是Oracle Rownum的机理。Rownum并不是一个真实存在的数据列，而是一个随数据集生成而生成的数据列。从上面的结果看，应该是Oracle首先生成了rownum数据列，之后再按照sal进行排序。所以，rownum并不能像我们想象的那样处理。
一些方法可以使用在这个问题上，主要是嵌套子查询方法，让我们可以使用ronwum来解决这个问题。
SQL> select t.*,rownum from (select empno, ename, sal, deptno from emp order by sal) t;
EMPNO ENAME SAL DEPTNO ROWNUM
----- ---------- --------- ------ ----------
7369 SMITH 800.00 20 1
7900 JAMES 950.00 30 2
7876 ADAMS 1100.00 20 3
7521 WARD 1250.00 30 4
7654 MARTIN 1250.00 30 5
7934 MILLER 1300.00 10 6
7844 TURNER 1500.00 30 7
7499 ALLEN 1600.00 30 8
7782 CLARK 2450.00 10 9
7698 BLAKE 2850.00 30 10
7566 JONES 2975.00 20 11
7788 SCOTT 3000.00 20 12
7902 FORD 3000.00 20 13
7839 KING 5000.00 10 14
14 rows selected
结果正确，不过这显然不是什么好方法。在官方手段中，Oracle推荐使用分析函数来解决序号问题。根据不同的实际需求，可以使用row_number、rank和dense_rank几个选择。
2、row_number()
Row_number是一个单纯的序号生成器。我们需要遵从分析函数的具体规则，告诉row_number函数按照那个数据列进行排序和生成行号即可。
SQL> select empno, ename, sal, deptno, row_number() over (order by sal) from emp;
EMPNO ENAME SAL DEPTNO ROW_NUMBER()OVER(ORDERBYSAL)
----- ---------- --------- ------ ----------------------------
7369 SMITH 800.00 20 1
7900 JAMES 950.00 30 2
7876 ADAMS 1100.00 20 3
7521 WARD 1250.00 30 4
7654 MARTIN 1250.00 30 5
7934 MILLER 1300.00 10 6
7844 TURNER 1500.00 30 7
7499 ALLEN 1600.00 30 8
7782 CLARK 2450.00 10 9
7698 BLAKE 2850.00 30 10
7566 JONES 2975.00 20 11
7788 SCOTT 3000.00 20 12
7902 FORD 3000.00 20 13
7839 KING 5000.00 10 14
14 rows selected
正是我们期望的结果。我们注意一下row_number的函数用法，在over后面的括号中，书写上排序原则和方法。从执行计划上，row_number带有很典型的分析函数特性，是一个window标记操作。
SQL> explain plan for select empno, ename, sal, deptno, row_number() over (order by sal) from emp;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3145491563
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 238 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 238 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
9 rows selected
分析函数最大的一个功能是可以使用partition可选参数，用来在其中分组。这个是普通函数很难实现的。例如：我们希望按照部门进行薪水排序，显示出每个员工在部门内部的薪水排名。
SQL> select empno, ename, sal, deptno, row_number() over (partition by deptno order by sal desc) sal_rank from emp;
EMPNO ENAME SAL DEPTNO SAL_RANK
----- ---------- --------- ------ ----------
7839 KING 5000.00 10 1
7782 CLARK 2450.00 10 2
7934 MILLER 1300.00 10 3
7788 SCOTT 3000.00 20 1
7902 FORD 3000.00 20 2
7566 JONES 2975.00 20 3
7876 ADAMS 1100.00 20 4
7369 SMITH 800.00 20 5
7698 BLAKE 2850.00 30 1
7499 ALLEN 1600.00 30 2
7844 TURNER 1500.00 30 3
7654 MARTIN 1250.00 30 4
7521 WARD 1250.00 30 5
7900 JAMES 950.00 30 6
14 rows selected
注意，row_number中的排序参数是不能少的！
SQL> select empno, ename, sal, deptno, row_number() from emp;
select empno, ename, sal, deptno, row_number() from emp
ORA-30484: 丢失的此函数窗口说明
SQL> select empno, ename, sal, deptno, row_number() over () from emp;
select empno, ename, sal, deptno, row_number() over () from emp
ORA-30485: 在窗口说明中丢失 ORDER BY 表达式
排序操作一个有争议和差异的需求点，就是当有相同取值的时候，排序序号的差异。从row_number行为看，Oracle给相同sal的进行顺序排下去的。Oracle还提供了rank和dense_rank功能。
分析函数排序的好处之一就是可以不使用order by的占位，我们可以在一个SQL中，生成多个数据列排序序号。
SQL> select empno, ename, sal, row_number() over (order by sal) sal_row, row_number() over (order by hiredate) hir_row from emp order by empno;
EMPNO ENAME SAL SAL_ROW HIR_ROW
----- ---------- --------- ---------- ----------
7369 SMITH 800.00 1 1
7499 ALLEN 1600.00 8 2
7521 WARD 1250.00 4 3
7566 JONES 2975.00 11 4
7654 MARTIN 1250.00 5 8
7698 BLAKE 2850.00 10 5
7782 CLARK 2450.00 9 6
7788 SCOTT 3000.00 12 13
7839 KING 5000.00 14 9
7844 TURNER 1500.00 7 7
7876 ADAMS 1100.00 3 14
7900 JAMES 950.00 2 10
7902 FORD 3000.00 13 11
7934 MILLER 1300.00 6 12
14 rows selected
3、rank函数
Rank是和row_number相似行为的分析函数。在用法上两者是没有显著性区别的，按照官方说法：rank会跨过tie的情况，也就是重值情况。我们看一下函数结果。
SQL> select empno, ename, sal, row_number() over (order by sal) sal_row, rank() over (order by sal) sal_rank from emp;
EMPNO ENAME SAL SAL_ROW SAL_RANK
----- ---------- --------- ---------- ----------
7369 SMITH 800.00 1 1
7900 JAMES 950.00 2 2
7876 ADAMS 1100.00 3 3
7521 WARD 1250.00 4 4
7654 MARTIN 1250.00 5 4
7934 MILLER 1300.00 6 6
7844 TURNER 1500.00 7 7
7499 ALLEN 1600.00 8 8
7782 CLARK 2450.00 9 9
7698 BLAKE 2850.00 10 10
7566 JONES 2975.00 11 11
7788 SCOTT 3000.00 12 12
7902 FORD 3000.00 13 12
7839 KING 5000.00 14 14
14 rows selected
在SQL中我们使用了row_number和rank行为的对比。我们发现在相同的排序取值的情况下，两个SQL函数的结果有差异。Row_number是将排序序号继续下去，内部随机结果。而rank是也将序号继续下去，但是相同取值的时候，相同值占相同的排名。
同样，rank也可以支持partition字句。
SQL> select empno, ename, deptno,sal, rank() over (partition by deptno order by sal) sal_rank from emp;
EMPNO ENAME DEPTNO SAL SAL_RANK
----- ---------- ------ --------- ----------
7934 MILLER 10 1300.00 1
7782 CLARK 10 2450.00 2
7839 KING 10 5000.00 3
7369 SMITH 20 800.00 1
7876 ADAMS 20 1100.00 2
7566 JONES 20 2975.00 3
7788 SCOTT 20 3000.00 4
7902 FORD 20 3000.00 4
7900 JAMES 30 950.00 1
7654 MARTIN 30 1250.00 2
7521 WARD 30 1250.00 2
7844 TURNER 30 1500.00 4
7499 ALLEN 30 1600.00 5
7698 BLAKE 30 2850.00 6
14 rows selected
4、dense_rank函数
Dense_rank和rank的行为类似，下面SQL用于对比效果。
SQL> select empno, ename, sal, row_number() over (order by sal) sal_row, rank() over (order by sal) sal_rank, dense_rank() over (order by sal) sal_dense_rank from emp;
EMPNO ENAME SAL SAL_ROW SAL_RANK SAL_DENSE_RANK
----- ---------- --------- ---------- ---------- --------------
7369 SMITH 800.00 1 1 1
7900 JAMES 950.00 2 2 2
7876 ADAMS 1100.00 3 3 3
7521 WARD 1250.00 4 4 4
7654 MARTIN 1250.00 5 4 4
7934 MILLER 1300.00 6 6 5
7844 TURNER 1500.00 7 7 6
7499 ALLEN 1600.00 8 8 7
7782 CLARK 2450.00 9 9 8
7698 BLAKE 2850.00 10 10 9
7566 JONES 2975.00 11 11 10
7788 SCOTT 3000.00 12 12 11
7902 FORD 3000.00 13 12 11
7839 KING 5000.00 14 14 12
14 rows selected
Rank和dense_rank相同，在相同的取值情况下，排序序号相同。差异在于后面的序号处理差异。Rank是把编号跳过去，而dense_rank这不跳号。
5、结论
Oracle分析函数的功能非常强大，很多高级报表SQL都是可以借助这类函数进行编写。