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

DB宝 2018-12-16
2011
♣题目部分在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程序员面试笔试宝典》，作者：李华荣。