資料庫系統應用，最典型的應用場景就是各種報表生成。作為開發人員，最理想的情況是“一句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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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都是可以藉助這類函式進行編寫。

聊聊Oracle排序分析函式

相關文章