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,
??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(o.tot_sales)?cust_sales,
??5?????????????????sum(sum(o.tot_sales))?over(partition?by?o.region_id)?region_sales
??6????????????from?orders_tmp?o
??7???????????where?o.year?=?2001
??8???????????group?by?o.region_id,?o.cust_nbr)?all_sales
??9???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.*,
??2?????????100?*?round(cust_sales?/?region_sales,?2)?||?'%'?Percent
??3????from?(select?o.cust_nbr?customer,
??4?????????????????o.region_id?region,
??5?????????????????sum(o.tot_sales)?cust_sales,
??6?????????????????sum(sum(o.tot_sales))?over(partition?by?o.region_id)?region_sales
??7????????????from?orders_tmp?o
??8???????????where?o.year?=?2001
??9???????????group?by?o.region_id,?o.cust_nbr)?all_sales
?10???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等。

参考资料：《Mastering Oracle SQL》(By Alan?Beaulieu, Sanjay?Mishra?O'Reilly June 2004??0-596-00632-2)