posts - 262,  comments - 221,  trackbacks - 0

目录:
===============================================
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(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)


-------------------------------------------------------------
生活就像打牌,不是要抓一手好牌,而是要尽力打好一手烂牌。
posted on 2008-06-25 14:02 Paul Lin 阅读(15146) 评论(8)  编辑  收藏 所属分类: Oracle 开发


FeedBack:
# re: 【原】Oracle开发专题之:分析函数(OVER)
2008-10-24 15:28 | li
正在学习中,谢谢,请问 “简单实例”部分 如果不用 分析函数如何实现呢?  回复  更多评论
  
# re: 【原】Oracle开发专题之:分析函数(OVER)
2008-10-28 10:09 | Paul Lin
@li

如果不用分析函数的话,就比较麻烦了。你只能分别查询出Customer, Region,和Customer的订单额,地区的订单总额。然后采用连接的方式:就是把两个结果集拼在一起。条件就是customer 的id和region id必须分别等于另外2个查询中的相应字段值  回复  更多评论
  
# re: 【原】Oracle开发专题之:分析函数(OVER)[未登录]
2008-10-29 09:14 | li
非常感谢你的回答.....我是oracle 的初学者,真没有想到,sql 的功能这么强大,努力学习中  回复  更多评论
  
# re: 【原】Oracle开发专题之:分析函数(OVER)
2010-08-23 08:03 | horrsion
写得很好,学习中  回复  更多评论
  
# re: 【原】Oracle开发专题之:分析函数(OVER)
2010-12-16 13:52 | china_uv
very 3Q  回复  更多评论
  
# re: 【原】Oracle开发专题之:分析函数(OVER)
2011-05-17 17:53 | cherry
为何是sum(sum()), 而不是sum()呢?  回复  更多评论
  
# re: 【原】Oracle开发专题之:分析函数(OVER)[未登录]
2011-05-17 18:11 | Paul Lin
@cherry

二次统计,即把某个人的某些数值统计总和后,再加上另外一个人的数值总和  回复  更多评论
  
# re: 【原】Oracle开发专题之:分析函数(OVER)
2011-07-06 16:50 | singleParty
请问sum(sum(a))中,括号内的sum(a)是因为group by a,所以在运算sum的时候需要聚合显示下a不然报错,是么?  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航:
 
<2008年6月>
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

常用链接

留言簿(21)

随笔分类

随笔档案

BlogJava热点博客

好友博客

搜索

  •  

最新评论

阅读排行榜

评论排行榜