Oracle SQL篇（三）Oracle ROWNUM 與TOP N分析

?

?

?

首先我們來看一下ROWNUM：
含義解釋：
1、rownum是oracle為從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推。這是一個偽列，可以用于限制查詢返回的總行數。
2、rownum不能以任何基表的名稱作為前綴。

對于ROWNUM來說，通常我們可以使用的比較符是<和<=,不能單獨的使用=、>、>=等比較運算符，其實我們可以這樣簡單的 理解，oracle是找到第一條的記錄添加序號1之后，才可以知道誰是第二條記錄，然后添加序號2，以此類推。所以對于等于來說，是可以有例外的，就是 rownum=1。

我們來看幾個簡單的演示：
?
scott@DB01> create table demo as select demono,ename,sal,comm,deptno from demo;
Table created.
scott@DB01> select rownum,t.* from demo t;

??? ROWNUM????? demoNO ENAME???????????? SAL?????? COMM???? DEPTNO
---------- ---------- ---------- ---------- ---------- ----------
???????? 1?????? 7369 SMITH???????????? 800??????????????????? 20
???????? 2?????? 7499 ALLEN??????????? 1600??????? 300???????? 30
???????? 3?????? 7521 WARD???????????? 1250??????? 500???????? 30
???????? 4?????? 7566 JONES??????????? 2975??????????????????? 20
???????? 5?????? 7654 MARTIN?????????? 1250?????? 1400???????? 30
???????? 6?????? 7698 BLAKE??????????? 2850??????????????????? 30
???????? 7?????? 7782 CLARK??????????? 2450??????????????????? 10
???????? 8?????? 7788 SCOTT??????????? 3000??????????????????? 20
???????? 9?????? 7839 KING???????????? 5000??????????????????? 10
??????? 10?????? 7844 TURNER?????????? 1500????????? 0???????? 30
??????? 11?????? 7876 ADAMS??????????? 1100??????????????????? 20
??????? 12?????? 7900 JAMES???????????? 950??????????????????? 30
??????? 13?????? 7902 FORD???????????? 3000??????????????????? 20
??????? 14?????? 7934 MILLER?????????? 1300??????????????????? 10

14 rows selected.

scott@DB01> select rownum,demono,ename,sal from demo where rownum<=3;

??? ROWNUM????? demoNO ENAME???????????? SAL
---------- ---------- ---------- ----------
???????? 1?????? 7369 SMITH???????????? 800
???????? 2?????? 7499 ALLEN??????????? 1600
???????? 3?????? 7521 WARD???????????? 1250

scott@DB01> select rownum,demono,ename,sal from demo where rownum<3;

??? ROWNUM????? demoNO ENAME???????????? SAL
---------- ---------- ---------- ----------
???????? 1?????? 7369 SMITH???????????? 800
???????? 2?????? 7499 ALLEN??????????? 1600

scott@DB01> select rownum,demono,ename,sal from demo where rownum=1;

??? ROWNUM????? demoNO ENAME???????????? SAL
---------- ---------- ---------- ----------
???????? 1?????? 7369 SMITH???????????? 800

scott@DB01> select rownum,demono,ename,sal from demo where rownum=3;

no rows selected

scott@DB01> select rownum,demono,ename,sal from demo where rownum>3;

no rows selected

如果我們想要查詢結果集中的某一段范圍的記錄，比如5-10條的記錄，該如何查詢呢？很多開發人員把這樣的需求稱為分頁
scott@DB01> select rownum,demono,ename,sal from demo where rownum between 5 and 10;
no rows selected

上面是一個錯誤的例子，我們來看正確的寫法，這里我們使用到了集合運算符minus(減法運算）
scott@DB01> select rownum,demono,ename,sal from demo where rownum<=10
? 2???????? minus
? 3???????? select rownum,demono,ename,sal from demo where rownum<=4;

??? ROWNUM????? demoNO ENAME???????????? SAL
---------- ---------- ---------- ----------
???????? 5?????? 7654 MARTIN?????????? 1250
???????? 6?????? 7698 BLAKE??????????? 2850
???????? 7?????? 7782 CLARK??????????? 2450
???????? 8?????? 7788 SCOTT??????????? 3000
???????? 9?????? 7839 KING???????????? 5000
??????? 10?????? 7844 TURNER?????????? 1500

6 rows selected.

如果我們有這樣一個需求，找到員工demo表中，薪水最高的前三名，如何來實現呢？在sql server中有標準的top n分析語句，不過不要放到oracle里來使用，不同的數據庫還是有區別的。當然了，也許你會認為這個問題有歧義，是前三個人呢？還是薪水排在最高3位的人？因為薪水有可能是相同的，在這里我們就找前三個人，看下面的語句：

scott@DB01> select rownum,demono,ename,sal
???????? 2> from demo
???????? 3> where rownum<=3
???????? 4> order by sal desc;

??? ROWNUM????? demoNO ENAME???????????? SAL
---------- ---------- ---------- ----------
???????? 2?????? 7499 ALLEN??????????? 1600
???????? 3?????? 7521 WARD???????????? 1250
???????? 1?????? 7369 SMITH???????????? 800

這個語句從表面上來看好像是正確的，從demo表里查詢數據，排序，最后利用rownum返回前三個人，但是我們看語句的執行結果顯然是不正確的。對于oracle的語句，我們在執行的時候遵循top-down的順序，或者我們可以說，語句按照順序來執行。

當然也有個別例外：
scott@DB01> select deptno,sum(sal) from demo
? 2???????? group by deptno
? 3???????? having sum(sal)>=10000;

??? DEPTNO?? SUM(SAL)
---------- ----------
??????? 20????? 10875

scott@DB01> select deptno,sum(sal) from demo
? 2???????? having sum(sal)>=10000
? 3???????? group by deptno;

??? DEPTNO?? SUM(SAL)
---------- ----------
??????? 20????? 10875

對于前面排名的語句當然是有問題的。當第2行語句執行后，拿到表中所有的數據，第3行語句緊接著執行，就把最前面的三條記錄取出來了（rownum是對查詢結果添加序號），這個時候，再做排序，當然拿到的就是對前面三條記錄排序的結果，如上所示。

正確的思路應該是，先做排序，再做條件篩選，也就是4行子句要在3行之前運行，如何來實現呢？在這里oracle借助了子查詢，用oracle的標準表述叫做內聯視圖（inline view），當然整個的sql就是oracle的top N分析語句的寫法，我們來看例子：

scott@DB01>? select rownum rank,t.*
? 2????????? from (select demono,ename,sal from demo order by sal desc) t
? 3????????? where rownum<=3;

????? RANK????? demoNO ENAME???????????? SAL
---------- ---------- ---------- ----------
???????? 1?????? 7839 KING???????????? 5000
???????? 2?????? 7788 SCOTT??????????? 3000
???????? 3?????? 7902 FORD???????????? 3000

在這里，oracle其實對內部子查詢做了優化處理，我們通常認為，簡單子查詢是內層查詢先執行，然后傳遞結果給外層查詢，然后外層查詢再執行。
但是對于這個例子，如果demo表數據量很大的話，那么內層排序需要花的時間就會非常多。而實際上呢，oracle會知道外層查詢需要的記錄數，如本例中是3，
oracle在對內層查詢排序時，并不是對demo表中的14條記錄做完全的排序，根據算法，他只要找到sal最高的3條就可以了，其余的11條記錄是沒必要排序的，這就大大的節省了語句的執行時間。

如果想要得到排序后的某段數據，我們可以通過嵌套的方法來實現：
scott@DB01> select t1.*?????? ?
? 2???????? from?? (select rownum rank,t.* from (select demono,ename,sal from demo order by sal desc) t) t1
? 3???????? where rank>=3 and rank<=7;

????? RANK????? demoNO ENAME???????????? SAL
---------- ---------- ---------- ----------
???????? 3?????? 7788 SCOTT??????????? 3000
???????? 4?????? 7566 JONES??????????? 2975
???????? 5?????? 7698 BLAKE??????????? 2850
???????? 6?????? 7782 CLARK??????????? 2450
???????? 7?????? 7499 ALLEN??????????? 1600
?

注：在前面兩個例子中，我們使用到了oracle的top N分析，不過都是對整張表，或者整個結果集來說的。其實oracle 對于類似的操作，提供了一套函數，我們稱之為分析函數，分析函數對于數據做統計和分析是非常有幫助的，我們在下面只是舉一個簡單的小例子，如果你感興趣可以看看

scott@DB01> select * from demo;
???? demoNO ENAME???????????? SAL?????? COMM???? DEPTNO
---------- ---------- ---------- ---------- ----------
????? 7369 SMITH???????????? 800??????????????????? 20
????? 7499 ALLEN??????????? 1600??????? 300???????? 30
????? 7521 WARD???????????? 1250??????? 500???????? 30
????? 7566 JONES??????????? 2975??????????????????? 20
????? 7654 MARTIN?????????? 1250?????? 1400???????? 30
????? 7698 BLAKE??????????? 2850??????????????????? 30
????? 7782 CLARK??????????? 2450??????????????????? 10
????? 7788 SCOTT??????????? 3000??????????????????? 20
????? 7839 KING???????????? 5000??????????????????? 10
????? 7844 TURNER?????????? 1500????????? 0???????? 30
????? 7876 ADAMS??????????? 1100??????????????????? 20
????? 7900 JAMES???????????? 950??????????????????? 30
????? 7902 FORD???????????? 3000??????????????????? 20
????? 7934 MILLER?????????? 1300??????????????????? 10

14 rows selected.

scott@DB01> break on deptno skip 1
scott@DB01> select *
? 2? from (select deptno,
? 3??????????????? ename,
? 4??????????????? sal,
? 5??????????????? dense_rank() over(partition by deptno order by sal desc) dr
? 6??????? from demo
? 7????????? )
? 8? where dr<=3
? 9? order by deptno;

??? DEPTNO ENAME???????????? SAL???????? DR
---------- ---------- ---------- ----------
??????? 10 KING???????????? 5000????????? 1
?????????? CLARK??????????? 2450????????? 2
?????????? MILLER?????????? 1300????????? 3

??????? 20 SCOTT??????????? 3000????????? 1
?????????? FORD???????????? 3000????????? 1
?????????? JONES??????????? 2975????????? 2
?????????? ADAMS??????????? 1100????????? 3

??????? 30 BLAKE??????????? 2850????????? 1
?????????? ALLEN??????????? 1600????????? 2
?????????? TURNER?????????? 1500????????? 3


10 rows selected.

scott@DB01> select * from (
? 2????????????????? select deptno,
? 3???????????????????????? ename,
? 4???????????????????????? sal,
? 5???????????????????????? row_number() over(partition by deptno order by sal desc) dr
? 6?????????????????? from demo
? 7???????????????? )
? 8? where dr<=3
? 9? order by deptno;

??? DEPTNO ENAME???????????? SAL???????? DR
---------- ---------- ---------- ----------
??????? 10 KING???????????? 5000????????? 1
?????????? CLARK??????????? 2450????????? 2
?????????? MILLER?????????? 1300????????? 3

??????? 20 SCOTT??????????? 3000????????? 1
?????????? FORD???????????? 3000????????? 2
?????????? JONES??????????? 2975????????? 3

??????? 30 BLAKE??????????? 2850????????? 1
?????????? ALLEN??????????? 1600????????? 2
?????????? TURNER?????????? 1500????????? 3