Oracle开发之分析函数简介Over用法

一、Oracle分析函数简介:

在日常的生产环境中,我们接触得比较多的是OLTP系统(即Online Transaction Process),这些系统的特点是具备实时要求,或者至少说对响应的时间多长有一定的要求;其次这些系统的业务逻辑一般比较复杂,可能需要经过多次的运算。比如我们经常接触到的电子商城。

在这些系统之外,还有一种称之为OLAP的系统(即Online Aanalyse Process),这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大,对实时响应的要求不高或者根本不关注这方面的要求,以查询、统计操作为主。

我们来看看下面的几个典型例子:
①查找上一年度各个销售区域排名前10的员工
②按区域查找上一年度订单总额占区域订单总额20%以上的客户
③查找上一年度销售最差的部门所在的区域
④查找上一年度销售最好和最差的产品

我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同,具体有:

①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作

二、Oracle分析函数简单实例:

下面我们通过一个实际的例子:按区域查找上一年度订单总额占区域订单总额20%以上的客户,来看看分析函数的应用。

【1】测试环境:

代码如下:

SQL> desc orders_tmp;

Name                           Null?    Type
 ----------------------- -------- ----------------
 CUST_NBR                    NOT NULL NUMBER(5)
 REGION_ID                   NOT NULL NUMBER(5)
 SALESPERSON_ID      NOT NULL NUMBER(5)
 YEAR                              NOT NULL NUMBER(4)
 MONTH                         NOT NULL NUMBER(2)
 TOT_ORDERS              NOT NULL NUMBER(7)
 TOT_SALES                 NOT NULL NUMBER(11,2)

【2】测试数据:

代码如下:

SQL> select * from orders_tmp;

CUST_NBR  REGION_ID SALESPERSON_ID       YEAR      MONTH TOT_ORDERS  TOT_SALES
---------- ---------- -------------- ---------- ---------- ---------- ----------
        11          7             11                       2001          7          2      12204
         4          5              4                         2001         10         2      37802
         7          6              7                         2001          2          3       3750
        10          6              8                        2001          1          2      21691
        10          6              7                        2001          2          3      42624
        15          7             12                       2000          5          6         24
        12          7              9                        2000          6          2      50658
         1          5              2                         2000          3          2      44494
         1          5              1                         2000          9          2      74864
         2          5              4                         2000          3          2      35060
         2          5              4                         2000          4          4       6454
         2          5              1                         2000         10          4      35580
         4          5              4                         2000         12          2      39190

13 rows selected.

【3】测试语句:

代码如下:

SQL> select o.cust_nbr customer,
  o.region_id region,
  sum(o.tot_sales) cust_sales,
  sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
from orders_tmp o
where o.year = 2001
 group by o.region_id, o.cust_nbr;

CUSTOMER     REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
         4              5      37802        37802
         7              6       3750         68065
        10             6      64315        68065
        11             7      12204        12204

三、分析函数OVER解析:

请注意上面的绿色高亮部分,group by的意图很明显:将数据按区域ID,客户进行分组,那么Over这一部分有什么用呢?假如我们只需要统计每个区域每个客户的订单总额,那么我们只需要group by o.region_id,o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额,这一点和前面的不同:需要在前面分组的基础上按区域累加。很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样,前者是对一个客户,后者是对一批客户)。

这就是over函数的作用了!它的作用是告诉SQL引擎:按区域对数据进行分区,然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。

现在我们已经知道2001年度每个客户及其对应区域的订单总额,那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了

代码如下:

SQL> select *
from (select o.cust_nbr customer,
     o.region_id region,
     sum(o.tot_sales) cust_sales,
     sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
   from orders_tmp o
   where o.year = 2001
   group by o.region_id, o.cust_nbr) all_sales
 where all_sales.cust_sales > all_sales.region_sales * 0.2;

CUSTOMER     REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
         4          5      37802        37802
        10          6      64315        68065
        11          7      12204        12204

SQL>

现在我们已经知道这些大客户是谁了!哦,不过这还不够,如果我们想要知道每个大客户所占的订单比例呢?看看下面的SQL语句,只需要一个简单的Round函数就搞定了。

代码如下:

SQL> select all_sales.*,
  100 * round(cust_sales / region_sales, 2) || '%' Percent
from (select o.cust_nbr customer,
   o.region_id region,
   sum(o.tot_sales) cust_sales,
   sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
  from orders_tmp o
  where o.year = 2001
  group by o.region_id, o.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.2;

CUSTOMER     REGION CUST_SALES REGION_SALES PERCENT
---------- ---------- ---------- ------------ ----------------------------------------
         4            5                  37802        37802    100%
        10           6                  64315        68065      94%
        11           7                  12204        12204    100%

SQL>

总结:

①Over函数指明在那些字段上做分析,其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。

②Over函数可以和其它聚集函数、分析函数搭配,起到不同的作用。例如这里的SUM,还有诸如Rank,Dense_rank等。

时间: 2016-05-23

Oracle存储过程游标用法分析

本文实例讲述了Oracle存储过程游标用法.分享给大家供大家参考,具体如下: 使用游标的5个步骤 1.声明一些变量用于保存select语句返回的指 2.声明游标,并指定select 语句 3.打开游标 4.从游标中获取记录 5.关闭游标 从游标中获取每一条记录可使用fetch语句.fetch语句将列的指读取到指定的变量中: 语法: fetch cursor_name into variable[, variable ...]; 例子: create or replace procedure se

oracle数据库定时任务dbms_job的用法详解

一.dbms_job涉及到的知识点 1.创建job: variable jobno number; dbms_job.submit(:jobno, --job号 'your_procedure;',--执行的存储过程, ';'不能省略 next_date, --下次执行时间 'interval' --每次间隔时间,interval以天为单位 ); –系统会自动分配一个任务号jobno. 2.删除job: dbms_job.remove(jobno); 3.修改要执行的操作: job:dbms_j

Oracle minus用法详解及应用实例

Oracle minus用法 "minus"直接翻译为中文是"减"的意思,在Oracle中也是用来做减法操作的,只不过它不是传统意义上对数字的减法,而是对查询结果集的减法.A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果,即在A中存在,而在B中不存在的记录.其算法跟Java中的Collection的removeAll()类似,即A minus B将只去除A跟B的交集部分,对于B中存在而A中不存在的记录不会做任何操作,也不会抛出异常. Ora

Oracle中游标Cursor基本用法详解

查询 SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的 返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中.SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ PL/SQL

Oracle中的INSTR,NVL和SUBSTR函数的用法详解

Oracle中INSTR的用法: INSTR方法的格式为 INSTR(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号) 返回找到的位置,如果找不到则返回0. 例如:INSTR('CORPORATE FLOOR','OR', 3, 2)中,源字符串为'CORPORATE FLOOR', 在字符串中查找'OR',从第三个字符位置开始查找"OR",取第三个字后第2个匹配项的位置. 默认查找顺序为从左到右.当起始位置为负数的时候,从右边开始查找. 所以SELECT I

讲解Oracle数据库中的数据字典及相关SQL查询用法

Oracle数据字典概述    数据库是数据的集合,数据库维护和管理这用户的数据,那么这些用户数据表都存在哪里,用户的信息是怎样的,存储这些用户的数据的路径在哪里,这些信息不属于用户的信息,却是数据库维护和管理用户数据的核心,这些信息就是数据库的数据字典来维护的,数据库的数据字典就汇集了这些数据库运行所需要的基础信息叻.每个数据库都提供了各自的数据字典的方案,虽然形式不同,但是目的和作用是一样的,比如在mysql里数据字典是在information_schema 里表现的,sqlserver则是

oracle数据库中sql%notfound的用法详解

SQL%NOTFOUND 是一个布尔值.与最近的sql语句(update,insert,delete,select)发生交互,当最近的一条sql语句没有涉及任何行的时候,则返回true.否则返回false.这样的语句在实际应用中,是非常有用的.例如要update一行数据时,如果没有找到,就可以作相应操作.如: begin update table_name set salary = 10000 where emp_id = 10; if sql%notfound then insert into

java 查询oracle数据库所有表DatabaseMetaData的用法(详解)

一 . 得到这个对象的实例 Connection con ; con = DriverManager.getConnection(url,userName,password); DatabaseMetaData dbmd = con.getMetaData(); 二. 方法getTables的用法 原型: ResultSet DatabaseMetaData.getTables(String catalog,String schema,String tableName,String []type

Oracle数据库中SQL语句的优化技巧

在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

JSP中EL表达式的用法详解(必看篇)

EL 全名为Expression Language EL 语法很简单,它最大的特点就是使用上很方便.接下来介绍EL主要的语法结构: ${sessionScope.user.sex} 所有EL都是以${为起始.以}为结尾的.上述EL范例的意思是:从Session的范围中,取得 用户的性别.假若依照之前JSP Scriptlet的写法如下: User user =(User)session.getAttribute("user"); String sex =user.getSex( );

JavaScript中的splice方法用法详解

JavaScript中的splice主要用来对js中的数组进行操作,包括删除,添加,替换等. 注意:这种方法会改变原始数组!. 1.删除-用于删除元素,两个参数,第一个参数(要删除第一项的位置),第二个参数(要删除的项数) 2.插入-向数组指定位置插入任意项元素.三个参数,第一个参数(插入位置),第二个参数(0),第三个参数(插入的项) 3.替换-向数组指定位置插入任意项元素,同时删除任意数量的项,三个参数.第一个参数(起始位置),第二个参数(删除的项数),第三个参数(插入任意数量的项) 示例:

Linux中 find查找命令用法详解

Linux下查找文件的命令有两个,分别是locate 和 find. locate指令和find找寻档案的功能类似,但locate是透过update程序将硬盘中的所有档案和目录资料先建立一个索引数据库,在 执行loacte时直接找该索引,查询速度会较快,索引数据库一般是由操作系统管理,但也可以直接下达update强迫系统立即修改索引数据库.简单介绍下它的两个选项. #locate -i        //查找文件的时候不区分大小写 比如:locate  –i   passwd -n      

php中mysql操作buffer用法详解

本文实例讲述了php中mysql操作buffer用法.分享给大家供大家参考.具体分析如下: php与mysql的连接有三种方式,mysql,mysqli,pdo.不管使用哪种方式进行连接,都有使用buffer和不使用buffer的区别. 什么叫使用buffer和不使用buffer呢? 客户端与mysql服务端进行查询操作,查询操作的时候如果获取的数据量比较大,那个这个查询结果放在哪里呢? 有两个地方可以放:客户端的缓冲区和服务端的缓冲区. 我们这里说的buffer指的是客户端的缓冲区,如果查询结

C#中的IDisposable模式用法详解

本文实例讲述了C#中IDisposable模式的用法,针对垃圾资源的回收进行了较为详细的讲解.分享给大家供大家参考之用.具体方法如下: 首先,对于垃圾回收而言,在C#中,托管资源的垃圾回收是通过CLR的Garbage Collection来实现的,Garbage Collection会调用堆栈上对象的析构函数完成对象的释放工作:而对于一些非托管资源,比如数据库链接对象等,需要实现IDisposable接口进行手动的垃圾回收.那么什么时候使用Idisposable接口,以及如何使用呢? 先来参考一

Python中的装饰器用法详解

本文实例讲述了Python中的装饰器用法.分享给大家供大家参考.具体分析如下: 这里还是先由stackoverflow上面的一个问题引起吧,如果使用如下的代码: 复制代码 代码如下: @makebold @makeitalic def say():    return "Hello" 打印出如下的输出: <b><i>Hello<i></b> 你会怎么做?最后给出的答案是: 复制代码 代码如下: def makebold(fn):