oracle的分析函数over

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

SELECT E.DEPTNO,E.EMPNO,LAST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL  ROWS
       
  /*LAST_VALUE(E.SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL ROWS*/
       --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
       --unbounded：不受控制的，无限的
       --preceding：在...之前
       --following：在...之后
       -- ORACLE默认是升序
        BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM SCOTT.EMP E;

--或者

SELECT E.DEPTNO,
? ? ? ?E.EMPNO,
? ? ? ?E.ENAME,
? ? ? ?E.SAL,
? ? ? ?MAX(E.SAL) OVER(PARTITION BY E.DEPTNO )MAX_SAL
FROM SCOTT.EMP E;

结果：

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

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

结果：

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

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;

结果：

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

SELECT EMPNO,--注意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;

结果：

示例5：当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总?

SELECT EMPNO,--注意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;

结果：

8、NULLS FIRST/LAST?将空值字段记录放到最前或最后显示

说明：

通过RANK()、DENSE_RANK()、ROW_NUMBER()对记录进行全排列、分组排列取值，但有时候，会遇到空值的情况，空值会影响得到的结果的正确性
NULLS FIRST/LAST 可以帮助我们在处理含有空值的排序排列中，将空值字段记录放到最前或最后显示，帮助我们得到期望的结果。

SELECT E.ENAME,RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL NULLS LAST)
  FROM SCOTT.EMP E;

结果：

2、NTILE(n)

说明：NTILE函数对一个数据分区中的有序结果集进行划分，将其分组为各个桶，并为每个小组分配一个唯一的组编号。这个函数在统计分析中是很有用的。例如，如果想移除异常值,你可以将它们分组到顶部或底部的桶中，然后在统计分析的时候将这些值排除。ORACLE数据库统计信息收集也使用NTILE函数来计算直方图信息边界。在统计学术语中，NTILE函数创建等宽直方图信息。

SELECT E.ENAME,NTILE(3) OVER(ORDER BY E.SAL DESC NULLS LAST) ALL_CMP,--若只取前三分之一，ALL_CMP=1即可，若只取中间三分之一，ALL_CMP=2即可
       NTILE(3) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC NULLS LAST) ALL_DEPT  --每个部门的分成三部分
  FROM SCOTT.EMP E

结果：

（编辑：好传媒网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!