一、Oracle分析函数入门

转载 2015年07月06日 19:48:13

一、Oracle分析函数入门

分析函数是什么?
分析函数是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;
复制代码

运行结果:

               

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

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

运行结果:

     

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

复制代码
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;
复制代码

运行结果:

   

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

复制代码
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;
复制代码

运行结果:

   

 示例目标:当前行的上一行(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;
复制代码

运行结果:

    

示例目标:   当前行的上一行(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;
复制代码

运行结果:

oracle入门学习笔记

oracle学习笔记
  • conanswp
  • conanswp
  • 2014年07月15日 22:36
  • 8725

Oracle 11g从入门到精通pdf

下载地址:网盘下载 目录 编辑 第1章 了解Oracle   1.1 Oracle中的基本概念   1.1.1 数据库   1.1.2 实例   1.1....
  • cf406061841
  • cf406061841
  • 2017年06月04日 23:22
  • 2062

超全的学习资料——Oracle 从入门到精通

Oracle 从入门到精通 目录 一、SQL...........................................................................
  • Li_soso
  • Li_soso
  • 2013年01月29日 10:20
  • 937

Oracle基础知识(四) - 触发器的基础知识

1. 什么是触发器         在Oracle中,触发器是一种特殊的存储过程,它在发生某种数据库事件时由Oracle系统自动触发。触发器通常用于加强数据的完整性约束和业务规则等, 对于表来说,触发...
  • jolingogo
  • jolingogo
  • 2013年11月07日 15:19
  • 1230

Oracle数据库的基础使用

oracle数据库的基础使用
  • zhaodongwoshini
  • zhaodongwoshini
  • 2016年11月10日 16:37
  • 4557

oracle从入门到精通 之 三.SQL基础的练习题目(用心总结版)

/*Student(Sno,Sname,Sage,Ssex) 学生表    Course(Cno,Cname,Tno) 课程表    SC(Sno,Cno,score) 成绩表    Teach...
  • yusiyuuestc
  • yusiyuuestc
  • 2013年12月20日 23:22
  • 797

《Oracle 12c数据库DBA入门指南 》目录

当当网页面链接:点击打开链接 点击打开链接 第1章  Oracle数据库基础知识 1.1  什么是关系数据库 1.1.1  关系数据库模型 1.1.2  关系数据模型的创始人 1.2  Oracle数...
  • brucexia
  • brucexia
  • 2015年02月10日 17:51
  • 1502

Oracle入门基础理论

一:Oracle的概述 什么是Oracle Oracle数据库是Oracle(中文名称叫甲骨文)公司的核心产品,Oracle数据库是一个适合于大中型企业的数据库管理系统。在所有的数据库管理系统中(...
  • f525921307
  • f525921307
  • 2016年08月16日 10:09
  • 794

Oracle学习笔记一

读《收获,不止oracle一书》笔记。 --------------------------------------------------------------------------------...
  • shuizhizhiyin
  • shuizhizhiyin
  • 2016年06月25日 14:28
  • 230

Oracle入门学习----通过jdbc连接oracle实例

/** * 一个非常标准的连接Oracle数据库的示例代码 */ public void testOracle() { Connection con = null;// 创建一个数据库连接...
  • u014482758
  • u014482758
  • 2015年07月28日 16:04
  • 437
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:一、Oracle分析函数入门
举报原因:
原因补充:

(最多只允许输入30个字)