一.分析函数1(OVER)
目录：
===============================================
1.Oracle分析函数简介
2. Oracle分析函数简单实例
3.分析函数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,
2???????? o.region_id region,
3???????? sum(o.tot_sales) cust_sales,
4???????? sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
5??? from orders_tmp o
6?? where o.year = 2001
7?? 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 *
2??? from (select o.cust_nbr customer,
3???????????????? o.region_id region,
4???????????????? sum(