聊聊Oracle排序分析函数

数据库系统应用,最典型的应用场景就是各种报表生成。作为开发人员,最理想的情况是“一句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_numberrankdense_rank几个选择。

 

2row_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还提供了rankdense_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

 

 

3rank函数

 

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_numberrank行为的对比。我们发现在相同的排序取值的情况下,两个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

 

 

4dense_rank函数

 

Dense_rankrank的行为类似,下面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

 

 

Rankdense_rank相同,在相同的取值情况下,排序序号相同。差异在于后面的序号处理差异。Rank是把编号跳过去,而dense_rank这不跳号。

 

5、结论

 

Oracle分析函数的功能非常强大,很多高级报表SQL都是可以借助这类函数进行编写。

上一篇:LinuxSSh服务与免密登录


下一篇:表空间查询和管理