【原】Oracle开发专题之：分析函数1(OVER)
目录：
===============================================
1.Oracle分析函数简介
2. Oracle分析函数简单实例
3.分析函数OVER解析
一、Oracle分析函数简介：
在日常的生产环境中，有一种称之为OLAP的系统(
即Online Analyses Process)，
这些系统一般用于系统决策使用。通常和数据仓库、
数据分析、数据挖掘等概念联
系在一起，以查询、统计操作为主。
我们来看看下面的几个典型例子：
①查找上一年度各个销售地区排名前10的员工
②按地区查找上一年度销售总额占地区销售总额20%以上的客户
③查找上一年度销售最差的部门所在的地区
④查找上一年度销售最好和最差的产品
我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到
的查询有些不同，具体有：
①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作
二、Oracle分析函数简单实例：
下面我们通过一个实际的例子来看看分析函数的应用：
按地区查找上一年度销售总额占地区销售总额20%以上的客户。
【1】测试环境：
SQL> desc orders_tmp;
Name Type
-------- ----------------
CUST_NBR NUMBER(5)
REGION_ID NUMBER(5)
SALESPERSON_ID NUMBER(5)
YEAR NUMBER(4)
MONTH NUMBER(2)
TOT_ORDERS NUMBER(7)
TOT_SALES 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
748
2 5 4 2000 3 2
35060
2 5 4 2000 4 4
54
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 315 68065
11 7 12204 12204
三、分析函数OVER解析：
请注意上面的绿色高亮部分：
sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales ；
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 315 68065
11 7 12204 12204
现在我们已经知道这些大客户是谁了！哦，不过这还不够，
如果我们想要知道每个大客户所占的订单比例呢？
看看下面的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 315 68065 94%
11 7 12204 12204 100%
总结：
① Over函数指明在那些字段上做分析，其内跟Partition by表示对数据进行分区。
注意Partition by可以有多个字段。
② Over函数可以和其它聚集函数、分析函数搭配，起到不同的作用。
例如这里的SUM，还有诸如Rank，Dense_rank等。
参考资料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)
