【DB筆試面試460】在Oracle中，有哪些常用的分析函數？

• 2019 年 10 月 11 日
• 筆記

題目部分

在Oracle中，有哪些常用的分析函數？

答案部分

分析函數是Oracle從8.1.6開始引入的一個新的概念，為分析數據提供了一種簡單高效的處理方式。在分析函數出現以前，實現相同的功能必須使用自聯查詢、子查詢或者內聯視圖，甚至需要複雜的存儲過程來實現。有了分析函數後，只要一條簡單的SQL語句就可以實現了，而且在執行效率方面也有相當大的提高。Oracle的分析函數主要用於報表開發和數據倉庫。分析函數的功能強大，可以用於SQL語句的優化，在某些情況下，能達到事半功倍的效果。

分析函數的一般格式是：函數名(參數列表) OVER ([PARTITION BY 欄位名或表達式] [ORDER BY 欄位名或表達式])，其中OVER()部分稱為開窗函數，它是可以選填的。開窗函數指定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化。分析函數的寫法比較複雜，下面將講解幾個常用的分析函數。

（一）RANK()分析函數

該函數的作用是根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。該函數的結果是不連續的，如果有4個人，其中有3個是並列第1名，那麼最後的排序結果結果如：1 1 1 4。

例子：下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號

SYS@lhrdb> SELECT d.department_id,    2         e.last_name,    3         e.salary,    4         RANK() OVER(PARTITION BY e.department_id ORDER BY e.salary) AS drank    5  FROM   hr.employees   e,    6         hr.departments d    7  WHERE  e.department_id = d.department_id    8  AND    d.department_id IN ('60', '90');  DEPARTMENT_ID LAST_NAME                     SALARY      DRANK  ------------- ------------------------- ---------- ----------             60 Lorentz                         4200          1             60 Pataballa                       4800          2             60 Austin                          4800          2             60 Ernst                           6000          4             60 Hunold                          9000          5             90 De Haan                        17000          1             90 Kochhar                        17000          1             90 King                           24000          3

RANK()分析函數可以用於Top-N查詢中，例如，在上例中，若要查詢每個部門薪水排在第一的員工，則SQL可以如下：

SYS@lhrdb> SELECT *    2    FROM (SELECT D.DEPARTMENT_ID,    3                 E.LAST_NAME,    4                 E.SALARY,    5                 RANK() OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY E.SALARY) AS DRANK    6            FROM HR.EMPLOYEES E, HR.DEPARTMENTS D    7           WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID    8             AND D.DEPARTMENT_ID IN ('60', '90'))    9   WHERE DRANK = 1;  DEPARTMENT_ID LAST_NAME                     SALARY      DRANK  ------------- ---------------------- ---------- ----------             60    Lorentz                         4200          1             90    Kochhar                        17000          1             90    De Haan                        17000          1

需要注意的是，除了ORDER BY子句的運算外，分析函數在SQL語句中將會最後執行。因此，分析函數只能應用於SELECT的列或ORDER BY子句中。

還有一個類似的函數為：DENSE_RANK()OVER(ORDER BY 列名排序)，它的排序結果是連續的，如果有4個人，其中有3個是並列第1名，那麼最後的排序結果如：1 1 1 2，如下：

SYS@lhrdb> SELECT d.department_id,    2         e.last_name,    3         e.salary,    4         DENSE_RANK() OVER(PARTITION BY e.department_id ORDER BY e.salary) as drank    5    FROM hr.employees e, hr.departments d    6   WHERE e.department_id = d.department_id    7     AND d.department_id IN ('60', '90');  DEPARTMENT_ID LAST_NAME                     SALARY      DRANK  ------------- ------------------------- ---------- ----------             60 Lorentz                         4200          1             60 Pataballa                       4800          2             60 Austin                          4800          2             60 Ernst                           6000          3             60 Hunold                          9000          4             90 De Haan                        17000          1             90 Kochhar                        17000          1             90 King                           24000          2

如果不想並列排序，那麼可以使用ROW_NUMBER分析函數，如下所示：

SYS@lhrdb> SELECT D.DEPARTMENT_ID,    2                 E.LAST_NAME,    3                 E.SALARY,    4                 ROW_NUMBER() OVER(PARTITION BY E.DEPARTMENT_ID ORDER BY E.SALARY) AS DRANK    5            FROM HR.EMPLOYEES E, HR.DEPARTMENTS D    6           WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID    7             AND D.DEPARTMENT_ID IN ('60', '90');  DEPARTMENT_ID LAST_NAME                     SALARY      DRANK  ------------- ------------------------- ---------- ----------             60 Lorentz                         4200          1             60 Pataballa                       4800          2             60 Austin                          4800          3             60 Ernst                           6000          4             60 Hunold                          9000          5             90 De Haan                        17000          1             90 Kochhar                        17000          2             90 King                           24000          3

（二）LAG和LEAD分析函數

LAG和LEAD函數可以在一次查詢中取出同一欄位的前N行的數據和後N行的值。這種操作可以使用對相同表的表連接來實現，不過使用LAG和LEAD有更高的效率。LAG可以訪問當前行之前的行，LEAD與LAG相反，LEAD可以訪問當前行之後的行。如下的程式碼查詢了AWR中的快照號：

SYS@lhrdb> SELECT TO_CHAR(D.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS') STARTUP_TIME,    2         LAG(D.SNAP_ID) OVER(PARTITION BY D.STARTUP_TIME ORDER BY SNAP_ID) SNAP_ID_PRE,    3         D.SNAP_ID,    4         LEAD(D.SNAP_ID) OVER(PARTITION BY D.STARTUP_TIME ORDER BY SNAP_ID) SNAP_ID1_NEXT    5    FROM DBA_HIST_SNAPSHOT D, V$INSTANCE ND    6   WHERE D.INSTANCE_NUMBER = ND.INSTANCE_NUMBER    7   AND d.snap_id BETWEEN 10 AND 15    8   ORDER BY D.SNAP_ID DESC;  STARTUP_TIME        SNAP_ID_PRE    SNAP_ID SNAP_ID1_NEXT  ------------------- ----------- ---------- -------------  2016-11-30 12:00:59          14         15  2016-11-30 11:00:56          13         14            15  2016-11-30 10:00:54          12         13            14  2016-11-30 08:51:50          11         12            13  2016-11-29 15:00:32          10         11            12  2016-11-29 14:00:25                     10            11

（三）RULLUP分析函數

ROLLUP分組函數可以理解為Group By分組函數封裝後的精簡用法。

SQL> SELECT NVL(a.deptno||'','總計') AS 部門編碼,    2         a.job AS 工作,    3         SUM(sal) AS 工資小計    4  FROM   scott.emp a    5  GROUP  BY ROLLUP((a.deptno, a.job));  部門編碼 工作 工資小計  ---------------------------------------- --------- ----------  10                                       CLERK           1300  10                                       MANAGER         2450  10                                       PRESIDENT       5000  20                                       CLERK           1900  20                                       ANALYST         6000  20                                       MANAGER         2975  30                                       CLERK            950  30                                       MANAGER         2850  30                                       SALESMAN        5600  總計                                                    29025

對每份工作還能進行小計，如下：

SELECT CASE GROUPING(A.DEPTNO)           WHEN 1 THEN            '總計'           ELSE            TO_CHAR(DEPTNO)         END AS 部門編碼,         CASE           WHEN GROUPING(A.DEPTNO) = 1 THEN            NULL           WHEN GROUPING(JOB) = 1 THEN            '小計'           ELSE            JOB         END AS 工作,           CASE             WHEN GROUPING(a.job) = 1 THEN              NULL             WHEN GROUPING(mgr) = 1 THEN              '小計'             ELSE              to_char(mgr)         END AS 主管,         SUM(SAL) AS 工資合計    FROM SCOTT.EMP A   WHERE A.DEPTNO IN (10, 20)   AND A.JOB IN ('CLERK','MANAGER')   GROUP BY ROLLUP(A.DEPTNO, A.JOB, A.MGR);

除此之外，還有COUNT() OVER、GROUP BY CUBE、RATIO_TO_REPORT、AVG OVER、MAX OVER等等常用的分析函數，讀者可自行查閱相關Oracle文檔進行學習。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。

About Me：小麥苗

● 本文作者：小麥苗，只專註於資料庫的技術，更注重技術的運用

● 作者部落格地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列題目來源於作者的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

● 題目解答若有不當之處，還望各位朋友批評指正，共同進步