Oracle开发专题之：分析函数(OVER)(二)

三、分析函数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等。

作者“涣熊的仓库”