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

2024-08-29 13:59:03

字体：大中小

来源：转载

供稿：网友

一、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,
	  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;

上一篇：Oracle 查询死锁并解锁的终极处理方法

下一篇：Oracle中rank,over partition函数的使用方法