Oracle开发之分析函数简介Over用法(2)-木庄网络博客

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      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 *
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      64315        68065
        11          7      12204        12204

SQL>

现在我们已经知道这些大客户是谁了！哦，不过这还不够，如果我们想要知道每个大客户所占的订单比例呢？看看下面的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                  64315        68065      94%
        11           7                  12204        12204    100%

SQL>

总结：

①Over函数指明在那些字段上做分析，其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。

②Over函数可以和其它聚集函数、分析函数搭配，起到不同的作用。例如这里的SUM，还有诸如Rank，Dense_rank等。

标签：Oracle

返回前面的内容