Oracle分析函数入门

434次阅读

共计 1893 个字符，预计需要花费 5 分钟才能阅读完成。

分析函数是什么？
分析函数是 Oracle 专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数和聚合函数的不同之处是什么？
普通的聚合函数用 group by 分组，每个分组返回一个统计值，而分析函数采用 partition by 分组，并且每组每行都可以返回一个统计值。

分析函数的形式
分析函数带有一个开窗函数 over()，包含三个分析子句: 分组 (partition by), 排序(order by), 窗口(rows)，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
注：窗口子句在这里我只说 rows 方式的窗口,range 方式和滑动窗口也不提

分析函数例子(在 scott 用户下模拟)

示例目的：显示各部门员工的工资，并附带显示该部分的最高工资。

--显示各部门员工的工资，并附带显示该部分的最高工资。
SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following 针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
            --unbounded：不受控制的，无限的
            --preceding：在... 之前
            --following：在... 之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

运行结果：

Oracle 分析函数入门

示例目的：按照 deptno 分组，然后计算每组值的总和

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;

运行结果：

Oracle 分析函数入门

示例目的：对各部门进行分组，并附带显示第一行至当前行的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意 ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

运行结果：

Oracle 分析函数入门

示例目标：当前行至最后一行的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意 ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

运行结果：

Oracle 分析函数入门

示例目标：当前行的上一行 (rownum-1) 到当前行的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意 ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行 (rownum-1) 到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

运行结果：

Oracle 分析函数入门

示例目标：当前行的上一行 (rownum-1) 到当前行的下辆行 (rownum+2) 的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意 ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行 (rownum-1) 到当前行的下辆行 (rownum+2) 的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;