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

一、Oracle分析函数简介：

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

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

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

我们看看上面的几个例子就能够感觉到这几个查询和我们平常遇到的查询有些区别，具体有：

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

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

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

【1】测试环境：

 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】测试数据：

  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】测试语句：

  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%以上的大客户了

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

SQL>

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

  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等。